From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Ross <jross@openvistas.net>
cc: pgsql-general@lists.postgresql.org
Subject: Re: After 10 -> 15 upgrade getting "cannot commit while a portal is
 pinned" on one python function
In-reply-to: <5ef96169-c11d-48de-b322-b7579bf56cc3@openvistas.net>
References: <6960da61-8033-4067-84d1-d9257ce94870@openvistas.net>
 <81c4556d-b599-4512-b6a2-7255f2a39089@aklaver.com>
 <d553c7d6-8af9-4e93-892b-7dc65129b4a6@openvistas.net>
 <37154728-4700-45b7-9261-72400c80b12b@aklaver.com>
 <133886.1710975858@sss.pgh.pa.us>
 <5ef96169-c11d-48de-b322-b7579bf56cc3@openvistas.net>
Comments: In-reply-to Jeff Ross <jross@openvistas.net>
	message dated "Wed, 27 Mar 2024 10:02:11 -0600"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <3446816.1711575877.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Wed, 27 Mar 2024 17:44:37 -0400
Message-ID: <3446817.1711575877@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3446817.1711575877%40sss.pgh.pa.us>
Precedence: bulk

Jeff Ross <jross@openvistas.net> writes:
> I only use one plpython3u cursor in that function.  The plpython docs say:

> "Cursors are automatically disposed of. But if you want to explicitly 
> release all resources held by a cursor, use the |close| method. Once 
> closed, a cursor cannot be fetched from anymore."

> https://www.postgresql.org/docs/15/plpython-database.html#id-1.8.11.14.3

> Perhaps "pinned" in the error message means "open"?

No, it means "pinned" ... but I see that plpython pins the portal
underlying any PLyCursor object it creates.  Most of our PLs do
that too, to prevent a portal from disappearing under them (e.g.
if you were to try to close the portal directly from SQL rather
than via whatever mechanism the PL wants you to use).

> I added a cursor.close() as the last line called in that function and it 
> works again.

It looks to me like PLy_cursor_close does pretty much exactly the same
cleanup as PLy_cursor_dealloc, including unpinning and closing the
underlying portal.  I'm far from a Python expert, but I suspect that
the docs you quote intend to say "cursors are disposed of when Python
garbage-collects them", and that the reason your code is failing is
that there's still a reference to the PLyCursor somewhere after the
plpython function exits, perhaps in a Python global variable.

			regards, tom lane