Обсуждение: 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.adam@petafuel.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

ludwig.adam@petafuel.de writes:
> 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 cannot find any occurrence of such an error message in the Postgres
source code (either HEAD or 9.1.x).  So I think this message must have
come from some other code --- perhaps whatever you're using for a 2PC
controller?  If that controller saw the old master and new master as
two different systems, it's not too surprising that it got confused.

            regards, tom lane