Prepared transactions vs novice DBAs, again

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Prepared transactions vs novice DBAs, again
Date: 2009-04-22 17:48:31
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

The pgsql-admin list has just seen another instance where careless use
of prepared transactions brought down a database, and the DBA (who had
no idea what a prepared transaction even was) had no idea how to fix it.
It seems to me we need to do something about making that stuff less
DBA-unfriendly. I don't have a concrete proposal about exactly what,
but I think there are a couple of lines of thought we could pursue.

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? 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.

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. 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. This could be got around if we allowed
max_prepared_transactions to be changed in some superuser-only fashion,
but right now it is a shared memory sizing parameter which defeats that.
Perhaps we could split it into a sizing parameter and some
easier-to-change enablement parameter? Or perhaps think of the ability
to issue PREPARE TRANSACTION as a grantable privilege that
non-superusers should not have by default? (But of course that won't
help DBAs who are not bright enough to run their applications as non
superusers. A GUC that even a superuser has to take explicit action
to change would likely be safer.)

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

regards, tom lane


Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2009-04-22 17:53:11 Re: Prepared transactions vs novice DBAs, again
Previous Message Mikko 2009-04-22 17:06:24 Re: trouble with to_char('L')