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