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

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

Categories

SQL command, Transaction command, Transactions

See also

SAVEPOINT, RELEASE SAVEPOINT