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

From Adrian Klaver
Subject Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function
Date
Msg-id 481ed49d-7d18-4fdb-9a62-df8598728e72@aklaver.com
Whole thread Raw
In response to Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function  (Jeff Ross <jross@openvistas.net>)
List pgsql-general
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.

b) #prod_database_connection.set_session(autocommit=True)
    #dev_database_connection.set_session(autocommit=True)

    Why are they commented out?

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?

How is it setup?

d) Why predefine all those cursors()?

e) Why is database global?

> 
> Jeff
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com




pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function
Next
From: veem v
Date:
Subject: Not able to purge partition