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(at)jabber(dot)postgresql(dot)org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997