Contents
ROLLBACK TO SAVEPOINT
A transaction command for reverting a transaction to a previous savepoint
ROLLBACK TO SAVEPOINT is a transaction command for rolling back a transaction to a previously defined savepoint.
ROLLBACK TO SAVEPOINT was added in PostgreSQL 8.0.
Change history
- PostgreSQL 8.0
- added (initial commit cc813fc2)
Examples
Basic usage of ROLLBACK TO SAVEPOINT:
postgres=# BEGIN;
BEGIN
postgres=*# SELECT 1;
?column?
----------
1
(1 row)
Time: 0.379 ms
postgres=*# SAVEPOINT s1;
SAVEPOINT
postgres=*# SELECT foo;
ERROR: column "foo" does not exist
LINE 1: SELECT foo;
^
postgres=!# ROLLBACK TO SAVEPOINT s1;
ROLLBACK
postgres=*# SELECT 2;
?column?
----------
2
(1 row)
postgres=*# COMMIT;
COMMIT
It is possible to roll back multiple times to the same savepoint:
postgres=*# SAVEPOINT s1; SAVEPOINT postgres=*# ROLLBACK TO SAVEPOINT s1; ROLLBACK postgres=*# ROLLBACK TO SAVEPOINT s1; ROLLBACK
It is not possible to roll back to a savepoint which was previously released:
postgres=*# RELEASE SAVEPOINT s1; RELEASE postgres=*# ROLLBACK TO SAVEPOINT s1; ERROR: savepoint "s1" does not exist
Attempting to roll back to a non-existent savepoint:
postgres=*# ROLLBACK TO SAVEPOINT foo; ERROR: savepoint "foo" does not exist
References
- PostgreSQL documentation: ROLLBACK TO SAVEPOINT