In procedures invoked by the
command as well as in anonymous code blocks (
DO command), it is possible to end transactions
using the commands
ROLLBACK. A new transaction is
started automatically after a transaction is ended using these
commands, so there is no separate
TRANSACTION command. (Note that
different meanings in PL/pgSQL.)
Here is a simple example:
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();
Transaction control is only possible in
invocations from the top level or nested
invocations without any other intervening command. For example,
if the call stack is
CALL proc1() →
CALL proc2() →
CALL proc3(), then the second and third
procedures can perform transaction control actions. But if the
call stack is
CALL proc1() →
SELECT func2() →
CALL proc3(), then the last procedure cannot do
transaction control, because of the
SELECT in between.
Special considerations apply to cursor loops. Consider this example:
CREATE PROCEDURE transaction_test2() LANGUAGE plpgsql AS $$ DECLARE r RECORD; BEGIN FOR r IN SELECT * FROM test2 ORDER BY x LOOP INSERT INTO test1 (a) VALUES (r.x); COMMIT; END LOOP; END; $$; CALL transaction_test2();
Normally, cursors are automatically closed at transaction
commit. However, a cursor created as part of a loop like this is
automatically converted to a holdable cursor by the first
ROLLBACK. That means that the cursor is fully
evaluated at the first
ROLLBACK rather than row by row. The
cursor is still removed automatically after the loop, so this is
mostly invisible to the user.
Transaction commands are not allowed in cursor loops driven by
commands that are not read-only (for example
UPDATE ... RETURNING).
A transaction cannot be ended inside a block with exception handlers.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.