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

"Joshua D. Drake" <jd(at)commandprompt(dot)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(dot)linnakangas(at)enterprisedb(dot)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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Apr 22, 2009 at 2:58 PM, Heikki Linnakangas
> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> 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.

I think we've already milked what we can from that, since a prepared
xact is treated exactly like an open one with no snapshot. The point
is that whatever rows it's written are still in-doubt and cannot be
frozen, so the wraparound horizon cannot advance past its XID.

regards, tom lane

On Wed, Apr 22, 2009 at 5:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Wed, Apr 22, 2009 at 2:58 PM, Heikki Linnakangas
>> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>>> 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.
>
> I think we've already milked what we can from that, since a prepared
> xact is treated exactly like an open one with no snapshot.  The point
> is that whatever rows it's written are still in-doubt and cannot be
> frozen, so the wraparound horizon cannot advance past its XID.

But surely that's not "the same" as a backend which is
idle-in-transaction? In that case I think you still need a snapshot?

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Apr 22, 2009 at 5:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I think we've already milked what we can from that, since a prepared
>> xact is treated exactly like an open one with no snapshot. The point
>> is that whatever rows it's written are still in-doubt and cannot be
>> frozen, so the wraparound horizon cannot advance past its XID.

> But surely that's not "the same" as a backend which is
> idle-in-transaction? In that case I think you still need a snapshot?

No; at least not unless it's a serializable transaction. That's the
point of the snapshot management work that Alvaro did for 8.4.

regards, tom lane

On Wed, Apr 22, 2009 at 8:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Wed, Apr 22, 2009 at 5:44 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> I think we've already milked what we can from that, since a prepared
>>> xact is treated exactly like an open one with no snapshot.  The point
>>> is that whatever rows it's written are still in-doubt and cannot be
>>> frozen, so the wraparound horizon cannot advance past its XID.
>
>> But surely that's not "the same" as a backend which is
>> idle-in-transaction?  In that case I think you still need a snapshot?
>
> No; at least not unless it's a serializable transaction.  That's the
> point of the snapshot management work that Alvaro did for 8.4.

Maybe I'm just dumb, but I don't get it. If I start a transaction and
do "SELECT * FROM foo" and then wait around for an hour or two while
someone else makes changes to foo and then do "SELECT * FROM foo"
again, I expect to see the same rows I saw the first time, which means
they still need to be around.

But if I now do "PREPARE TRANSACTION", it's no longer necessary for
those rows to be retained, because it's not possible for me to look at
them again.

Is this the commit you're talking about?

http://archives.postgresql.org/pgsql-committers/2008-05/msg00220.php

...Robert

On Wed, Apr 22, 2009 at 9:21 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Maybe I'm just dumb, but I don't get it.  If I start a transaction and
> do "SELECT * FROM foo" and then wait around for an hour or two while
> someone else makes changes to foo and then do "SELECT * FROM foo"
> again, I expect to see the same rows I saw the first time, which means
> they still need to be around.

Yeah, I'm dumb. It doesn't work this way. I guess I always assumed
that this was the reason why <idle in transaction> caused database
bloat, but apparently not!

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Maybe I'm just dumb, but I don't get it. If I start a transaction and
> do "SELECT * FROM foo" and then wait around for an hour or two while
> someone else makes changes to foo and then do "SELECT * FROM foo"
> again, I expect to see the same rows I saw the first time, which means
> they still need to be around.

That only happens in a serializable transaction, ie, one that took
*and kept* a snapshot at transaction start. In READ COMMITTED mode
we drop all snaps between statements, so there is nothing stopping
reclaiming of rows that were visible to prior statements.

regards, tom lane

"Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:
> I am not opposed to making the default zero.

+1 making zero the default for 8.4

> I am also +1 on adding the warnings.

+1, but less urgent, lower priority

-Kevin

On Wednesday 22 April 2009 15:49:32 Tom Lane wrote:
> I wrote:
> > Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)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?
>

I see this has already been committed, and I am not seriously opposed to
changing it, but I wanted to chime in on a point no one seemed to raise. I
used to recommend people set this to 0 pretty regularly, since most web shops
don't even know what prepared transactions are, let alone use them. I got
less agressive about this after a few people reported to me that they had run
out of lock slots on thier systems. Now, you'd think that ~300 lock slots
wouldn't make that much difference, but it did make me a little nervous; so I
thought I'd mention it.

--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

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

Just as a followup (and I already posted this on the pgsql-admin thread),
the check_postgres script now has a specific check for this very case.
It simply checks the age of entries in pg_prepared_xacts and gives
a warning if the number is at or over the given threshhold (defaults
to 1 second). I'm still a heavy +1 on making the default Postgres
configuration value 0, but hopefully this will help.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200905051128
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkoAW0wACgkQvJuQZxSWSsgGRgCePjErqeAPEv4MLJzgEnh/tXtA
yLEAoPhBNvaWvcmTF9D8faZzI044zpBL
=ouXW
-----END PGP SIGNATURE-----

"Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:
> Just as a followup (and I already posted this on the pgsql-admin thread),
> the check_postgres script now has a specific check for this very case.
> It simply checks the age of entries in pg_prepared_xacts and gives
> a warning if the number is at or over the given threshhold (defaults
> to 1 second). I'm still a heavy +1 on making the default Postgres
> configuration value 0, but hopefully this will help.

Hmm, 1 second seems kinda tight --- it would not surprise me to have
valid situations where it takes over a second for an XA manager to
collect all the responses and decide to commit. If you set it at a
minute or an hour you'd have very much less chance of false positives,
and not really give up much that I can see.

regards, tom lane

--On Dienstag, Mai 05, 2009 15:29:15 +0000 Greg Sabino Mullane
<greg(at)turnstep(dot)com> wrote:

> It simply checks the age of entries in pg_prepared_xacts and gives
> a warning if the number is at or over the given threshhold (defaults
> to 1 second). I'm still a heavy +1 on making the default Postgres
> configuration value 0, but hopefully this will help.

1 seconds seems a very low default for me. I can imagine that most
distributed transactions are taking longer than this to complete.

--
Thanks

Bernd

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> 1 seconds seems a very low default for me. I can imagine that most
> distributed transactions are taking longer than this to complete.

One second means it is set by default to catch *all* prepared
transactions. It's simply checking how long the transaction has
been open via:

SELECT database, ROUND(EXTRACT(epoch FROM now()-prepared)), prepared
FROM pg_prepared_xacts ORDER BY prepared ASC;

If you *are* using prepared transactions (which most people are not),
you would want to set a specific number for your environment -
and certainly more than 1 second (perhaps 5 minutes?) At that point,
the check changes from "has anybody mistakenly created a prepared
transaction" to "has one of our prepared transactions been open
too long?"

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200905051154
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkoAYaEACgkQvJuQZxSWSsgYqACgvQOPJKMpDAIdSuGIGjvqrkxO
XA8AoKraljUOgV7JrFlv2dJR/T/IJ1iv
=QMWI
-----END PGP SIGNATURE-----