Re: [Postgres-xc-bugs] Repairing Corrupt Objects

[Koichi S. <koi...@gm...>]

Thanks for this detailed info.

Could you try to run pgxc_clean to clean-up such locally-existing 2PC?   If
you'd like to abort such outstanding 2PCs, you can do the following:

1. Connect to the node where you outstanding 2PC exists as a super-user.
2. Set 'xc_maintenance_mode' to "on".   This enables local maintenance
work.   Please note that you cannot set this to ON in your postgresql.conf
file.   You can set this to ON only within a session.
3. Issue ROLLBACK PREPARED command.

I hope this works.   This is basically what pgxc_clean is doing but you can
do it manually too.

Pgxc_clean will be found in contrib directory.

Good luck;

---
Koichi Suzuki


2013/11/2 West, William <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...>
> Date: Friday, November 1, 2013 at 10:56 AM
> To: "pos...@li..." <
> 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
>
>
> ------------------------------------------------------------------------------
> Android is increasing in popularity, but the open development platform that
> developers love is also attractive to malware creators. Download this white
> paper to learn more about secure code signing practices that can help keep
> Android apps secure.
> http://pubads.g.doubleclick.net/gampad/clk?id=65839951&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xc-bugs mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-bugs
>
>