By John Doe December 18, 2024
Summary: In this article, we will learn some caveats for prepared transactions in PostgreSQL.
Table of Contents
Introduction
PostgreSQL gives you the possibility for two-phase commit. You’ll might need that if you want an atomic distributed commit. If you check the PostgreSQL documentation there is a clear warning about using these kind of transactions: “Unless you’re writing a transaction manager, you probably shouldn’t be using PREPARE TRANSACTION”. If you really need to use them, you need to be very careful, that prepared transactions are committed or rollback-ed as soon as possible. In other words, you need a mechanism that monitors the prepared transactions in your database and takes appropriate action if they are kept open too long. If this happens you will run into various issues and it is not immediately obvious where your issues come from.
Caveats for prepared transactions
To start with, lets create a simple prepared transaction:
begin;
create table t1 (a int);
insert into t1 values (1);
prepare transaction 'abc';
From this point on, the transaction is not anymore associated with the session. You can verify that easily if you try to commit or rollback the transaction:
commit;
WARNING: there is no transaction in progress
This also means that the “t1” table that was created before we prepared the transaction is not visible to us:
select * from t1;
ERROR: relation "t1" does not exist
LINE 1: select * from t1;
^
Although we are not in any visible transaction anymore, there are locks in the background because of our prepared transaction:
select * from pg_locks
where database = (select oid from pg_database where datname = 'postgres')
and mode like '%Exclusive%';
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+---------------------+---------+----------+-----------
relation | 12969 | 24582 | | | | | | | | -1/562 | | RowExclusiveLock | t | f |
relation | 12969 | 24582 | | | | | | | | -1/562 | | AccessExclusiveLock | t | f |
(2 rows)
There is one AccessExclusiveLock lock, wihch is the lock on the “t1” table. The other lock, “RowExclusiveLock”, is the lock that protects the row we inserted above. How can we know that? Well, currently this is only a guess, as the “t1” table is not visible:
select relname from pg_class where oid = 24582;
relname
---------
(0 rows)
Once we commit the prepared transaction, we can verify, that it really was about “t1”:
commit prepared 'abc';
select relname from pg_class where oid = 24582;
relname
---------
t1
(1 row)
select * from t1;
a
---
1
(1 row)
We can also confirm that by again taking a look the locks:
select * from pg_locks
where database = (select oid from pg_database where datname = 'postgres')
and mode like '%Exclusive%';
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+------+---------+----------+-----------
(0 rows)
These locks are gone as well. So, not a big deal, as soon as the prepared transaction is committed all is fine. This is the good case and if it goes like that you will probabyl not hit any issue.
Lets create another prepared transaction:
begin;
insert into t1 values(2);
prepare transaction 'abc';
First point to remember: Once you create a prepared transaction it is fully stored on disk:
ls -la $PGDATA/pg_twophase/*
-rw------- 1 postgres postgres 212 Feb 26 11:24 /db/pgsql/data/pg_twophase/00000233
Once it is committed the file is gone:
commit prepared 'abc';
ls -la $PGDATA/pg_twophase/
drwx------ 2 postgres postgres 4096 Feb 26 11:26 .
drwx------ 20 postgres postgres 4096 Feb 26 10:49 ..
Why is that? The answer is, that a prepared transaction even can be committed or rollback-ed if the server crashes. But this also means, that prepared transactions are persistent across restarts of the instance:
begin;
insert into t1 values(3);
prepare transaction 'abc';
$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
$ ls -la $PGDATA/pg_twophase/
drwx------ 2 postgres postgres 4096 Feb 26 11:28 .
drwx------ 20 postgres postgres 4096 Feb 26 11:28 ..
-rw------- 1 postgres postgres 212 Feb 26 11:28 00000234
Is that an issue? Imagine someone prepared a transaction and forgot to commit or rollback the transaction. A few days later someone wants to modify the application and tries to add a column to the “t1” table:
ALTER TABLE t1 ADD COLUMN b text;
This will be blocked for no obvious reason. Looking at the locks once more:
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------------+---------+----------+-------------------------------
relation | 12969 | 24582 | | | | | | | | 3/4 | 10591 | AccessExclusiveLock | f | f | 2021-02-26 11:30:30.303512+01
relation | 12969 | 24582 | | | | | | | | -1/564 | | RowExclusiveLock | t | f |
(2 rows)
We can see that pid 10591 is trying to get the look but cannot get in (granted = ‘f’). The other entry has no pid entry and this is the prepared transaction. The pid will always be empty for prepared transactions, so if you already know this, it might point you to the correct solution for this. If you don’t, then you are almost stuck. There is no session you can terminate, as nothing is reported about that in pg_stat_activity:
select datid, datname, pid, wait_event_type, wait_event, state, backend_type from pg_stat_activity;
datid | datname | pid | wait_event_type | wait_event | state | backend_type
-------+----------+-------+-----------------+---------------------+--------+------------------------------
| | 10582 | Activity | AutoVacuumMain | | autovacuum launcher
| | 10584 | Activity | LogicalLauncherMain | | logical replication launcher
12969 | postgres | 10591 | Lock | relation | active | client backend
12969 | postgres | 10593 | | | active | client backend
| | 10580 | Activity | BgWriterHibernate | | background writer
| | 10579 | Activity | CheckpointerMain | | checkpointer
| | 10581 | Activity | WalWriterMain | | walwriter
(7 rows)
You will not see any blocking sessions (blocked_by = 0):
select pid
, usename
, pg_blocking_pids(pid) as blocked_by
, query as blocked_query
from pg_stat_activity
where cardinality(pg_blocking_pids(pid)) > 0;
pid | usename | blocked_by | blocked_query
-------+----------+------------+-----------------------------------
10591 | postgres | {0} | ALTER TABLE t1 ADD COLUMN b text;
Even if you restart the instance the issue will persist. The only solution to that is, to either commit or rollback the prepared transactions;
select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+-----+-------------------------------+----------+----------
564 | abc | 2021-02-26 11:28:37.362649+01 | postgres | postgres
(1 row)
rollback prepared 'abc';
As soon this completed the other session will be able to complete it’s work:
ALTER TABLE t1 ADD COLUMN b text;
Remember: When things look really weird, it might be, because you have ongoing prepared transactions.