From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Jeff Ross <jross@openvistas.net>
To: pgsql-general@lists.postgresql.org
Subject: Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function
Date: Wed, 20 Mar 2024 16:13:31 -0700
Message-ID: <481ed49d-7d18-4fdb-9a62-df8598728e72@aklaver.com> (raw)
In-Reply-To: <d553c7d6-8af9-4e93-892b-7dc65129b4a6@openvistas.net>
References: <6960da61-8033-4067-84d1-d9257ce94870@openvistas.net>
<81c4556d-b599-4512-b6a2-7255f2a39089@aklaver.com>
<d553c7d6-8af9-4e93-892b-7dc65129b4a6@openvistas.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, jross@openvistas.net
Subject: Re: After 10 -> 15 upgrade getting "cannot commit while a portal is pinned" on one python function
In-Reply-To: <481ed49d-7d18-4fdb-9a62-df8598728e72@aklaver.com>
References: <6960da61-8033-4067-84d1-d9257ce94870@openvistas.net> <81c4556d-b599-4512-b6a2-7255f2a39089@aklaver.com> <d553c7d6-8af9-4e93-892b-7dc65129b4a6@openvistas.net> <481ed49d-7d18-4fdb-9a62-df8598728e72@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox