doc/src/sgml/ref/rollback_to.sgml

<!--
$PostgreSQL: pgsql/doc/src/sgml/ref/rollback_to.sgml,v 1.8 2006/09/16 00:30:20 momjian Exp $
PostgreSQL documentation
-->

<refentry id="SQL-ROLLBACK-TO">
 <refmeta>
  <refentrytitle id="SQL-ROLLBACK-TO-TITLE">ROLLBACK TO SAVEPOINT</refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>ROLLBACK TO SAVEPOINT</refname>
  <refpurpose>roll back to a savepoint</refpurpose>
 </refnamediv>

 <indexterm zone="sql-rollback-to">
  <primary>ROLLBACK TO SAVEPOINT</primary>
 </indexterm>

 <indexterm zone="sql-rollback-to">
  <primary>savepoints</primary>
  <secondary>rolling back</secondary>
 </indexterm>

 <refsynopsisdiv>
<synopsis>
ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] <replaceable>savepoint_name</replaceable>
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   Roll back all commands that were executed after the savepoint was
   established.  The savepoint remains valid and can be rolled back to
   again later, if needed.
  </para>

  <para>
   <command>ROLLBACK TO SAVEPOINT</> implicitly destroys all savepoints that
   were established after the named savepoint.
  </para>
 </refsect1>

 <refsect1>
  <title>Parameters</title>

  <variablelist>
   <varlistentry>
    <term><replaceable class="PARAMETER">savepoint_name</></term>
    <listitem>
     <para>
      The savepoint to roll back to.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1>
  <title>Notes</title>

  <para>
   Use <xref linkend="SQL-RELEASE-SAVEPOINT"
   endterm="SQL-RELEASE-SAVEPOINT-TITLE"> to destroy a savepoint without
   discarding the effects of commands executed after it was established.
  </para>

  <para>
   Specifying a savepoint name that has not been established is an error.
  </para>

  <para>
   Cursors have somewhat non-transactional behavior with respect to
   savepoints.  Any cursor that is opened inside a savepoint will be closed
   when the savepoint is rolled back.  If a previously opened cursor is
   affected by a 
   <command>FETCH</> command inside a savepoint that is later rolled
   back, the cursor position remains at the position that <command>FETCH</>
   left it pointing to (that is, <command>FETCH</> is not rolled back).
   Closing a cursor is not undone by rolling back, either.
   A cursor whose execution causes a transaction to abort is put in a
   can't-execute state, so while the transaction can be restored using
   <command>ROLLBACK TO SAVEPOINT</>, the cursor can no longer be used.
  </para>
 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   To undo the effects of the commands executed after <literal>my_savepoint</literal>
   was established:
<programlisting>
ROLLBACK TO SAVEPOINT my_savepoint;
</programlisting>
  </para>

  <para>
   Cursor positions are not affected by savepoint rollback:
<programlisting>
BEGIN;

DECLARE foo CURSOR FOR SELECT 1 UNION SELECT 2;

SAVEPOINT foo;

FETCH 1 FROM foo;
 ?column? 
----------
        1

ROLLBACK TO SAVEPOINT foo;

FETCH 1 FROM foo;
 ?column? 
----------
        2

COMMIT;
</programlisting>
   </para>


 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   The <acronym>SQL</> standard specifies that the key word
   <literal>SAVEPOINT</> is mandatory, but <productname>PostgreSQL</>
   and <productname>Oracle</> allow it to be omitted.  SQL allows
   only <literal>WORK</>, not <literal>TRANSACTION</>, as a noise word
   after <literal>ROLLBACK</>.  Also, SQL has an optional clause
   <literal>AND [ NO ] CHAIN</> which is not currently supported by
   <productname>PostgreSQL</>.  Otherwise, this command conforms to
   the SQL standard.
  </para>
 </refsect1>

 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-begin" endterm="sql-begin-title"></member>
   <member><xref linkend="sql-commit" endterm="sql-commit-title"></member>
   <member><xref linkend="sql-release-savepoint" endterm="sql-release-savepoint-title"></member>
   <member><xref linkend="sql-rollback" endterm="sql-rollback-title"></member>
   <member><xref linkend="sql-savepoint" endterm="sql-savepoint-title"></member>
  </simplelist>
 </refsect1>
</refentry>