Postgresql invalid transaction termination when calling procedure

Question

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?

Answer 1

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.