Prepared transactions vs novice DBAs, again
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
Tom Lane wrote:
Anyway, maybe question zero is whether anyone else thinks this is
important enough to justify extra work in the area.
One thing that has already changed is that DROP DATABASE reports "N
users and M prepared transactions", so there is more of a hint.
Another thing we could do is make autovacuum log something about those,
similar to what it does to temp tables. And if one of them gets too
near Xid wraparound, kill it.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
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
Joshua D. Drake wrote:
On Wed, 2009-04-22 at 13:48 -0400, Tom Lane wrote:
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.
I think we should change the way we test it. Could we simply make
max_prepared_transactions = 0 the default, but put
"max_prepared_transactions = 5" into the config file in "make check"?
That seems like the best alternative, it doesn't seem right to build
extra config options or other infrastructure into the server just so
that the regression tests can test a feature.
Perhaps we should also make the manual more clear on the fact that
PREPARE TRANSACTION isn't supposed to be used by casual users, but only
by an external transaction monitor implementation. It wouldn't have
helped at all in the recent case on pgsql-admin, though, as the format
of the global transaction suggests that it was issued through the JDBC
driver. Very likely none of the developer were aware either that there's
2PC in action. Nothing short of disabling PREPARE TRANSACTION in default
configuration will help with that.
Printing a warning similar to the "database \"%s\" must be vacuumed
within %u transactions" would be a good idea as well. That doesn't need
to be limited to prepared transactions; a warning whenever there's an
excessively old transaction active would be nice. People sometimes leave
behind a psql session with a transaction open when they go for vacation etc.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
"Joshua D. Drake" <jd@commandprompt.com> writes:
On Wed, 2009-04-22 at 13:48 -0400, Tom Lane wrote:
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.
Well, I agree that losing regression testing of the feature would be a
Bad Thing. But we already require superuser privs to run the regression
tests. I'm thinking if we were to change things so that the regression
tests could temporarily turn on the ability to issue PREPARE
TRANSACTION, it would be possible to keep the testing but still have
the out-of-the-box configuration disable PREPARE TRANSACTION.
Anyway, as I said originally, this is all just brainstorming at this
point.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160
Anyway, maybe question zero is whether anyone else thinks this is
important enough to justify extra work in the area.
Yes. For every user that complains on the list, there are a dozen other
quiet ones who have been bit by the same.
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.
I highly support setting it to zero by default. If our testing process
cannot handle changing things on the fly, then that process should be fixed.
Therefore, the default out-of-the-box configuration
of Postgres shouldn't allow PREPARE TRANSACTION at all.
Seems overkill, IMHO.
Do we want to treat old prepared xacts as being as dangerous
as an impending wraparound?
Yes.
- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation
PGP Key: 0x14964AC8 200904221428
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iEYEAREDAAYFAknvYdIACgkQvJuQZxSWSsgEagCffiTkxT3iRB2IDpADIu0eZspG
Pj8AniqBsi0sYuJvxzPWXIgKNk1QApEQ
=oBJJ
-----END PGP SIGNATURE-----
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
I think we should change the way we test it. Could we simply make
max_prepared_transactions = 0 the default, but put
"max_prepared_transactions = 5" into the config file in "make check"?
That only works for make check, not make installcheck. We'd really have
to complicate the GUC setup somehow to have this work, eg add a separate
enable_prepared_transactions bool that can be changed without restart.
regards, tom lane
Heikki Linnakangas wrote:
I think we should change the way we test it. Could we simply make
max_prepared_transactions = 0 the default, but put
"max_prepared_transactions = 5" into the config file in "make check"?
That seems like the best alternative, it doesn't seem right to build
extra config options or other infrastructure into the server just so
that the regression tests can test a feature.
FWIW, modern versions of the buildfarm script have support for
non-standard options in the install-check pieces.
cheers
andrew
Tom Lane wrote:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
I think we should change the way we test it. Could we simply make
max_prepared_transactions = 0 the default, but put
"max_prepared_transactions = 5" into the config file in "make check"?That only works for make check, not make installcheck.
Configuration affects what can be tested in installcheck, that's quite
natural. I would be happy with simply adding an alternative expected
output file for min_prepared_xacts=0 case. Like we've done for xml test
cases, for example, though that's a compile-time option.
Or we could print a notice if you run make installcheck against a server
with max_prepared_transactions=0.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Wed, 2009-04-22 at 11:00 -0700, Joshua D. Drake wrote:
Then perhaps a setting like max_stale_prepared_transaction_age and once
that threshold is met it will autorollback?
I think that defeats the safety of prepared transactions in many cases.
Let's say you PREPARE TRANSACTION on two systems, and then COMMIT
PREPARED on the first one. Then, you go to COMMIT PREPARED on the second
one, and the time has lapsed so you can't (and you can't rollback the
first one, either).
Regards,
Jeff Davis
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
Tom Lane wrote:
That only works for make check, not make installcheck.
Configuration affects what can be tested in installcheck, that's quite
natural. I would be happy with simply adding an alternative expected
output file for min_prepared_xacts=0 case. Like we've done for xml test
cases, for example, though that's a compile-time option.
Hmm, that's true; the xml case is a relevant precedent. This would be
a pretty low-effort way of addressing the problem. Another nice thing
about it is that we'd stop having a default max_prepared_transactions
value that's completely useless (5 is guaranteed to be either too much
or not enough...)
regards, tom lane
Jeff Davis <pgsql@j-davis.com> writes:
On Wed, 2009-04-22 at 11:00 -0700, Joshua D. Drake wrote:
Then perhaps a setting like max_stale_prepared_transaction_age and once
that threshold is met it will autorollback?
I think that defeats the safety of prepared transactions in many cases.
Let's say you PREPARE TRANSACTION on two systems, and then COMMIT
PREPARED on the first one. Then, you go to COMMIT PREPARED on the second
one, and the time has lapsed so you can't (and you can't rollback the
first one, either).
Yeah, any sort of auto rollback on prepared xacts is scary.
You could probably argue that an autorollback threshold up around a
billion transactions is safe enough. However, the bad side-effects
of a forgotten prepared transaction would start to happen long before
that, in the form of bloated tables. (Or am I wrong about that?
Does a prepared xact still block vacuum cleanup in HEAD, or has that
been fixed since 8.2?) I think DBAs would be tempted to set the
threshold a lot lower, and then sooner or later they'd lose data.
regards, tom lane
On Wed, 2009-04-22 at 13:53 -0400, Alvaro Herrera wrote:
Another thing we could do is make autovacuum log something about those,
similar to what it does to temp tables. And if one of them gets too
near Xid wraparound, kill it.
As I said in my reply to Joshua, I don't think killing a prepared
transaction is consistent with the safety people expect from 2PC.
However, if it's near wraparound time, that could be considered an
exceptional case I suppose, and if we don't have a better way to avoid
getting the system in a very bad state, it might be acceptable.
I like the idea of logging some kind of warning a long time before it
becomes a real problem. Should the staleness of a prepared transaction
be measured in time or xid age or both? Maybe have a reasonable default
of a few minutes or a couple thousand transactions before it starts
issuing warnings?
Regards,
Jeff Davis
Tom Lane wrote:
Does a prepared xact still block vacuum cleanup in HEAD, or has that
been fixed since 8.2?
It still does. A prepared xact is just like a idle-in-transaction
backend as far as vacuum is concerned.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Wed, 2009-04-22 at 21:58 +0300, Heikki Linnakangas wrote:
Tom Lane wrote:
Does a prepared xact still block vacuum cleanup in HEAD, or has that
been fixed since 8.2?It still does. A prepared xact is just like a idle-in-transaction
backend as far as vacuum is concerned.
I thought idle transactions generally have released all their snapshots
(where possible), thus allowing VACUUM to work. I would think something
similar could work for prepared transactions.
Regards,
Jeff Davis
Jeff Davis wrote:
On Wed, 2009-04-22 at 21:58 +0300, Heikki Linnakangas wrote:
Tom Lane wrote:
Does a prepared xact still block vacuum cleanup in HEAD, or has that
been fixed since 8.2?It still does. A prepared xact is just like a idle-in-transaction
backend as far as vacuum is concerned.I thought idle transactions generally have released all their snapshots
(where possible), thus allowing VACUUM to work. I would think something
similar could work for prepared transactions.
If the prepared transaction has modified any rows (as it typically has,
or you wouldn't bother with 2PC to begin with), its XID is on disk. We
can't advance OldestXmin beyond that.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
I wrote:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
Configuration affects what can be tested in installcheck, that's quite
natural. I would be happy with simply adding an alternative expected
output file for min_prepared_xacts=0 case. Like we've done for xml test
cases, for example, though that's a compile-time option.
Hmm, that's true; the xml case is a relevant precedent. This would be
a pretty low-effort way of addressing the problem. Another nice thing
about it is that we'd stop having a default max_prepared_transactions
value that's completely useless (5 is guaranteed to be either too much
or not enough...)
The more I think about this the more I like it. The current default of
5 never had any justification beyond allowing the regression tests to
run --- it's almost certainly not enough for production usage of the
feature, but it exposes you to all of the downsides of accidental use.
If we change it to zero, we could alter the Notes for PREPARE
TRANSACTION to urge more strongly that the feature not be enabled
without having set up appropriate external infrastructure.
Warning about very old prepared transactions is something that we
could think about doing as well; it doesn't have to be either-or.
I think the need for it would decrease quite a bit if they weren't
enabled by default, though.
Comments? Anyone seriously opposed to making the default be zero?
regards, tom lane
On Wed, 2009-04-22 at 15:49 -0400, Tom Lane wrote:
I wrote:
Warning about very old prepared transactions is something that we
could think about doing as well; it doesn't have to be either-or.
I think the need for it would decrease quite a bit if they weren't
enabled by default, though.Comments? Anyone seriously opposed to making the default be zero?
I am not opposed to making the default zero. I am also +1 on adding the
warnings.
Joshua D. Drake
regards, tom lane
--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997
"Joshua D. Drake" <jd@commandprompt.com> writes:
On Wed, 2009-04-22 at 15:49 -0400, Tom Lane wrote:
Comments? Anyone seriously opposed to making the default be zero?
I am not opposed to making the default zero. I am also +1 on adding the
warnings.
What I think we could/should do about that for 8.4 is to improve the
HINTs associated with the impending-wraparound warnings in varsup.c,
so that they mention cleaning out old prepared transactions as another
thing you might need to do. A more extensive solution --- in
particular, adding warnings that occur at some lower threshold than
the wraparound warnings --- seems to me to require more changes than
we should consider post-beta. It might be worth working on for 8.5
though.
(In case it's not clear, I'm envisioning changing the
max_prepared_transactions default for 8.4. It won't be a big change
if we do it as Heikki suggests.)
regards, tom lane
On Wed, Apr 22, 2009 at 2:58 PM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
Tom Lane wrote:
Does a prepared xact still block vacuum cleanup in HEAD, or has that
been fixed since 8.2?It still does. A prepared xact is just like a idle-in-transaction backend as
far as vacuum is concerned.
Is that really necessary? It's true that you can't vacuum away any
rows whose xmin is that of the prepared xact, but it seems like you
wouldn't need to keep rows just because they were *visible* to the
prepared xact. Once prepared, it's no longer capable of reading them.
...Robert
