PostgreSQL Tutorial: Subtransaction

Summary: in this tutorial, you will learn how to handle PostgreSQL subtransactions using the SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT statements.

Table of Contents

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:

CREATE TABLE test0 AS SELECT 1 AS i;
-- Start a main transaction
BEGIN;
-- Perform some operations within the transaction
UPDATE test0 SET i = i + 1;
-- Start a subtransaction
SAVEPOINT s1;
-- Continue with more operations
UPDATE test0 SET i = i - 1000;
-- Check the content in the table
SELECT * FROM test0;
  i
------
 -998
(1 row)
-- Something went wrong, let's roll back to the savepoint
ROLLBACK TO SAVEPOINT s1;
-- Continue with other operations
UPDATE test0 SET i = i + 1;
-- Finally, when everything is fine, commit the transaction
COMMIT;
-- Check the content in the table again
SELECT * FROM test0;
 i
---
 3
(1 row)

Who is using subtransactions?

Today, the majority of the popular ORMs and frameworks support nested transactions natively. Some examples:

• Django
• SQLAlchemy
• Ruby on Rails / Active Record
• Spring Framework
• GORM

Besides SAVEPOINTs, there are other ways to create subtransactions:

• BEGIN / EXCEPTION WHEN .. / END blocks in PL/pgSQL code (the official documentation does not describe it well; explored, for example, in this article: “PL/PgSQL Exception and Subtransactions”).
• plpy.subtransaction() in PL/Python code.
• Set ON_ERROR_ROLLBACK variable to on in psql.
• Specifies autosave connection parameter with JDBC driver.

One may assume that many applications that use PL/pgSQL or PL/Python functions use subtransactions. Systems that run API built on PostgREST, Supabase, Hasura might have PL/pgSQL functions (including trigger functions) that involve BEGIN / EXCEPTION WHEN .. / END blocks; in such cases, those systems use subtransactions.

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.