Mastering PostgreSQL 9.6

Making use of savepoints

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 zero test=# ROLLBACK TO SAVEPOINT a; ROLLBACK test=# SELECT 3;  ?column?  ----------         3 (1 row) test=# COMMIT; COMMIT 

After the first SELECT clause, I decided to create a SAVEPOINT to make sure that the application can always ...