doc/src/sgml/ref/savepoint.sgml

   1 <!--
   2 doc/src/sgml/ref/savepoint.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-savepoint">
   7  <indexterm zone="sql-savepoint">
   8   <primary>SAVEPOINT</primary>
   9  </indexterm>
  10
  11  <indexterm zone="sql-savepoint">
  12   <primary>savepoints</primary>
  13   <secondary>defining</secondary>
  14  </indexterm>
  15
  16  <refmeta>
  17   <refentrytitle>SAVEPOINT</refentrytitle>
  18   <manvolnum>7</manvolnum>
  19   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  20  </refmeta>
  21
  22  <refnamediv>
  23   <refname>SAVEPOINT</refname>
  24   <refpurpose>define a new savepoint within the current transaction</refpurpose>
  25  </refnamediv>
  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.  If savepoints with the
  57       same name already exist, they will be inaccessible until newer
  58       identically-named savepoints are released.
  59      </para>
  60     </listitem>
  61    </varlistentry>
  62   </variablelist>
  63  </refsect1>
  64
  65  <refsect1>
  66   <title>Notes</title>
  67
  68   <para>
  69    Use <link linkend="sql-rollback-to"><command>ROLLBACK TO</command></link> to
  70    rollback to a savepoint.  Use <link linkend="sql-release-savepoint"><command>RELEASE SAVEPOINT</command></link>
  71    to destroy a savepoint, keeping
  72    the effects of commands executed after it was established.
  73   </para>
  74
  75   <para>
  76    Savepoints can only be established when inside a transaction block.
  77    There can be multiple savepoints defined within a transaction.
  78   </para>
  79  </refsect1>
  80
  81  <refsect1>
  82   <title>Examples</title>
  83
  84   <para>
  85    To establish a savepoint and later undo the effects of all commands executed
  86    after it was established:
  87 <programlisting>
  88 BEGIN;
  89     INSERT INTO table1 VALUES (1);
  90     SAVEPOINT my_savepoint;
  91     INSERT INTO table1 VALUES (2);
  92     ROLLBACK TO SAVEPOINT my_savepoint;
  93     INSERT INTO table1 VALUES (3);
  94 COMMIT;
  95 </programlisting>
  96    The above transaction will insert the values 1 and 3, but not 2.
  97   </para>
  98
  99   <para>
 100    To establish and later destroy a savepoint:
 101 <programlisting>
 102 BEGIN;
 103     INSERT INTO table1 VALUES (3);
 104     SAVEPOINT my_savepoint;
 105     INSERT INTO table1 VALUES (4);
 106     RELEASE SAVEPOINT my_savepoint;
 107 COMMIT;
 108 </programlisting>
 109    The above transaction will insert both 3 and 4.
 110   </para>
 111
 112   <para>
 113   To use a single savepoint name:
 114 <programlisting>
 115 BEGIN;
 116     INSERT INTO table1 VALUES (1);
 117     SAVEPOINT my_savepoint;
 118     INSERT INTO table1 VALUES (2);
 119     SAVEPOINT my_savepoint;
 120     INSERT INTO table1 VALUES (3);
 121
 122     -- rollback to the second savepoint
 123     ROLLBACK TO SAVEPOINT my_savepoint;
 124     SELECT * FROM table1;               -- shows rows 1 and 2
 125
 126     -- release the second savepoint
 127     RELEASE SAVEPOINT my_savepoint;
 128
 129     -- rollback to the first savepoint
 130     ROLLBACK TO SAVEPOINT my_savepoint;
 131     SELECT * FROM table1;               -- shows only row 1
 132 COMMIT;
 133 </programlisting>
 134   The above transaction shows row 3 being rolled back first, then row 2.
 135   </para>
 136
 137  </refsect1>
 138
 139  <refsect1>
 140   <title>Compatibility</title>
 141
 142   <para>
 143    SQL requires a savepoint to be destroyed automatically when another
 144    savepoint with the same name is established.  In
 145    <productname>PostgreSQL</productname>, the old savepoint is kept, though only the more
 146    recent one will be used when rolling back or releasing.  (Releasing the
 147    newer savepoint with <command>RELEASE SAVEPOINT</command> will cause the older one
 148    to again become accessible to <command>ROLLBACK TO SAVEPOINT</command> and
 149    <command>RELEASE SAVEPOINT</command>.) Otherwise, <command>SAVEPOINT</command> is
 150    fully SQL conforming.
 151   </para>
 152  </refsect1>
 153
 154  <refsect1>
 155   <title>See Also</title>
 156
 157   <simplelist type="inline">
 158    <member><xref linkend="sql-begin"/></member>
 159    <member><xref linkend="sql-commit"/></member>
 160    <member><xref linkend="sql-release-savepoint"/></member>
 161    <member><xref linkend="sql-rollback"/></member>
 162    <member><xref linkend="sql-rollback-to"/></member>
 163   </simplelist>
 164  </refsect1>
 165 </refentry>