Thread: "set autocommit on" in golang client query
Apologies for the rather naive question. I have a psql migration script to call a looping procedure which commits batches of data along the following lines: set search_path = a, b c; \set AUTOCOMMIT on -- call procedure with nested transactions CALL c.pr_my_procedure(debug=>true); I'm trying to migrate this to a programme using the golang pgx module to exec this code on a large number of databases in parallel. There doesn't seem an obvious way of setting autocommit outside of psql, so the equivalent of conn.Exec(context.Background(), "set search_path = a, b c; set AUTOCOMMIT on; CALL c.pr_my_procedure(debug=>true);") fails with ERROR: syntax error at or near "ON" while conn.Exec(context.Background(), "set search_path = a, b c; CALL c.pr_my_procedure(debug=>true);") fails with ERROR: invalid transaction termination How should I set autocommit on? The "Create Procedure" docs at https://www.postgresql.org/docs/current/sql-createprocedure.html don't seem to mention autocommit. Rory
Hi,
Le lun. 1 août 2022 à 15:10, Rory Campbell-Lange <rory@campbell-lange.net> a écrit :
Apologies for the rather naive question.
I have a psql migration script to call a looping procedure which commits
batches of data along the following lines:
set search_path = a, b c;
\set AUTOCOMMIT on
-- call procedure with nested transactions
CALL c.pr_my_procedure(debug=>true);
I'm trying to migrate this to a programme using the golang pgx module to exec
this code on a large number of databases in parallel. There doesn't seem an
obvious way of setting autocommit outside of psql, so the equivalent of
conn.Exec(context.Background(),
"set search_path = a, b c; set AUTOCOMMIT on; CALL c.pr_my_procedure(debug=>true);")
fails with
ERROR: syntax error at or near "ON"
while
conn.Exec(context.Background(),
"set search_path = a, b c; CALL c.pr_my_procedure(debug=>true);")
fails with
ERROR: invalid transaction termination
How should I set autocommit on? The "Create Procedure" docs at
https://www.postgresql.org/docs/current/sql-createprocedure.html don't seem to
mention autocommit.
That's because it doesn't exist on the PostgreSQL server. You can use it with psql but that's a psql feature, not the server's.
--
Guillaume.