postgresql prepared transaction - ghdrako/doc_snipets GitHub Wiki

Transactions are attached to a session.

A prepared transaction is a session independent, crash resistant, state maintained transaction. The state of the transaction is stored on disk which allows the database server to reinstate the transaction even after restarting from a crash. A prepared transaction is maintained until a rollback or a commit action is performed on it.

Create

created prepared transaction within existing transaction block.

PREPARE TRANSACTION ‘transaction_id’

this process prepares a transaction for a two-phase commit.

Creating a prepared transaction is a four step process:

• BEGIN (or START TRANSACTION)
• Perform required operations
• PREPARE TRANSACTION
• COMMIT or ROLLBACK PREPARED

Example

BEGIN;
CREATE TABLE foo(firstCol INT);
INSERT INTO foo VALUES(27);
PREPARE TRANSACTION 'foo_insert';
SELECT * FROM pg_prepared_xacts;
COMMIT PREPARED 'foo_insert';

When a server is stopped (or it crashes) with one or more active prepared transaction(s), it creates one file each for every active prepared transaction under pg_twophase folder within the data directory

ls -l /tmp/data/pg_twophase/

File name is generate as hex(transaction ID)

When the server was restarted it recover transactiom save in this folder.

If you don’t wish to recover a prepared transaction, you may simply delete the corresponding file under the pg_twophase folder.

Configure

In PostgreSQL, by default, max_prepared_transactions is set to 0 in postgresql.conf file; i.e. it is disabled. If you want to use prepared transactions, it is recommended to set this value to max_connections. On the standby server in a synchronous streaming replication setup however, you’d want to be more generous and set the value a little higher than “max_connections” as otherwise you risk the standby not accepting any queries.

Find

pg_prepared_xacts
Columns:
Transaction; xid (transaction ID)
GID; text (a user defined name for the prepared transaction)
Prepared date; (timestamp with timezone when the prepared transaction was created)
Owner; (user who created the prepared transaction)
Database; (name of the database)

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>;

Cavits

The real issue is that an orphaned prepared transaction continues to hold key system resources which may include locks, or keeping alive a transaction ID that may hold back vacuum from cleaning up dead tuples that are no longer visible to any other transaction except for this orphaned prepared transaction.