If I run the example code at https://www.postgresql.org/docs/current/plpgsql-transactions.html inside PgAdmin with autocommit off I get an error
CALL transaction_test1();
ERROR: invalid transaction termination
SQL state: 2D000
Context: PL/pgSQL function stg_vplymp.transaction_test1() line n at COMMIT
but if I run it on command line with psql it runs as expected.
What's the difference? Does PgAdmin add something to call stack so the procedure cannot do transaction control?
Using psql
:
CREATE TABLE test1(a integer);
CREATE PROCEDURE transaction_test1()
LANGUAGE plpgsql
AS $$
BEGIN
FOR i IN 0..9 LOOP
INSERT INTO test1 (a) VALUES (i);
IF i % 2 = 0 THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
END LOOP;
END;
$$;
CALL transaction_test1();
SELECT * from test1;
a
---
0
2
4
6
8
TRUNCATE test1;
BEGIN;
CALL transaction_test1();
ERROR: invalid transaction termination
CONTEXT: PL/pgSQL function transaction_test1() line 6 at COMMIT
ROLLBACK;
I'm going to say that when you turn autocommit
off in pgAdmin4 it starts a transaction block with BEGIN
. Per CALL:
If CALL is executed in a transaction block, then the called procedure cannot execute transaction control statements. Transaction control statements are only allowed if CALL is executed in its own transaction.
The solution would be to run with autocommit
on. That is the default behavior for psql
, which is why it works there, unless you explicitly create a transaction block.