Re: Prepared transactions vs novice DBAs, again

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.

Sincerely,

Joshua D. Drake

-- 
PostgreSQL - XMPP: jdrake@jabber.postgresql.org  Consulting, Development, Support, Training  503-667-4564 -
http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997