Menu
▾
▴
postgres-xc-bugs
|
From: West, W. <ww...@uc...> - 2013-11-01 17:41:16
|
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 on 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 |
|
From: West, W. <ww...@uc...> - 2013-11-01 17:56:31
|
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 |
|
From: West, W. <ww...@uc...> - 2013-11-01 23:28:49
|
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
|
|
From: Koichi S. <koi...@gm...> - 2013-11-05 01:13:35
|
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 > > |
|
From: West, W. <ww...@uc...> - 2013-11-01 22:38:26
|
All, Sorry for the multiple posting but the first emails seemed to get garbled. Hopefully this will post with the full text. Please ignore the other posts with this topic. 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 |
|
From: 鈴木 幸市 <ko...@in...> - 2013-11-05 01:36:36
|
Is this related to the outstanding 2PC three days ago, which I responded with a solution? If not, could you identify corrupt database object? Regards; --- Koichi Suzuki On 2013/11/02, at 7:34, "West, William" <ww...@uc...<mailto:ww...@uc...>> wrote: All, Sorry for the multiple posting but the first emails seemed to get garbled. Hopefully this will post with the full text. Please ignore the other posts with this topic. 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 ------------------------------------------------------------------------------ November Webinars for C, C++, Fortran Developers Accelerate application performance with scalable programming models. Explore techniques for threading, error checking, porting, and tuning. Get the most from the latest Intel processors and coprocessors. See abstracts and register http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk_______________________________________________ Postgres-xc-developers mailing list Pos...@li... https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers |
|
From: West, W. <ww...@uc...> - 2013-11-05 03:15:40
|
Koichi, Yes it is related. I used your solution and it did rollback the long running prepared statement. I assumed this might have been causing an unregistered block on the table I was attempting to run queries against or vacuum. Unfortunately though this did not appear to alleviate the situation below. I have a single table affected by this. The other object in the database seem to work as expected. If I try to run a query against this table or vacuum it creates 2 pids like so: 505149 19636 0.0 0.0 192248 6824 ? Ss 18:26 0:00 postgres: bigonc_prd bigonc 72.220.234.184(49302) VACUUM 505149 19889 0.0 0.0 191872 4992 ? Ss 19:03 0:00 postgres: bigonc_prd bigonc 127.0.0.1(57250) VACUUM waiting To me this looks like a process is pawned from the client while another process is started on the server. The client process seems to be causing a blocking like against the server pid. This is the output from PG_LOCKS for the client process: “virtualxid" "3/324” "3/324” 19636 “ExclusiveLock” t t “transactionid" “” 63030 "3/324” 19636 “ExclusiveLock” t f Thanks for your help with the other issue as well, Bill West From: 鈴木 幸市 <ko...@in...<mailto:ko...@in...>> Date: Monday, November 4, 2013 at 6:36 PM To: William West <ww...@uc...<mailto:ww...@uc...>> Cc: "pos...@li...<mailto:pos...@li...>" <pos...@li...<mailto:pos...@li...>>, "pos...@li...<mailto:pos...@li...>" <pos...@li...<mailto:pos...@li...>> Subject: Re: [Postgres-xc-developers] Repairing Corrupt Objects Is this related to the outstanding 2PC three days ago, which I responded with a solution? If not, could you identify corrupt database object? Regards; --- Koichi Suzuki On 2013/11/02, at 7:34, "West, William" <ww...@uc...<mailto:ww...@uc...>> wrote: All, Sorry for the multiple posting but the first emails seemed to get garbled. Hopefully this will post with the full text. Please ignore the other posts with this topic. 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 ------------------------------------------------------------------------------ November Webinars for C, C++, Fortran Developers Accelerate application performance with scalable programming models. Explore techniques for threading, error checking, porting, and tuning. Get the most from the latest Intel processors and coprocessors. See abstracts and register http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk_______________________________________________ Postgres-xc-developers mailing list Pos...@li...<mailto:Pos...@li...> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers |
|
From: 鈴木 幸市 <ko...@in...> - 2013-11-05 07:17:48
|
There could be several different cause of the problem. First of all, to ensure that there's no more outstanding 2PCs, could you visit all the nodes (coordinators and datanodes) directly with psql and see if there's no running 2PCs in any of the nodes? If you find any, you should rollback all of them using the same technique. If not, could you restart the whole cluster? I advise to use -m immediate or -m fast option to stop them with pg_ctl. Then restart the cluster and see what's going on. I believe only 2PC-repated resources are carried over when nodes are restarted and all the others are cleared up. I do hope you a allowed to do so. Regards; --- Koichi Suzuki On 2013/11/05, at 12:15, "West, William" <ww...@uc...<mailto:ww...@uc...>> wrote: Koichi, Yes it is related. I used your solution and it did rollback the long running prepared statement. I assumed this might have been causing an unregistered block on the table I was attempting to run queries against or vacuum. Unfortunately though this did not appear to alleviate the situation below. I have a single table affected by this. The other object in the database seem to work as expected. If I try to run a query against this table or vacuum it creates 2 pids like so: 505149 19636 0.0 0.0 192248 6824 ? Ss 18:26 0:00 postgres: bigonc_prd bigonc 72.220.234.184(49302) VACUUM 505149 19889 0.0 0.0 191872 4992 ? Ss 19:03 0:00 postgres: bigonc_prd bigonc 127.0.0.1(57250) VACUUM waiting To me this looks like a process is pawned from the client while another process is started on the server. The client process seems to be causing a blocking like against the server pid. This is the output from PG_LOCKS for the client process: “virtualxid" "3/324” "3/324” 19636 “ExclusiveLock” t t “transactionid" “” 63030 "3/324” 19636 “ExclusiveLock” t f Thanks for your help with the other issue as well, Bill West From: 鈴木 幸市 <ko...@in...<mailto:ko...@in...>> Date: Monday, November 4, 2013 at 6:36 PM To: William West <ww...@uc...<mailto:ww...@uc...>> Cc: "pos...@li...<mailto:pos...@li...>" <pos...@li...<mailto:pos...@li...>>, "pos...@li...<mailto:pos...@li...>" <pos...@li...<mailto:pos...@li...>> Subject: Re: [Postgres-xc-developers] Repairing Corrupt Objects Is this related to the outstanding 2PC three days ago, which I responded with a solution? If not, could you identify corrupt database object? Regards; --- Koichi Suzuki On 2013/11/02, at 7:34, "West, William" <ww...@uc...<mailto:ww...@uc...>> wrote: All, Sorry for the multiple posting but the first emails seemed to get garbled. Hopefully this will post with the full text. Please ignore the other posts with this topic. 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 ------------------------------------------------------------------------------ November Webinars for C, C++, Fortran Developers Accelerate application performance with scalable programming models. Explore techniques for threading, error checking, porting, and tuning. Get the most from the latest Intel processors and coprocessors. See abstracts and register http://pubads.g.doubleclick.net/gampad/clk?id=60136231&iu=/4140/ostg.clktrk_______________________________________________ Postgres-xc-developers mailing list Pos...@li...<mailto:Pos...@li...> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers |
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X