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