doc/src/sgml/ref/rollback_to.sgml

   1 <!--
   2 doc/src/sgml/ref/rollback_to.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-rollback-to">
   7  <indexterm zone="sql-rollback-to">
   8   <primary>ROLLBACK TO SAVEPOINT</primary>
   9  </indexterm>
  10
  11  <indexterm zone="sql-rollback-to">
  12   <primary>savepoints</primary>
  13   <secondary>rolling back</secondary>
  14  </indexterm>
  15
  16  <refmeta>
  17   <refentrytitle>ROLLBACK TO SAVEPOINT</refentrytitle>
  18   <manvolnum>7</manvolnum>
  19   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  20  </refmeta>
  21
  22  <refnamediv>
  23   <refname>ROLLBACK TO SAVEPOINT</refname>
  24   <refpurpose>roll back to a savepoint</refpurpose>
  25  </refnamediv>
  26
  27  <refsynopsisdiv>
  28 <synopsis>
  29 ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] <replaceable>savepoint_name</replaceable>
  30 </synopsis>
  31  </refsynopsisdiv>
  32
  33  <refsect1>
  34   <title>Description</title>
  35
  36   <para>
  37    Roll back all commands that were executed after the savepoint was
  38    established and then start a new subtransaction at the same transaction level.
  39    The savepoint remains valid and can be rolled back to again later,
  40    if needed.
  41   </para>
  42
  43   <para>
  44    <command>ROLLBACK TO SAVEPOINT</command> implicitly destroys all savepoints that
  45    were established after the named savepoint.
  46   </para>
  47  </refsect1>
  48
  49  <refsect1>
  50   <title>Parameters</title>
  51
  52   <variablelist>
  53    <varlistentry>
  54     <term><replaceable class="parameter">savepoint_name</replaceable></term>
  55     <listitem>
  56      <para>
  57       The savepoint to roll back to.
  58      </para>
  59     </listitem>
  60    </varlistentry>
  61   </variablelist>
  62  </refsect1>
  63
  64  <refsect1>
  65   <title>Notes</title>
  66
  67   <para>
  68    Use <link linkend="sql-release-savepoint"><command>RELEASE SAVEPOINT</command></link> to destroy a savepoint
  69    without discarding the effects of commands executed after it was
  70    established.
  71   </para>
  72
  73   <para>
  74    Specifying a savepoint name that has not been established is an error.
  75   </para>
  76
  77   <para>
  78    Cursors have somewhat non-transactional behavior with respect to
  79    savepoints.  Any cursor that is opened inside a savepoint will be closed
  80    when the savepoint is rolled back.  If a previously opened cursor is
  81    affected by a <command>FETCH</command> or <command>MOVE</command> command inside a
  82    savepoint that is later rolled back, the cursor remains at the
  83    position that <command>FETCH</command> left it pointing to (that is, the cursor
  84    motion caused by <command>FETCH</command> is not rolled back).
  85    Closing a cursor is not undone by rolling back, either.
  86    However, other side-effects caused by the cursor's query (such as
  87    side-effects of volatile functions called by the query) <emphasis>are</emphasis>
  88    rolled back if they occur during a savepoint that is later rolled back.
  89    A cursor whose execution causes a transaction to abort is put in a
  90    cannot-execute state, so while the transaction can be restored using
  91    <command>ROLLBACK TO SAVEPOINT</command>, the cursor can no longer be used.
  92   </para>
  93  </refsect1>
  94
  95  <refsect1>
  96   <title>Examples</title>
  97
  98   <para>
  99    To undo the effects of the commands executed after <literal>my_savepoint</literal>
 100    was established:
 101 <programlisting>
 102 ROLLBACK TO SAVEPOINT my_savepoint;
 103 </programlisting>
 104   </para>
 105
 106   <para>
 107    Cursor positions are not affected by savepoint rollback:
 108 <programlisting>
 109 BEGIN;
 110
 111 DECLARE foo CURSOR FOR SELECT 1 UNION SELECT 2;
 112
 113 SAVEPOINT foo;
 114
 115 FETCH 1 FROM foo;
 116  ?column?
 117 ----------
 118         1
 119
 120 ROLLBACK TO SAVEPOINT foo;
 121
 122 FETCH 1 FROM foo;
 123  ?column?
 124 ----------
 125         2
 126
 127 COMMIT;
 128 </programlisting></para>
 129
 130
 131  </refsect1>
 132
 133  <refsect1>
 134   <title>Compatibility</title>
 135
 136   <para>
 137    The <acronym>SQL</acronym> standard specifies that the key word
 138    <literal>SAVEPOINT</literal> is mandatory, but <productname>PostgreSQL</productname>
 139    and <productname>Oracle</productname> allow it to be omitted.  SQL allows
 140    only <literal>WORK</literal>, not <literal>TRANSACTION</literal>, as a noise word
 141    after <literal>ROLLBACK</literal>.  Also, SQL has an optional clause
 142    <literal>AND [ NO ] CHAIN</literal> which is not currently supported by
 143    <productname>PostgreSQL</productname>.  Otherwise, this command conforms to
 144    the SQL standard.
 145   </para>
 146  </refsect1>
 147
 148  <refsect1>
 149   <title>See Also</title>
 150
 151   <simplelist type="inline">
 152    <member><xref linkend="sql-begin"/></member>
 153    <member><xref linkend="sql-commit"/></member>
 154    <member><xref linkend="sql-release-savepoint"/></member>
 155    <member><xref linkend="sql-rollback"/></member>
 156    <member><xref linkend="sql-savepoint"/></member>
 157   </simplelist>
 158  </refsect1>
 159 </refentry>