Обсуждение: Cursor Names are "Session Global while Opened"? (should be mentioned in the docs)
Cursor Names are "Session Global while Opened"? (should be mentioned in the docs)
От
PG Doc comments form
Дата:
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!