BUG #12035: Prepared Transactions cannot be ROLLBACKed

The following bug has been logged on the website:

Bug reference: 12035
Logged by: Ludwig Adam
Email address: ludwig(dot)adam(at)petafuel(dot)de
PostgreSQL version: 9.1.14
Operating system: on x86_64-unknown-linux-gnu, com (Ubuntu 12.04 LTS
Description:

Hi,

Matheus0l on #postgresql asked me to file a bug on this: Our setup is two
postgres servers in streaming replication controlled by pgpool II. After a
failover (i.e. slave got promoted) we had around 16 Prepared Transactions in
pg_prepared_xacts for the new master, locking some tables.

However we were unable to Perform "ROLLBACK prepared 'gid'"- it gave us the
error message:"
ERROR: cannot rollback prepared transaction 'gid'- transaction is busy."

I can confirm that there were no backend processes trying to ROLLBACK or
COMMIT at the same time- issue persisted with all backend connections
disconnected.

We then did a base-backup and restarted replication to the former master.
During replication, the prepared transactions were not present, however,
once we promoted that server to master, the transactions were there again
and could not be ROLLBACKed either.

After discussions on #postgresql we finally resolved this by setting up
another base backup, deleting the corresponding state files in pg_twophase
and promoting to master. The new master did not have these transactions
listed any longer.

Please feel free to get in touch with me, should you have any questions for
specifics.

Ludwig