| <!-- |
| $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> |