May 30, 2024
Summary: In this tutorial, you will learn how to check abandoned prepared transactions in PostgreSQL.
Table of Contents
Abandoned prepared transactions and VACUUM
During two-phase commit, a distributed transaction is first prepared with the PREPARE statement and then committed with the COMMIT PREPARED statement.
Once PostgreSQL prepares a transaction, the transaction is kept “hanging around” until the PostgreSQL commits it or aborts it. It even has to survive a server restart! Normally, transactions don’t remain in the prepared state for long, but sometimes things go wrong and the administrator has to remove a prepared transaction manually.
VACUUM is potentially blocked by the xmin horizon, as we discussed in Check replication slots blocking VACUUM tutorial.
A transaction prepared for a two-phase commit will prevent VACUUM cleanup until it is either committed or rolled back.
List abandoned prepared transactions
You can find all prepared transactions and their xmin value with the following query:
SELECT gid, prepared, owner, database, transaction AS xmin
FROM pg_prepared_xacts
ORDER BY age(transaction) DESC;
Once identified, you can either use the ROLLBACK PREPARED SQL statement to remove the prepared transactions, or use the COMMIT PREPARED SQL statement to commit the prepared transactions, just as following:
COMMIT PREPARED <gid_from_above>;
ROLLBACK PREPARED <gid_from_above>;