ROLLBACK TO SAVEPOINTSection: SQL Commands (7)
Index Return to Main Contents
NAMEROLLBACK TO SAVEPOINT - roll back to a savepoint
ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name
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.
- The savepoint to roll back to.
Use RELEASE SAVEPOINT [release_savepoint(7)] to destroy a savepoint without discarding the effects of commands executed after it was established.
Specifying a savepoint name that has not been established is an error.
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 FETCH command inside a savepoint that is later rolled back, the cursor position remains at the position that FETCH left it pointing to (that is, 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 cannot-execute state, so while the transaction can be restored using ROLLBACK TO SAVEPOINT, the cursor can no longer be used.
To undo the effects of the commands executed after my_savepoint was established:
ROLLBACK TO SAVEPOINT my_savepoint;
Cursor positions are not affected by savepoint rollback:
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;
The SQL standard specifies that the key word SAVEPOINT is mandatory, but PostgreSQL and Oracle allow it to be omitted. SQL allows only WORK, not TRANSACTION, as a noise word after ROLLBACK. Also, SQL has an optional clause AND [ NO ] CHAIN which is not currently supported by PostgreSQL. Otherwise, this command conforms to the SQL standard.
SEE ALSOBEGIN [begin(7)], COMMIT [commit(7)], RELEASE SAVEPOINT [release_savepoint(7)], ROLLBACK [rollback(7)], SAVEPOINT [savepoint(7)]
This document was created by man2html, using the manual pages.
Time: 05:34:16 GMT, December 24, 2015