I cannot COMMIT inside a procedure

Question

This question has been asked before :

Finding COMMIT impossible in a Postgres Housekeeping Procedure

Postgresql invalid transaction termination when calling procedure

...but none of the answers worked for me.

I have a loop inside my stored procedure. The loop contains INSERTs, which need to be written to the output table at the end of each pass of the loop.

• If I don't use COMMIT, I need to wait until the end of the stored procedure to have the results accessible.
• When I insert COMMIT (right before the "end loop"), I get the following error message :

ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function [...] at COMMIT 

SQL state: 2D000

I tried the first example from the official documentation : https://www.postgresql.org/docs/12/plpgsql-transactions.html

and I still get the same error message.

In fact, I made an even more minimal example :

CREATE TABLE test1 (a INTEGER);
CREATE PROCEDURE test_commit()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 0..9 LOOP
        INSERT INTO test1 (a) VALUES (i);
            COMMIT;
    END LOOP;
END;
$$;

CALL test_commit();

Same error message :

ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function test_commit() line 5 at COMMIT 

SQL state: 2D000

I am running the code from pgAdmin 4, and I tried switching "auto commit" on or off... same result.

What am I doing wrong?

Answer 1

The CALL that invokes a transaction-handling procedure cannot be wrapped in a transaction with the other statements, in any way. The doc you linked actually mentions that, although without any details or demonstration:

Transaction control is only possible in CALL or DO invocations from the top level or nested CALL or DO invocations without any other intervening command.

Similar to how you need to run VACUUM separately because it refuses to run in a transaction, you have to run that CALL on its own:

1. Select just the line with CALL test_commit(); and hit Execute/Refresh F5:
2. Move the CALL test_commit(); to a separate Query Tool tab and execute it there.
3. As suggested by @Laurenz Albe, try switching from pgAdmin Query Tool to PSQL Tool and run it there instead.

Both of the solutions in pgAdmin Query Tool will work regardless of autocommit on/off state. In psql, you'd have to explicitly open a transaction to switch it off.