Home Explore Blog CI



postgresql

11th chunk of `doc/src/sgml/protocol.sgml`
87470303214829676dfb1e73a4ac8b04a708c1b892025bea0000000100000fa2

</programlisting>
     then the divide-by-zero failure in the <command>SELECT</command> will force
     rollback of the first <command>INSERT</command>.  Furthermore, because
     execution of the message is abandoned at the first error, the second
     <command>INSERT</command> is never attempted at all.
    </para>

    <para>
     If instead the message contains
<programlisting>
BEGIN;
INSERT INTO mytable VALUES(1);
COMMIT;
INSERT INTO mytable VALUES(2);
SELECT 1/0;
</programlisting>
     then the first <command>INSERT</command> is committed by the
     explicit <command>COMMIT</command> command.  The second <command>INSERT</command>
     and the <command>SELECT</command> are still treated as a single transaction,
     so that the divide-by-zero failure will roll back the
     second <command>INSERT</command>, but not the first one.
    </para>

    <para>
     This behavior is implemented by running the statements in a
     multi-statement Query message in an <firstterm>implicit transaction
     block</firstterm> unless there is some explicit transaction block for them to
     run in.  The main difference between an implicit transaction block and
     a regular one is that an implicit block is closed automatically at the
     end of the Query message, either by an implicit commit if there was no
     error, or an implicit rollback if there was an error.  This is similar
     to the implicit commit or rollback that happens for a statement
     executed by itself (when not in a transaction block).
    </para>

    <para>
     If the session is already in a transaction block, as a result of
     a <command>BEGIN</command> in some previous message, then the Query message
     simply continues that transaction block, whether the message contains
     one statement or several.  However, if the Query message contains
     a <command>COMMIT</command> or <command>ROLLBACK</command> closing the existing
     transaction block, then any following statements are executed in an
     implicit transaction block.
     Conversely, if a <command>BEGIN</command> appears in a multi-statement Query
     message, then it starts a regular transaction block that will only be
     terminated by an explicit <command>COMMIT</command> or <command>ROLLBACK</command>,
     whether that appears in this Query message or a later one.
     If the <command>BEGIN</command> follows some statements that were executed as
     an implicit transaction block, those statements are not immediately
     committed; in effect, they are retroactively included into the new
     regular transaction block.
    </para>

    <para>
     A <command>COMMIT</command> or <command>ROLLBACK</command> appearing in an implicit
     transaction block is executed as normal, closing the implicit block;
     however, a warning will be issued since a <command>COMMIT</command>
     or <command>ROLLBACK</command> without a previous <command>BEGIN</command> might
     represent a mistake.  If more statements follow, a new implicit
     transaction block will be started for them.
    </para>

    <para>
     Savepoints are not allowed in an implicit transaction block, since
     they would conflict with the behavior of automatically closing the
     block upon any error.
    </para>

    <para>
     Remember that, regardless of any transaction control commands that may
     be present, execution of the Query message stops at the first error.
     Thus for example given
<programlisting>
BEGIN;
SELECT 1/0;
ROLLBACK;
</programlisting>
     in a single Query message, the session will be left inside a failed
     regular transaction block, since the <command>ROLLBACK</command> is not
     reached after the divide-by-zero error.  Another <command>ROLLBACK</command>
     will be needed to restore the session to a usable state.
    </para>

    <para>
     Another behavior of note is that initial lexical and syntactic
     analysis is done on the entire query string before any of it is
     executed.

Title: Transaction Handling in PostgreSQL Query Protocol
Summary
PostgreSQL handles transactions in query messages, with implicit transaction blocks created for multi-statement queries, and explicit transaction control commands such as BEGIN, COMMIT, and ROLLBACK affecting the behavior of these blocks, including error handling, savepoint restrictions, and query execution termination upon error.