Contents
SAVEPOINT
An SQL command for defining a new savepoint
SAVEPOINT is a transaction command for defining a new savepoint.
SAVEPOINT was added in PostgreSQL 8.0.
Usage
A savepoint is a user-defined marker within a transaction created with the SAVEPOINT command, which can be rolled back to without invalidating the entire transaction.
The savepoint must be named; the name can be arbitrary, but must follow the same rules as for other database objects such as table and column identifiers; see the PostgreSQL documentation section "Identifiers and Key Words" for further details.
Change history
- PostgreSQL 8.0
- added (initial commit cc813fc2)
Examples
Basic usage of SAVEPOINT:
postgres=# BEGIN;
BEGIN
postgres=*# SELECT 1;
?column?
----------
1
(1 row)
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
Note that savepoints can only be used within transaction blocks:
postgres=# SAVEPOINT s1; ERROR: SAVEPOINT can only be used in transaction blocks
A savepoint cannot of course be established after a transaction error:
postgres=# BEGIN;
BEGIN
postgres=*# SELECT foo;
ERROR: column "foo" does not exist
LINE 1: SELECT foo;
^
postgres=!# SAVEPOINT foo;
ERROR: current transaction is aborted, commands ignored until end of transaction block
Valid savepoint names:
postgres=*# SAVEPOINT foo; SAVEPOINT postgres=*# SAVEPOINT _foo; SAVEPOINT postgres=*# SAVEPOINT foo_bar_1; SAVEPOINT postgres=*# SAVEPOINT über1; SAVEPOINT
postgres=*# SAVEPOINT ใในใ; SAVEPOINT
postgres=*# SAVEPOINT "123"; SAVEPOINT postgres=*# SAVEPOINT "[foo]"; SAVEPOINT
Invalid savepoint names:
postgres=*# SAVEPOINT 1;
ERROR: syntax error at or near "1"
LINE 1: SAVEPOINT 1;
^
postgres=*# SAVEPOINT [foo];
ERROR: syntax error at or near "["
LINE 1: SAVEPOINT [foo];
^
References
- PostgreSQL documentation: SAVEPOINT