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 zero
test=# 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 

After the first SELECT clause, I decided to create a savepoint to make sure that the application can always return to this point inside the transaction. As you can see, the savepoint has a name, which is referred to later.

After returning to the savepoint called a, the transaction can proceed normally. The code has jumped back to before the error, so everything is fine.

The number of savepoints inside a transaction is practically unlimited. We have seen customers with over 250,000 savepoints in a single operation. PostgreSQL can easily handle this.

If you want to remove a savepoint from inside a transaction, there's the RELEASE SAVEPOINT command:

test=# \h RELEASE
Command: RELEASE SAVEPOINT
Description: destroy a previously defined savepoint
Syntax:
RELEASE [ SAVEPOINT ] savepoint_name

URL: https://www.postgresql.org/docs/12/sql-release-savepoint.html 

Many people ask what will happen if you try to reach a savepoint after a transaction has ended. The answer is that the life of a savepoint ends as soon as the transaction ends. In other words, there is no way to return to a certain point in time after the transactions have been completed.