While working on transaction control in procedures, I noticed some
inconsistencies in how portal pinning is used.
This mechanism was introduced in
eb81b6509f4c9109ecf8839d8c482cc597270687 to prevent user code from
closing cursors that PL/pgSQL has created internally, mainly for FOR
loops. Otherwise, user code could just write CLOSE '<unnamed portal X>'
to mess with the language internals.
It seems to me that PL/Perl and PL/Python should also use that
mechanism, because the same problem could happen there. (PL/Tcl does
not expose any cursor functionality AFAICT.) Currently, in PL/Perl, if
an internally generated cursor is closed, PL/Perl just thinks the cursor
has been exhausted and silently does nothing. PL/Python comes back with
a slightly bizarre error message "closing a cursor in an aborted
subtransaction", which might apply in some situations but not in all.
Attached is a sample patch that adds portal pinning to PL/Perl and
PL/Python.
But I also wonder whether we shouldn't automatically pin/unpin portals
in SPI_cursor_open() and SPI_cursor_close(). This makes sense if you
consider "pinned" to mean "internally generated". I don't think there
is a scenario in which user code should directly operate on a portal
created by SPI.
Comments?
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services