December 9, 2023
Summary: in this tutorial, you will learn how to handle PostgreSQL subtransactions using the
ROLLBACK TO SAVEPOINT, and
RELEASE SAVEPOINT statements.
Introduction to Subtransaction
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.
A savepoint is a feature that allows you to create a named point within a transaction to which you can later roll back, while leaving the rest of the transaction intact. Savepoints are useful when you want to handle errors or exceptions within a transaction and selectively roll back to a specific point in the transaction without having to undo all the changes made so far.
How it works
Here’s how you can work with savepoints in PostgreSQL:
Start a transaction using the
This begins a new transaction.
Within the transaction, you can create a savepoint using the
SAVEPOINT statement, giving it a name:
In this example, a savepoint named
my_savepoint is created within the transaction.
Perform one or more SQL operations (e.g.,
DELETE, etc.) within the transaction.
If at any point you want to roll back to the savepoint, you can use the
ROLLBACK TO statement:
ROLLBACK TO my_savepoint;
This will undo all changes made after the
my_savepoint savepoint was created, effectively reverting the transaction to that point.
You can also release a savepoint using the
This removes the savepoint and allows the transaction to continue from that point.
Finally, when you’re ready to commit all the changes made within the transaction, you can use the
This saves all the changes made within the transaction to the database.
Here’s a complete example:
-- Perform some operations within the transaction
INSERT INTO employees (name, salary) VALUES ('John', 50000);
-- Continue with more operations
UPDATE departments SET manager_id = 1 WHERE department_name = 'HR';
-- Something went wrong, let's roll back to the savepoint
ROLLBACK TO my_savepoint;
-- Continue with other operations or release the savepoint
-- RELEASE my_savepoint;
-- Finally, when everything is fine, commit the transaction
Savepoints allow you to have finer control over transactions, especially in complex scenarios where you want to handle errors gracefully and selectively roll back to specific points within a transaction.
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.
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.