Mastering PostgreSQL 10

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 the problem, users can utilize something called SAVEPOINT. As the name indicates, it is a safe place inside a transaction that the application can return to in the event 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; 
ERROR:  division by zerotest=# SELECT 2; 
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 

After the first ...