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

On 3/20/24 17:13, Adrian Klaver wrote:

> On 3/20/24 15:52, Jeff Ross wrote:
>> On 3/20/24 16:25, Adrian Klaver wrote:
>>
>>> On 3/20/24 15:18, Jeff Ross wrote:
>>>> Greetings!
>>>>
>>>> I built a trigger fired process that copies an "order" from our
>>>> production database to our dev database.  An order, in this case,
>>>> is an initial row from a table and all of the rows in all of the
>>>> tables in that database/schema that are needed to satisfy all of
>>>> the foreign key constraints for the original insert. Through a web
>>>> page, one of our folks can select a schema and an order id to
>>>> copy.  That information is then inserted into a table.  A trigger
>>>> attached to that table takes care of copying the necessary rows
>>>> using a function that uses both plython3u and psycopg2.  I can
>>>> supply the source code if that will help.
>>>
>>> I think that will help, especially the interaction between psycopg2
>>> and plpython3u.
>>>
>> As requested:
>>
>> https://openvistas.net/copy_orders_to_dev.html
>
> 1) I have not gone through this thoroughly enough to figure out what
> is going on.
>
> 2) Things I have noticed, may not be relevant.
>
> a) from psycopg2 import sql
>    Never used.
>
I have a base template that I use for complex functions--this is a part
of that.  Never caused a problem before.
> b) #prod_database_connection.set_session(autocommit=True)
>    #dev_database_connection.set_session(autocommit=True)
>
>    Why are they commented out?

If autocommit is on, the following fails:

NOTICE:  217, create temp table if not exists load_temp (like
wholesale.load including all) on commit drop; truncate load_temp;
ERROR:  psycopg2.errors.UndefinedTable: relation "load_temp" does not exist

>
> c) prod_database_connection_string = "host='pgbouncer' dbname='%s'
> application_name = '%s'"
>
> dev_database_connection_string = "host='pgbouncer' dbname='%s'
> application_name = '%s'"
>
> What version of PgBouncer?
>
[rossj(at)cron ~]$ pgbouncer -V
PgBouncer 1.21.0
libevent 2.0.21-stable
adns: evdns2
tls: OpenSSL 1.0.2k-fips  26 Jan 2017

> How is it setup?

session mode?

>
> d) Why predefine all those cursors()?
Why not?  Sort of part of the template and the psycopg2 docs say that
cursors are cheap.
>
> e) Why is database global?

I think that's also a holdover from a previous function that I imported
into this.

Thanks for the reply--gave me a good chance to do a little cleanup.  The
error is happening when psycopg2 is trying to commit so I'll also ask there.

Jeff