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@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



Reply via email to