Re: [HACKERS] Prepared transactions vs novice DBAs, again
-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 g...@turnstep.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- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
Greg Sabino Mullane g...@turnstep.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
--On Dienstag, Mai 05, 2009 15:29:15 + Greg Sabino Mullane g...@turnstep.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
-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 g...@turnstep.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- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
On Wednesday 22 April 2009 15:49:32 Tom Lane wrote: I wrote: Heikki Linnakangas heikki.linnakan...@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? 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
Joshua D. Drake j...@commandprompt.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
Joshua D. Drake j...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
-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 g...@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- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
Heikki Linnakangas heikki.linnakan...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
Heikki Linnakangas heikki.linnakan...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
Jeff Davis pg...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
I wrote: Heikki Linnakangas heikki.linnakan...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
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: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
Joshua D. Drake j...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
On Wed, Apr 22, 2009 at 2:58 PM, Heikki Linnakangas heikki.linnakan...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
Robert Haas robertmh...@gmail.com writes: On Wed, Apr 22, 2009 at 2:58 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
On Wed, Apr 22, 2009 at 5:44 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Apr 22, 2009 at 2:58 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
Robert Haas robertmh...@gmail.com writes: On Wed, Apr 22, 2009 at 5:44 PM, Tom Lane t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
On Wed, Apr 22, 2009 at 8:58 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, Apr 22, 2009 at 5:44 PM, Tom Lane t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
On Wed, Apr 22, 2009 at 9:21 PM, Robert Haas robertmh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepared transactions vs novice DBAs, again
Robert Haas robertmh...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
