Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.
Much better than my suggestion. Including both the problem
conditions and the solution is ideal.
I'd missed that we now allow FOR UPDATE and FOR SHARE on subqueries.
Nice enhancement.
-Kevin