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.
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?
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
orDO
invocations from the top level or nestedCALL
orDO
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:
CALL test_commit();
and hit Execute/Refresh F5:
CALL test_commit();
to a separate Query Tool tab and execute it there.
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.