From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Rob Sargent <robjsargent@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function
Date: Wed, 27 Mar 2024 16:41:05 -0700
Message-ID: <bc73315e-cc02-4892-bcc7-669ffc5b38c9@aklaver.com> (raw)
In-Reply-To: <98d3484a-0ce1-4c74-85d4-c053652c5b9d@gmail.com>
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>
<3446817.1711575877@sss.pgh.pa.us>
<45d75f81-0be1-4990-97a7-5b4fa0e37c65@openvistas.net>
<98d3484a-0ce1-4c74-85d4-c053652c5b9d@gmail.com>
On 3/27/24 16:35, Rob Sargent wrote:
>
>
> On 3/27/24 17:05, Jeff Ross wrote:
>>
>> On 3/27/24 15:44, Tom Lane wrote:
>>
>>> 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
>>>
>>>
>> Thank you for your reply, as always, Tom!
>>
>> Debugging at this level might well be over my paygrade ;-)
>>
>> I just happy that the function works again, and that I was able to
>> share a solution to this apparently rare error with the community.
>>
>> Jeff
>>
> My read of Tom's reply suggests you still have work to do to find the
> other "reference" holding on to your cursor.
I would start with:
def logging(comment):
global database
<...>
--
Adrian Klaver
adrian.klaver@aklaver.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, robjsargent@gmail.com, 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: <bc73315e-cc02-4892-bcc7-669ffc5b38c9@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox