/usr/man2/cat.l/prepare_transaction.l.Z

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 com-
       mit.  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 probability 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 or ROLLBACK PREPARED, respectively. Those commands can
       be issued from any session, not only the one that executed 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  transac-
       tion 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  to
       start one.

       It  is not currently allowed to PREPARE a transaction that has executed
       any operations involving temporary tables or created any  cursors  WITH
       HOLD.  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,  those
       effects  persist after PREPARE 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_pre-
       pared_xacts system view.

       From a performance standpoint, it is unwise to  leave  transactions  in
       the  prepared  state  for a long time: this will for instance interfere
       with the ability of VACUUM to reclaim storage. 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 make any serious use of prepared transactions, you will probably
       want to increase the value of max_prepared_transactions, as the default
       setting is quite small (to avoid wasting resources for those who  don't
       use  it).  It is recommendable to make it at least equal to max_connec-
       tions, so that every session can have a prepared transaction pending.

EXAMPLES

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

       PREPARE TRANSACTION 'foobar';

SEE ALSO

       COMMIT  PREPARED [commit_prepared(l)], ROLLBACK PREPARED [rollback_pre-
       pared(l)]

SQL - Language Statements         2005-11-05             PREPARE TRANSACTION()