pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
0-byte 2PC File 3+ messages / 2 participants [nested] [flat]
* 0-byte 2PC File @ 2013-07-13 23:41 Vincent Lau <vincent.lau@rentrakmail.com> 2013-07-14 16:19 ` Re: 0-byte 2PC File Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 3+ messages in thread From: Vincent Lau @ 2013-07-13 23:41 UTC (permalink / raw) To: pgsql-admin Hello! We are running 8.4.13 on this particular database and recently we have been getting empty two phase commit state files inside the pg_twophase directory. These prepared transactions can't be rolled back due to the empty state file. Here is an example of the error message. ERROR: two-phase state file for transaction 121421120 is corrupt The only way to get rid of this is to restart the database, which is just a bandaid solution at this point. Have you guys seen this before? I have googled for this but can't seem to find a single instance of this happening to anyone. I sure will work with our developers to see where in our code is generating this file and go from there. But in the mean time, do you guys have any idea on how I should pursuit this on the database side of things? Here is a little more info about one of the empty file. It does look like the file was created empty. -bash-3.2$ stat 073CBD40 File: `073CBD40' Size: 0 Blocks: 0 IO Block: 4096 regular empty file Device: 830h/2096d Inode: 100663469 Links: 1 Access: (0600/-rw-------) Uid: ( 499/postgres) Gid: ( 402/ dba) Access: 2013-07-12 23:05:48.369557197 -0700 Modify: 2013-07-12 23:05:48.398557197 -0700 Change: 2013-07-12 23:05:48.398557197 -0700 Any help is appreciated and I'd be more than happy to provide more info. Thanks! -- Vincent Lau | Database Administrator 503.284.7581 x394 | vincent.lau@rentrak.com <vincent.lau@rentrakmail.com> RENTRAK | www.rentrak.com | NASDAQ: RENT Notice: This message is confidential and is intended only for the recipient(s) named above. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message. ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 0-byte 2PC File 2013-07-13 23:41 0-byte 2PC File Vincent Lau <vincent.lau@rentrakmail.com> @ 2013-07-14 16:19 ` Tom Lane <tgl@sss.pgh.pa.us> 2013-07-14 21:04 ` Re: 0-byte 2PC File Vincent Lau <vincent.lau@rentrakmail.com> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2013-07-14 16:19 UTC (permalink / raw) To: Vincent Lau <vincent.lau@rentrakmail.com>; +Cc: pgsql-admin Vincent Lau <vincent.lau@rentrakmail.com> writes: > We are running 8.4.13 on this particular database and recently we have been > getting empty two phase commit state files inside the pg_twophase > directory. These prepared transactions can't be rolled back due to the > empty state file. Here is an example of the error message. > ERROR: two-phase state file for transaction 121421120 is corrupt > The only way to get rid of this is to restart the database, which is just a > bandaid solution at this point. Is there a waiting transaction corresponding to that file? If not you could just manually remove the file, I think. Not that that isn't a band-aid, but it'd be less painful than a restart. Do you see any error messages logged at the time the transaction PREPARE is attempted? regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 0-byte 2PC File 2013-07-13 23:41 0-byte 2PC File Vincent Lau <vincent.lau@rentrakmail.com> 2013-07-14 16:19 ` Re: 0-byte 2PC File Tom Lane <tgl@sss.pgh.pa.us> @ 2013-07-14 21:04 ` Vincent Lau <vincent.lau@rentrakmail.com> 0 siblings, 0 replies; 3+ messages in thread From: Vincent Lau @ 2013-07-14 21:04 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin On Sun, Jul 14, 2013 at 9:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Vincent Lau <vincent.lau@rentrakmail.com> writes: > > We are running 8.4.13 on this particular database and recently we have > been > > getting empty two phase commit state files inside the pg_twophase > > directory. These prepared transactions can't be rolled back due to the > > empty state file. Here is an example of the error message. > > > ERROR: two-phase state file for transaction 121421120 is corrupt > > > The only way to get rid of this is to restart the database, which is > just a > > bandaid solution at this point. > > Is there a waiting transaction corresponding to that file? If not you > could just manually remove the file, I think. Not that that isn't a > band-aid, but it'd be less painful than a restart. > By the time the next batch of processing attempted to roll back these 2PCs the original waiting transaction would already have been terminated in a fashion that it could not roll back its own 2PCs. You are right that I can just remove the state file without any negative effect except that it'd error out the next batch of processing when it tries to roll back the previously generated 2PCs. Our developers conceded that there is a historical reason we are using 2PC but that reason is no longer valid. So it's a little bit of a tech debt for us now. > > Do you see any error messages logged at the time the transaction PREPARE > is attempted? > I tried looking around but saw no such error messages. But I suppose I can crank up the log level to see. > > regards, tom lane > Tom, thank you for the pointers. I will keep the community posted when and if we can get to the bottom of this. At this point I do not believe it's hardware issue. It doesn't sound like an intrinsic database issue to me either. Could be data or application. -- Vincent Lau | Database Administrator 503.284.7581 x394 | vincent.lau@rentrak.com <vincent.lau@rentrakmail.com> RENTRAK | www.rentrak.com | NASDAQ: RENT Notice: This message is confidential and is intended only for the recipient(s) named above. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message. ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2013-07-14 21:04 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2013-07-13 23:41 0-byte 2PC File Vincent Lau <vincent.lau@rentrakmail.com> 2013-07-14 16:19 ` Tom Lane <tgl@sss.pgh.pa.us> 2013-07-14 21:04 ` Vincent Lau <vincent.lau@rentrakmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox