Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux