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