On Thu, Dec 17, 2009 at 1:05 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > "Kevin Grittner" <kevin.gritt...@wicourts.gov> writes: >> Tom Lane <t...@sss.pgh.pa.us> wrote: >>> I'm not very sure what a clearer explanation would look like > >> As a stab at it, how about?: > >> This behavior makes Read Committed mode unsuitable for many UPDATE >> or DELETE commands with joins or subqueries > > After thinking a bit, I'd be inclined to add a new paragraph. > In particular, now that FOR UPDATE actually works in subqueries, > it'd be worth pointing out that you can add that to guard against > this type of issue. Perhaps, after the "DELETE FROM website" > example, we could add something like > > UPDATEs and DELETEs involving joins or subqueries are particularly > at risk, since they may perform an update based on a combination of > old rows from other tables with an up-to-date target row. This risk > can be mitigated by adding FOR UPDATE or FOR SHARE to subqueries, so > that all rows directly involved in an update are guaranteed current. > However that will also increase the risk of deadlock failures.
I like that. It might also be worth trying to explain that if you select some data out of the database, do a computation with it, and then use the results to drive an update, you're going to want to make the initial select be FOR SHARE. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers