Regular Maintenance - PostgreSQL 9 Administration - page 220

How to do it...
Removing a prepared transaction is also referred to as "resolving in-doubt transactions". The
transaction is literally stuck between committing and aborting. The database or transaction
manager crashed, leaving the transaction mid-way through the two-phase commit process.
If you have a connection pool of 100 active connections and something crashes, you'll
probably find 1 to 20 transactions stuck in the prepared state, depending upon how long your
average transaction is.
To resolve the transaction, we need to decide whether we want that change, or not. The best
way is to check what happened externally to PostgreSQL. That should help you decide.
If you do need further help, look at the There's more section.
If you wish to commit the changes, then:
COMMIT PREPARED 'prep1';
or if you want to rollback the changes then:
ROLLBACK PREPARED 'prep1';
How it works...
Prepared transactions are persistent across crashes, so you can't just do a fast restart to
get rid of them. They have both an internal transaction identifier and an external "global
identiier". Either of those can be used to locate locked resources, and decide how to resolve
the transactions.
There's more...
If you're not sure what the prepared transaction actually did, you can go and look, though it is
time consuming. The pg_locks view shows locks are held by prepared transactions. You can
get a full report of what is being locked using the following query:
postgres=# SELECT l.locktype, x.database, l.relation, l.page,
l.tuple,l.classid, l.objid, l.objsubid,
l.mode, x.transaction, x.gid, x.prepared,
x.owner
FROM pg_locks l JOIN pg_prepared_xacts x
ON l.virtualtransaction = '-1/' ||
x.transaction::text;