Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function

On 3/27/24 18:00, Jeff Ross wrote:
> 
> On 3/27/24 17: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.
> 
> Yes, my read was the same.
> 
> There are exactly 3 references to that cursor now that I added the 
> close() at the end.
> 
> Here are the first 2 (cursor renamed from the code I posted):
> 
>          plpy_cursor = plpy.cursor(schemas_query)
>          while True:
>              schema_rows = plpy_cursor.fetch(100)

If the above is the complete while loop how you expect it to break out 
of the loop?

Or did you do per Postgres docs?:

https://www.postgresql.org/docs/current/plpython-database.html

cursor = plpy.cursor("select num from largetable")
while True:
     rows = cursor.fetch(batch_size)
     if not rows:
         break
     for row in rows:
         if row['num'] % 2:
             odd += 1


> 
> The last is:
> 
>          plpy_cursor.close()
> 
> I don't know how to proceed further.
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com