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   <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"
  68    endterm="SQL-RELEASE-SAVEPOINT-TITLE"> to destroy a savepoint without
  69    discarding the effects of commands executed after it was 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
  81    <command>FETCH</> command inside a savepoint that is later rolled
  82    back, the cursor position remains at the position that <command>FETCH</>
  83    left it pointing to (that is, <command>FETCH</> is not rolled back).
  84    Closing a cursor is not undone by rolling back, either.
  85    A cursor whose execution causes a transaction to abort is put in a
  86    cannot-execute state, so while the transaction can be restored using
  87    <command>ROLLBACK TO SAVEPOINT</>, the cursor can no longer be used.
  88   </para>
  89  </refsect1>
  90
  91  <refsect1>
  92   <title>Examples</title>
  93
  94   <para>
  95    To undo the effects of the commands executed after <literal>my_savepoint</literal>
  96    was established:
  97 <programlisting>
  98 ROLLBACK TO SAVEPOINT my_savepoint;
  99 </programlisting>
 100   </para>
 101
 102   <para>
 103    Cursor positions are not affected by savepoint rollback:
 104 <programlisting>
 105 BEGIN;
 106
 107 DECLARE foo CURSOR FOR SELECT 1 UNION SELECT 2;
 108
 109 SAVEPOINT foo;
 110
 111 FETCH 1 FROM foo;
 112  ?column?
 113 ----------
 114         1
 115
 116 ROLLBACK TO SAVEPOINT foo;
 117
 118 FETCH 1 FROM foo;
 119  ?column?
 120 ----------
 121         2
 122
 123 COMMIT;
 124 </programlisting>
 125    </para>
 126
 127
 128  </refsect1>
 129
 130  <refsect1>
 131   <title>Compatibility</title>
 132
 133   <para>
 134    The <acronym>SQL</> standard specifies that the key word
 135    <literal>SAVEPOINT</> is mandatory, but <productname>PostgreSQL</>
 136    and <productname>Oracle</> allow it to be omitted.  SQL allows
 137    only <literal>WORK</>, not <literal>TRANSACTION</>, as a noise word
 138    after <literal>ROLLBACK</>.  Also, SQL has an optional clause
 139    <literal>AND [ NO ] CHAIN</> which is not currently supported by
 140    <productname>PostgreSQL</>.  Otherwise, this command conforms to
 141    the SQL standard.
 142   </para>
 143  </refsect1>
 144
 145  <refsect1>
 146   <title>See Also</title>
 147
 148   <simplelist type="inline">
 149    <member><xref linkend="sql-begin" endterm="sql-begin-title"></member>
 150    <member><xref linkend="sql-commit" endterm="sql-commit-title"></member>
 151    <member><xref linkend="sql-release-savepoint" endterm="sql-release-savepoint-title"></member>
 152    <member><xref linkend="sql-rollback" endterm="sql-rollback-title"></member>
 153    <member><xref linkend="sql-savepoint" endterm="sql-savepoint-title"></member>
 154   </simplelist>
 155  </refsect1>
 156 </refentry>