On 3/20/24 17:04, Tom Lane wrote:
Adrian Klaver <adrian.klaver@aklaver.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