The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/15/plpgsql-cursors.html
Description:
Okay,
I am not 100% sure how to explain what the issue is. Simply put. If I
call a cursor: cur_this in a procedure.
Then while that cursor is OPEN, I call ANOTHER procedure that (anywhere down
the callstack) tries to open another cursor, although different, with the
same name (cur_this), then I get an error...
[42P03] ERROR: cursor "cur_this" already in use
I believe the documentation does NOT make this clear. Nor does it come
close to suggesting the right way to redesign the cursor name (or if there
is a way to have it be dynamically generated so it can't collide, although
there is some verbiage in there about refcursors).
To be clear. Section: 43.7.1. Declaring Cursor Variables
has no indication that the "name" of a cursor variable might live OUTSIDE
the DECLARE/BEGIN/END block unlike other variables.
And that once opened, anything in the call stack could REFERENCE this? (what
are the limitations here? I would avoid doing it, but interesting...)
Once you bump into it, it might "make some sense", but the opportunity to
document this. Explain how it should and SHOULD NOT be used [Is it a bad
idea to get the current values of a cursor opened by a caller and not closed
yet? (OMG I hope so)].
But this caused some renaming of cursors in our converted code... And I
would have NEVER GUESSED this behavior existed.
And I don't know where exactly it should be documented... (This location
and the sql-declare), or how to properly document it.
For example, there is plenty of room here:
https://www.postgresql.org/docs/current/sql-declare.html
to identify the nuances of how the "name" of a cursor impacts things, like
the names of other cursors.
Finally, as an observation, I think I can safely assume that you can't write
a recursive procedure that makes the recursive calls from inside the LOOP of
an open (locally defined) cursor for this same reason.
Thanks in advance!
