doc/src/sgml/ref/rollback_to.sgml

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