doc/src/sgml/ref/savepoint.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-SAVEPOINT">
   7  <refmeta>
   8   <refentrytitle id="SQL-SAVEPOINT-TITLE">SAVEPOINT</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>SAVEPOINT</refname>
  15   <refpurpose>define a new savepoint within the current transaction</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-savepoint">
  19   <primary>SAVEPOINT</primary>
  20  </indexterm>
  21
  22  <indexterm zone="sql-savepoint">
  23   <primary>savepoints</primary>
  24   <secondary>defining</secondary>
  25  </indexterm>
  26
  27  <refsynopsisdiv>
  28 <synopsis>
  29 SAVEPOINT <replaceable>savepoint_name</replaceable>
  30 </synopsis>
  31  </refsynopsisdiv>
  32
  33  <refsect1>
  34   <title>Description</title>
  35
  36   <para>
  37    <command>SAVEPOINT</command> establishes a new savepoint within
  38    the current transaction.
  39   </para>
  40
  41   <para>
  42    A savepoint is a special mark inside a transaction that allows all commands
  43    that are executed after it was established to be rolled back, restoring
  44    the transaction state to what it was at the time of the savepoint.
  45   </para>
  46  </refsect1>
  47
  48  <refsect1>
  49   <title>Parameters</title>
  50
  51   <variablelist>
  52    <varlistentry>
  53     <term><replaceable>savepoint_name</replaceable></term>
  54     <listitem>
  55      <para>
  56       The name to give to the new savepoint.
  57      </para>
  58     </listitem>
  59    </varlistentry>
  60   </variablelist>
  61  </refsect1>
  62
  63  <refsect1>
  64   <title>Notes</title>
  65
  66   <para>
  67    Use <xref linkend="SQL-ROLLBACK-TO" endterm="SQL-ROLLBACK-TO-TITLE"> to
  68    rollback to a savepoint.  Use <xref linkend="SQL-RELEASE-SAVEPOINT"
  69    endterm="SQL-RELEASE-SAVEPOINT-TITLE"> to destroy a savepoint, keeping
  70    the effects of commands executed after it was established.
  71   </para>
  72
  73   <para>
  74    Savepoints can only be established when inside a transaction block.
  75    There can be multiple savepoints defined within a transaction.
  76   </para>
  77  </refsect1>
  78
  79  <refsect1>
  80   <title>Examples</title>
  81
  82   <para>
  83    To establish a savepoint and later undo the effects of all commands executed
  84    after it was established:
  85 <programlisting>
  86 BEGIN;
  87     INSERT INTO table1 VALUES (1);
  88     SAVEPOINT my_savepoint;
  89     INSERT INTO table1 VALUES (2);
  90     ROLLBACK TO SAVEPOINT my_savepoint;
  91     INSERT INTO table1 VALUES (3);
  92 COMMIT;
  93 </programlisting>
  94    The above transaction will insert the values 1 and 3, but not 2.
  95   </para>
  96
  97   <para>
  98    To establish and later destroy a savepoint:
  99 <programlisting>
 100 BEGIN;
 101     INSERT INTO table1 VALUES (3);
 102     SAVEPOINT my_savepoint;
 103     INSERT INTO table1 VALUES (4);
 104     RELEASE SAVEPOINT my_savepoint;
 105 COMMIT;
 106 </programlisting>
 107    The above transaction will insert both 3 and 4.
 108   </para>
 109  </refsect1>
 110
 111  <refsect1>
 112   <title>Compatibility</title>
 113
 114   <para>
 115    SQL requires a savepoint to be destroyed automatically when another
 116    savepoint with the same name is established.  In
 117    <productname>PostgreSQL</>, the old savepoint is kept, though only the more
 118    recent one will be used when rolling back or releasing.  (Releasing the
 119    newer savepoint will cause the older one to again become accessible to
 120    <command>ROLLBACK TO SAVEPOINT</> and <command>RELEASE SAVEPOINT</>.)
 121    Otherwise, <command>SAVEPOINT</command> is fully SQL conforming.
 122   </para>
 123  </refsect1>
 124
 125  <refsect1>
 126   <title>See Also</title>
 127
 128   <simplelist type="inline">
 129    <member><xref linkend="sql-begin" endterm="sql-begin-title"></member>
 130    <member><xref linkend="sql-commit" endterm="sql-commit-title"></member>
 131    <member><xref linkend="sql-release-savepoint" endterm="sql-release-savepoint-title"></member>
 132    <member><xref linkend="sql-rollback" endterm="sql-rollback-title"></member>
 133    <member><xref linkend="sql-rollback-to" endterm="sql-rollback-to-title"></member>
 134   </simplelist>
 135  </refsect1>
 136 </refentry>