| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/prepare_transaction.sgml,v 1.5 2006/09/16 00:30:19 momjian Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="SQL-PREPARE-TRANSACTION"> |
| <refmeta> |
| <refentrytitle id="sql-prepare-transaction-title">PREPARE TRANSACTION</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>PREPARE TRANSACTION</refname> |
| <refpurpose>prepare the current transaction for two-phase commit</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-prepare-transaction"> |
| <primary>PREPARE TRANSACTION</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| PREPARE TRANSACTION <replaceable class="PARAMETER">transaction_id</replaceable> |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1> |
| <title>Description</title> |
| |
| <para> |
| <command>PREPARE TRANSACTION</command> 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 probability that it can be committed |
| successfully, even if a database crash occurs before the commit is |
| requested. |
| </para> |
| |
| <para> |
| Once prepared, a transaction can later be committed or rolled back |
| with <xref linkend="sql-commit-prepared" endterm="sql-commit-prepared-title"> |
| or <xref linkend="sql-rollback-prepared" endterm="sql-rollback-prepared-title">, |
| respectively. Those commands can be issued from any session, not |
| only the one that executed the original transaction. |
| </para> |
| |
| <para> |
| From the point of view of the issuing session, <command>PREPARE |
| TRANSACTION</command> is not unlike a <command>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.) |
| </para> |
| |
| <para> |
| If the <command>PREPARE TRANSACTION</command> command fails for any |
| reason, it becomes a <command>ROLLBACK</>: the current transaction |
| is canceled. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Parameters</title> |
| |
| <variablelist> |
| <varlistentry> |
| <term><replaceable class="PARAMETER">transaction_id</replaceable></term> |
| <listitem> |
| <para> |
| An arbitrary identifier that later identifies this transaction for |
| <command>COMMIT PREPARED</> or <command>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. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| </refsect1> |
| |
| <refsect1> |
| <title>Notes</title> |
| |
| <para> |
| This command must be used inside a transaction block. Use <xref |
| linkend="sql-begin" endterm="sql-begin-title"> to start one. |
| </para> |
| |
| <para> |
| It is not currently allowed to <command>PREPARE</> a transaction that |
| has executed any operations involving temporary tables, |
| created any cursors <literal>WITH HOLD</>, or executed |
| <command>LISTEN</> or <command>UNLISTEN</>. |
| Those features are too tightly |
| tied to the current session to be useful in a transaction to be prepared. |
| </para> |
| |
| <para> |
| If the transaction modified any run-time parameters with <command>SET</>, |
| those effects persist after <command>PREPARE TRANSACTION</>, and will not |
| be affected by any later <command>COMMIT PREPARED</command> or |
| <command>ROLLBACK PREPARED</command>. Thus, in this one respect |
| <command>PREPARE TRANSACTION</> acts more like <command>COMMIT</> than |
| <command>ROLLBACK</>. |
| </para> |
| |
| <para> |
| All currently available prepared transactions are listed in the |
| <link linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link> |
| system view. |
| </para> |
| |
| <para> |
| 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 <command>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. |
| </para> |
| |
| <para> |
| If you make any serious use of prepared transactions, you will probably |
| want to increase the value of <xref |
| linkend="guc-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 |
| <xref linkend="guc-max-connections">, so that every session can have |
| a prepared transaction pending. |
| </para> |
| </refsect1> |
| |
| <refsect1 id="sql-prepare-transaction-examples"> |
| <title id="sql-prepare-transaction-examples-title">Examples</title> |
| <para> |
| Prepare the current transaction for two-phase commit, using |
| <literal>foobar</> as the transaction identifier: |
| |
| <programlisting> |
| PREPARE TRANSACTION 'foobar'; |
| </programlisting> |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <simplelist type="inline"> |
| <member><xref linkend="sql-commit-prepared" endterm="sql-commit-prepared-title"></member> |
| <member><xref linkend="sql-rollback-prepared" endterm="sql-rollback-prepared-title"></member> |
| </simplelist> |
| </refsect1> |
| |
| </refentry> |