Skip site navigation (1) Skip section navigation (2)

Re: Prepared transactions vs novice DBAs, again

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jeff Davis <pgsql(at)j-davis(dot)com>
Cc: jd(at)commandprompt(dot)com, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Prepared transactions vs novice DBAs, again
Date: 2009-04-22 18:53:09
Message-ID: 8785.1240426389@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> On Wed, 2009-04-22 at 11:00 -0700, Joshua D. Drake wrote:
>> Then perhaps a setting like max_stale_prepared_transaction_age and once
>> that threshold is met it will autorollback?

> I think that defeats the safety of prepared transactions in many cases.
> Let's say you PREPARE TRANSACTION on two systems, and then COMMIT
> PREPARED on the first one. Then, you go to COMMIT PREPARED on the second
> one, and the time has lapsed so you can't (and you can't rollback the
> first one, either).

Yeah, any sort of auto rollback on prepared xacts is scary.

You could probably argue that an autorollback threshold up around a
billion transactions is safe enough.  However, the bad side-effects
of a forgotten prepared transaction would start to happen long before
that, in the form of bloated tables.  (Or am I wrong about that?
Does a prepared xact still block vacuum cleanup in HEAD, or has that
been fixed since 8.2?)  I think DBAs would be tempted to set the
threshold a lot lower, and then sooner or later they'd lose data.

			regards, tom lane

In response to

Responses

pgsql-hackers by date

Next:From: Jeff DavisDate: 2009-04-22 18:55:27
Subject: Re: Prepared transactions vs novice DBAs, again
Previous:From: Tom LaneDate: 2009-04-22 18:48:58
Subject: Re: Prepared transactions vs novice DBAs, again

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group