how to use COMMIT and ROLLBACK in a PostgreSQL function
You cannot use transaction statements like SAVEPOINT
, COMMIT
or ROLLBACK
in a function. The documentation says:
In procedures invoked by the
CALL
command as well as in anonymous code blocks (DO
command), it is possible to end transactions using the commandsCOMMIT
andROLLBACK
.
Ex negativo, since functions are not procedures that are invoked with CALL
, you cannot do that in functions.
The BEGIN
that starts a block in PL/pgSQL is different from the SQL statement BEGIN
that starts a transaction.
Just remove the COMMIT
from your function, and you have the solution: since the whole function is always run inside a single transaction, any error in the third statement will lead to a ROLLBACK
that also undoes the first two statements.
Compared to other SQL languages, you should think that Postgres always takes care of the commit/rollback in case of error implicitly when you are inside a transaction.
Here is what the doc is saying:
Transactions are a fundamental concept of all database systems. The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.
CREATE OR REPLACE FUNCTION TEST1 ()
RETURNS VOID
LANGUAGE 'plpgsql'
AS $$
BEGIN
INSERT INTO table1 VALUES (1);
INSERT INTO table1 VALUES (2);
INSERT INTO table1 VALUES ('A');
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;$$;