Hazard's stuff

Fixing Greenplum 'unresolved in-doubt transaction' errors

— Posted by hazard @ 2010-06-29 05:00
We had an issue with a database server running Greenplum (commercial Postgresql for large-scale datawarehousing). Greenplum was starting, but attempts to do anything in the database were resulting in the following errors:

INFO: Crash recovery broadcast of the distributed transaction 'Commit Prepared' broadcast succeeded for gid = 1265880453-0032866370.
INFO: Crash recovery broadcast of the distributed transaction 'Abort Prepared' broadcast succeeded for gid = 1265880453-0032866371��C
psql: FATAL: DTM Log recovery failed. There are still unresolved in-doubt transactions on some of the segment databaes that were not able to be resolved for an unknown reason. (cdbtm.c:2829)
DETAIL: Here is a list of in-doubt transactions in the system: List of In-doubt transactions remaining across the segdbs: ("1265880453-0032866371��C" , )
HINT: Try restarting the Greenplum Database array. If the problem persists an Administrator will need to resolve these transactions manually.

Of course, manuals/forums/Google did not provide any useful ideas as regards to how 'to resolve these transactions manually'. Morever there was no backup handy (and the db was huge). I didn't care about two lost transactions, I just wanted to start the database. After an hour of attempts, eventually I succeeded. The trick was to delete files from pg_twophase/ subdirectories.

I'm blogging this in the hope that when somebody else faces this problem, he would be able to find this post through Google, saving his nerves. :-)

Guaba Beach Bar Limassol

— Posted by hazard @ 2010-06-23 14:33
Great music but a boo for customer service. I don't like being told "I don't give a shit" by supervisors. Having spent there a few hundred euros in a week, the least I want is to be handled politely. Fix yourself or you'll end up only with drunken kids. The atmosphere has been degrading ever since the move to a new place.