[Postgres-xc-bugs] Repairing Corrupt Objects

[West, W. <ww...@uc...>]

I think I may have found the source of my problem but not the solution. There seems to be a prepared transaction running for days in my XC instance. I tried to roll it back but got the following:

bigonc=# select * from pg_prepared_xacts;
 transaction |  gid   |           prepared            |   owner    | database
-------------+--------+-------------------------------+------------+----------
       11248 | T11248 | 2013-10-25 14:35:14.467445-07 | bigonc_prd | bigonc
(1 row)

bigonc=# rollback prepared '11248';
ERROR:  prepared transaction with identifier "11248" does not exist
STATEMENT:  rollback prepared '11248';
ERROR:  prepared transaction with identifier "11248" does not exist

It cannot find the transaction id. I am assuming it is the first field in the table but since it doesn’t have the work ID in it, I could be wrong. Stopping and starting the server has no impact on this transaction. What other methods might be available to me short of reinitializing the database to clear this impediment.

Thanks,

Bill West

From: <West>, William West <ww...@uc...<mailto:ww...@uc...>>
Date: Friday, November 1, 2013 at 10:56 AM
To: "pos...@li...<mailto:pos...@li...>" <pos...@li...<mailto:pos...@li...>>
Subject: Repairing Corrupt Objects

All,

I have a database set up but it seems fairly unstable. I have a number of objects in the running database but I have one object that is corrupt. I know this because doing any query or dml statement on this table stalls the client. I also notice that when I run statements that encounter the invalid object. It spawns 2 process ID like below:

505149   19373  0.0  0.0 191768  5908 ?        Ss   10:30   0:00 postgres:  postgresql bigonc [local] VACUUM
505149   19379  1.2  0.0 191972 24776 ?        Ss   10:30   0:00 postgres: postgresql bigonc 127.0.0.1(46305) VACUUM waiting

These processes never complete and seem like they are locking one another. However there is nothing showing in pg_locks. Does anyone recognize this behavior. Is there anyway to repair or drop a corrupt object in the database?

Thanks,
Bill West