Re: 0-byte 2PC File

On Sun, Jul 14, 2013 at 9:19 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Vincent Lau <vincent(dot)lau(at)rentrakmail(dot)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(dot)lau(at)rentrak(dot)com <vincent(dot)lau(at)rentrakmail(dot)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.