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

On 3/20/24 17:04, Tom Lane wrote:

> Adrian Klaver<adrian(dot)klaver(at)aklaver(dot)com> writes:
>> Haven't had a chance to go through this yet. I'm going to say though
>> that Tom Lane is looking for a shorter generic case that anyone could
>> run on their system.
> Yeah, it's a long way from that trigger function definition to a
> working (i.e. failing) example. Shortening the trigger might help by
> eliminating some parts of the infrastructure that would need to be
> shown --- but nobody's going to try to reverse-engineer all that.
>
> regards, tom lane
>
It took some digging but I've found a very simple fix to this.

Somewhere (sorry, can't find it again) I read that a postgresql cursor
is sometimes referenced as "portal".  This was when I was still pretty
sure that this was a psycopg2 issue.

Further testing ruled that out--I wasn't getting the error on the
psycopg2 commit statements, I was getting the error when the plpython3u
function itself exits and tries to commit.

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"?

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

I haven't been able to come up with a test case that throws the same
error, though, so I consider this a solution to what is very likely an
odd corner case.

Jeff