doc/src/sgml/ref/prepare_transaction.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-PREPARE-TRANSACTION">
   7  <refmeta>
   8   <refentrytitle id="sql-prepare-transaction-title">PREPARE TRANSACTION</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>PREPARE TRANSACTION</refname>
  15   <refpurpose>prepare the current transaction for two-phase commit</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-prepare-transaction">
  19   <primary>PREPARE TRANSACTION</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 PREPARE TRANSACTION <replaceable class="PARAMETER">transaction_id</replaceable>
  25 </synopsis>
  26  </refsynopsisdiv>
  27
  28  <refsect1>
  29   <title>Description</title>
  30
  31   <para>
  32    <command>PREPARE TRANSACTION</command> prepares the current transaction
  33    for two-phase commit. After this command, the transaction is no longer
  34    associated with the current session; instead, its state is fully stored on
  35    disk, and there is a very high probability that it can be committed
  36    successfully, even if a database crash occurs before the commit is
  37    requested.
  38   </para>
  39
  40   <para>
  41    Once prepared, a transaction can later be committed or rolled back
  42    with <xref linkend="sql-commit-prepared" endterm="sql-commit-prepared-title">
  43    or <xref linkend="sql-rollback-prepared" endterm="sql-rollback-prepared-title">,
  44    respectively.  Those commands can be issued from any session, not
  45    only the one that executed the original transaction.
  46   </para>
  47
  48   <para>
  49    From the point of view of the issuing session, <command>PREPARE
  50    TRANSACTION</command> is not unlike a <command>ROLLBACK</> command:
  51    after executing it, there is no active current transaction, and the
  52    effects of the prepared transaction are no longer visible.  (The effects
  53    will become visible again if the transaction is committed.)
  54   </para>
  55
  56   <para>
  57    If the <command>PREPARE TRANSACTION</command> command fails for any
  58    reason, it becomes a <command>ROLLBACK</>: the current transaction
  59    is canceled.
  60   </para>
  61  </refsect1>
  62
  63  <refsect1>
  64   <title>Parameters</title>
  65
  66   <variablelist>
  67    <varlistentry>
  68     <term><replaceable class="PARAMETER">transaction_id</replaceable></term>
  69     <listitem>
  70      <para>
  71       An arbitrary identifier that later identifies this transaction for
  72       <command>COMMIT PREPARED</> or <command>ROLLBACK PREPARED</>.
  73       The identifier must be written as a string literal, and must be
  74       less than 200 bytes long.  It must not be the same as the identifier
  75       used for any currently prepared transaction.
  76      </para>
  77     </listitem>
  78    </varlistentry>
  79   </variablelist>
  80  </refsect1>
  81
  82  <refsect1>
  83   <title>Notes</title>
  84
  85   <para>
  86    This command must be used inside a transaction block. Use <xref
  87    linkend="sql-begin" endterm="sql-begin-title"> to start one.
  88   </para>
  89
  90   <para>
  91    It is not currently allowed to <command>PREPARE</> a transaction that
  92    has executed any operations involving temporary tables,
  93    created any cursors <literal>WITH HOLD</>, or executed
  94    <command>LISTEN</> or <command>UNLISTEN</>.
  95    Those features are too tightly
  96    tied to the current session to be useful in a transaction to be prepared.
  97   </para>
  98
  99   <para>
 100    If the transaction modified any run-time parameters with <command>SET</>
 101    (without the <literal>LOCAL</> option),
 102    those effects persist after <command>PREPARE TRANSACTION</>, and will not
 103    be affected by any later <command>COMMIT PREPARED</command> or
 104    <command>ROLLBACK PREPARED</command>.  Thus, in this one respect
 105    <command>PREPARE TRANSACTION</> acts more like <command>COMMIT</> than
 106    <command>ROLLBACK</>.
 107   </para>
 108
 109   <para>
 110    All currently available prepared transactions are listed in the
 111    <link linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link>
 112    system view.
 113   </para>
 114
 115   <caution>
 116    <para>
 117     It is unwise to leave transactions in the prepared state for a long time.
 118     This will interfere with the ability of <command>VACUUM</> to reclaim
 119     storage, and in extreme cases could cause the database to shut down
 120     to prevent transaction ID wraparound (see <xref
 121     linkend="vacuum-for-wraparound">).  Keep in mind also that the transaction
 122     continues to hold whatever locks it held.  The intended usage of the
 123     feature is that a prepared transaction will normally be committed or
 124     rolled back as soon as an external transaction manager has verified that
 125     other databases are also prepared to commit.
 126    </para>
 127
 128    <para>
 129     If you have not set up an external transaction manager to track prepared
 130     transactions and ensure they get closed out promptly, it is best to keep
 131     the prepared-transaction feature disabled by setting
 132     <xref linkend="guc-max-prepared-transactions"> to zero.  This will
 133     prevent accidental creation of prepared transactions that might then
 134     be forgotten and eventually cause problems.
 135    </para>
 136   </caution>
 137  </refsect1>
 138
 139  <refsect1 id="sql-prepare-transaction-examples">
 140   <title id="sql-prepare-transaction-examples-title">Examples</title>
 141   <para>
 142    Prepare the current transaction for two-phase commit, using
 143    <literal>foobar</> as the transaction identifier:
 144
 145 <programlisting>
 146 PREPARE TRANSACTION 'foobar';
 147 </programlisting>
 148   </para>
 149  </refsect1>
 150
 151  <refsect1>
 152   <title>See Also</title>
 153
 154   <simplelist type="inline">
 155    <member><xref linkend="sql-commit-prepared" endterm="sql-commit-prepared-title"></member>
 156    <member><xref linkend="sql-rollback-prepared" endterm="sql-rollback-prepared-title"></member>
 157   </simplelist>
 158  </refsect1>
 159
 160 </refentry>