PREPARE TRANSACTION(7)                                    SQL Commands                                    PREPARE TRANSACTION(7)



NAME
       PREPARE TRANSACTION - prepare the current transaction for two-phase commit


SYNOPSIS
       PREPARE TRANSACTION transaction_id


DESCRIPTION
       PREPARE  TRANSACTION  prepares  the  current  transaction for two-phase commit. After this command, the transaction is no
       longer associated with the current session; instead, its state is fully stored on disk, and there is a very  high  proba-
       bility that it can be committed successfully, even if a database crash occurs before the commit is requested.

       Once  prepared, a transaction can later be committed or rolled back with COMMIT PREPARED [commit_prepared(7)] or ROLLBACK
       PREPARED [rollback_prepared(7)], respectively. Those commands can be issued from any session, not only the one that  exe-
       cuted the original transaction.

       From  the point of view of the issuing session, PREPARE TRANSACTION is not unlike a ROLLBACK command: after executing it,
       there is no active current transaction, and the effects of the prepared transaction are no longer visible.  (The  effects
       will become visible again if the transaction is committed.)

       If the PREPARE TRANSACTION command fails for any reason, it becomes a ROLLBACK: the current transaction is canceled.

PARAMETERS
       transaction_id
              An  arbitrary  identifier  that  later  identifies this transaction for COMMIT PREPARED or ROLLBACK PREPARED.  The
              identifier must be written as a string literal, and must be less than 200 bytes long. It must not be the  same  as
              the identifier used for any currently prepared transaction.

NOTES
       This command must be used inside a transaction block. Use BEGIN [begin(7)] to start one.

       It is not currently allowed to PREPARE a transaction that has executed any operations involving temporary tables, created
       any cursors WITH HOLD, or executed LISTEN or UNLISTEN.  Those features are too tightly tied to the current session to  be
       useful in a transaction to be prepared.

       If the transaction modified any run-time parameters with SET (without the LOCAL option), those effects persist after PRE-
       PARE TRANSACTION, and will not be affected by any later COMMIT PREPARED or ROLLBACK PREPARED. Thus, in this  one  respect
       PREPARE TRANSACTION acts more like COMMIT than ROLLBACK.

       All currently available prepared transactions are listed in the pg_prepared_xacts system view.

              Caution:

              It is unwise to leave transactions in the prepared state for a long time.  This will interfere with the ability of
              VACUUM to reclaim storage, and in extreme cases could cause the database to shut down to  prevent  transaction  ID
              wraparound  (see in the documentation). Keep in mind also that the transaction continues to hold whatever locks it
              held. The intended usage of the feature is that a prepared transaction will normally be committed or  rolled  back
              as soon as an external transaction manager has verified that other databases are also prepared to commit.

              If  you  have not set up an external transaction manager to track prepared transactions and ensure they get closed
              out promptly, it is best to keep the prepared-transaction feature disabled by setting max_prepared_transactions to
              zero.  This  will prevent accidental creation of prepared transactions that might then be forgotten and eventually
              cause problems.


EXAMPLES
       Prepare the current transaction for two-phase commit, using foobar as the transaction identifier:

       PREPARE TRANSACTION 'foobar';


SEE ALSO
       COMMIT PREPARED [commit_prepared(7)], ROLLBACK PREPARED [rollback_prepared(7)]



SQL - Language Statements                                  2011-09-22                                     PREPARE TRANSACTION(7)