3
PostgreSQL documentation
6
<refentry id="SQL-ROLLBACK-TO">
8
<refentrytitle id="SQL-ROLLBACK-TO-TITLE">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"
68
endterm="SQL-RELEASE-SAVEPOINT-TITLE"> to destroy a savepoint without
69
discarding the effects of commands executed after it was established.
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
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.
92
<title>Examples</title>
95
To undo the effects of the commands executed after <literal>my_savepoint</literal>
98
ROLLBACK TO SAVEPOINT my_savepoint;
103
Cursor positions are not affected by savepoint rollback:
107
DECLARE foo CURSOR FOR SELECT 1 UNION SELECT 2;
116
ROLLBACK TO SAVEPOINT foo;
131
<title>Compatibility</title>
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
146
<title>See Also</title>
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>