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
