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

Re: Prepared transactions vs novice DBAs, again

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Prepared transactions vs novice DBAs, again
Date: 2009-04-22 18:00:01
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On Wed, 2009-04-22 at 13:48 -0400, Tom Lane wrote:

> One line of thought is just to raise the visibility of old prepared
> transactions somehow.  I don't think I want to go as far as, say, making
> every session-start issue WARNINGs about every prepared xact that's more
> than a few minutes old.  But that might be what it takes to get a DBA's
> attention in the worst case.  Do we want to treat old prepared xacts as
> being as dangerous as an impending wraparound? 

What about tracking them via autovacuum rounds. E.g; These prepared
transactions were around last round and are still around this round.

WARNING: You have X prepared transactions that are potentially stale

Then perhaps a setting like max_stale_prepared_transaction_age and once
that threshold is met it will autorollback?

>  Maybe it'd be helpful
> just to fix the impending-wraparound warnings to include mention of old
> prepared xacts if there are any.  But of course, by the time it gets as
> bad as in the recent pgsql-admin case, you've already had enormous
> problems with database bloat.

Yes that would be helpful as well.

> Another line of thought is that prepared xacts are inherently a bad
> thing to be using if you have not done careful setup of a lot of
> external infrastructure (in particular, have a transaction monitor
> running somewhere).  Therefore, the default out-of-the-box configuration
> of Postgres shouldn't allow PREPARE TRANSACTION at all.

Not sure what I think about this.

>   The main
> objection to just setting max_prepared_transactions to zero by default
> is that it would kill our ability to test the feature in the standard
> regression tests. 

That kills it for me. Unless we want to change the way we test.

> Anyway, maybe question zero is whether anyone else thinks this is
> important enough to justify extra work in the area.

I think that anything that points out lack of or inability for
maintenance to do its thing is probably more important than a lot of the
other stuff we spend time on.


Joshua D. Drake

PostgreSQL - XMPP: jdrake(at)jabber(dot)postgresql(dot)org
   Consulting, Development, Support, Training
   503-667-4564 -
   The PostgreSQL Company, serving since 1997

In response to


pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2009-04-22 18:22:57
Subject: Re: Prepared transactions vs novice DBAs, again
Previous:From: Alvaro HerreraDate: 2009-04-22 17:58:48
Subject: Re: trouble with to_char('L')

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