Mastering PostgreSQL 12 - Third Edition

Making use of SAVEPOINT

In professional applications, it can be pretty hard to write reasonably long transactions without ever encountering a single error. To solve this problem, users can utilize something called SAVEPOINT. As the name indicates, a savepoint is a safe place inside a transaction that the application can return to if things go terribly wrong. Here is an example:

test=# BEGIN; 
BEGIN test=# SELECT 1; 
 ?column?  
---------- 
        1 
(1 row) 
test=# SAVEPOINT a; 
SAVEPOINT test=# SELECT 2 / 0; 
psql: ERROR:  division by zerotest=# SELECT 2; 
psql: ERROR:  current transaction is aborted, commands ignored until end of transaction block test=# ROLLBACK TO SAVEPOINT a; 
ROLLBACK test=# SELECT 3; 
 ?column?  
---------- 
        3(1 row) 
test=# COMMIT; 
COMMIT