PostgreSQL Tutorial: Subtransaction

December 9, 2023

Summary: in this tutorial, you will learn how to handle PostgreSQL subtransactions using the SAVEPOINT, 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 BEGIN statement:

BEGIN;

This begins a new transaction.

Within the transaction, you can create a savepoint using the SAVEPOINT statement, giving it a name:

SAVEPOINT my_savepoint;

In this example, a savepoint named my_savepoint is created within the transaction.

Perform one or more SQL operations (e.g., INSERT, UPDATE, 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 RELEASE statement:

RELEASE my_savepoint;

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 COMMIT statement:

COMMIT;

This saves all the changes made within the transaction to the database.

Example

Here’s a complete example:

BEGIN;
-- Perform some operations within the transaction
INSERT INTO employees (name, salary) VALUES ('John', 50000);
SAVEPOINT my_savepoint;
-- 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
COMMIT;

Summary

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.

comments powered by Disqus