In procedures invoked by the CALL
command
as well as in anonymous code blocks (DO
command),
it is possible to end transactions using the
commands COMMIT
and ROLLBACK
. A new
transaction is started automatically after a transaction is ended using
these commands, so there is no separate START
TRANSACTION
command. (Note that BEGIN
and
END
have 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();
A new transaction starts out with default transaction characteristics such
as transaction isolation level. In cases where transactions are committed
in a loop, it might be desirable to start new transactions automatically
with the same characteristics as the previous one. The commands
COMMIT AND CHAIN
and ROLLBACK AND
CHAIN
accomplish this.
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. 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 COMMIT
or
ROLLBACK
. That means that the cursor is fully
evaluated at the first COMMIT
or
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.