Re: determine snapshot after obtaining locks for first statement

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

regards, tom lane