2 doc/src/sgml/ref/rollback_to.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-ROLLBACK-TO">
8 <refentrytitle>ROLLBACK TO SAVEPOINT</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>ROLLBACK TO SAVEPOINT</refname>
15 <refpurpose>roll back to a savepoint</refpurpose>
18 <indexterm zone="sql-rollback-to">
19 <primary>ROLLBACK TO SAVEPOINT</primary>
22 <indexterm zone="sql-rollback-to">
23 <primary>savepoints</primary>
24 <secondary>rolling back</secondary>
29 ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] <replaceable>savepoint_name</replaceable>
34 <title>Description</title>
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.
43 <command>ROLLBACK TO SAVEPOINT</> implicitly destroys all savepoints that
44 were established after the named savepoint.
49 <title>Parameters</title>
53 <term><replaceable class="PARAMETER">savepoint_name</></term>
56 The savepoint to roll back to.
67 Use <xref linkend="SQL-RELEASE-SAVEPOINT"> to destroy a savepoint
68 without discarding the effects of commands executed after it was
73 Specifying a savepoint name that has not been established is an error.
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.
95 <title>Examples</title>
98 To undo the effects of the commands executed after <literal>my_savepoint</literal>
101 ROLLBACK TO SAVEPOINT my_savepoint;
106 Cursor positions are not affected by savepoint rollback:
110 DECLARE foo CURSOR FOR SELECT 1 UNION SELECT 2;
119 ROLLBACK TO SAVEPOINT foo;
127 </programlisting></para>
133 <title>Compatibility</title>
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
148 <title>See Also</title>
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>