</sect1>

  <sect1 id="plpgsql-transactions">
   <title>Transaction Management</title>

   <para>
    In procedures invoked by the <command>CALL</command> command
    as well as in anonymous code blocks (<command>DO</command> command),
    it is possible to end transactions using the
    commands <command>COMMIT</command> and <command>ROLLBACK</command>.  A new
    transaction is started automatically after a transaction is ended using
    these commands, so there is no separate <command>START
    TRANSACTION</command> command.  (Note that <command>BEGIN</command> and
    <command>END</command> have different meanings in PL/pgSQL.)
   </para>

   <para>
    Here is a simple example:
<programlisting>
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();
</programlisting>
   </para>

   <indexterm zone="plpgsql-transaction-chain">
    <primary>chained transactions</primary>
    <secondary>in PL/pgSQL</secondary>
   </indexterm>

   <para id="plpgsql-transaction-chain">
    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
    <command>COMMIT AND CHAIN</command> and <command>ROLLBACK AND
    CHAIN</command> accomplish this.
   </para>

   <para>
    Transaction control is only possible in <command>CALL</command> or
    <command>DO</command> invocations from the top level or nested
    <command>CALL</command> or <command>DO</command> invocations without any
    other intervening command.  For example, if the call stack is
    <command>CALL proc1()</command> &rarr; <command>CALL proc2()</command>
    &rarr; <command>CALL proc3()</command>, then the second and third
    procedures can perform transaction control actions.  But if the call stack
    is <command>CALL proc1()</command> &rarr; <command>SELECT
    func2()</command> &rarr; <command>CALL proc3()</command>, then the last
    procedure cannot do transaction control, because of the
    <command>SELECT</command> in between.
   </para>

   <para>
    <application>PL/pgSQL</application> does not support savepoints
    (<command>SAVEPOINT</command>/<command>ROLLBACK TO
    SAVEPOINT</command>/<command>RELEASE SAVEPOINT</command> commands).
    Typical usage patterns for savepoints can be replaced by blocks with
    exception handlers (see <xref linkend="plpgsql-error-trapping"/>).
    Under the hood, a block with exception handlers forms a
    subtransaction, which means that transactions cannot be ended inside
    such a block.
   </para>

   <para>
    Special considerations apply to cursor loops.  Consider this example:
<programlisting>
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();
</programlisting>
    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 <command>COMMIT</command> or
    <command>ROLLBACK</command>.  That means that the cursor is fully
    evaluated at the first <command>COMMIT</command> or
    <command>ROLLBACK</command> rather than row by row.  The cursor is still
    removed automatically after the loop, so this is mostly invisible to the
    user.  But one must keep in mind that any table or row locks taken by
    the cursor's query will no longer be held after the
    first <command>COMMIT</command> or
    <command>ROLLBACK</command>.
   </para>

   <para>