Re: Is FOR UPDATE an optimization fence?

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Sun, Oct 11, 2009 at 12:35 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> It's an entirely trivial code change either way. I'm inclined to think
>> that we should prevent flattening, on the grounds of least astonishment.

> It seems like this is somewhat related to the question of embedding an
> {INSERT|UPDATE|DELETE}...RETURNING in some arbitrary part of a query
> versus only allowing it in a WITH clause. The argument for only
> allowing it in a WITH clause is that there is otherwise no guarantee
> that it is evaluated in its entirety but just once. ISTM we could
> contrariwise give it the handling you're proposing here: allow it
> anywhere in the query, but make it act as an optimization fence.

I don't think this analogy to update queries is a very solid one.
SELECT FOR UPDATE has a couple of properties that make it much less
critical for it to have evaluate-exactly-once semantics:

* Locking the same row twice is a no-op.

* If you don't run the query to completion and thus don't lock all
the rows, so what? Arguably, the semantics of the query are to
lock only the rows actually read/returned.

> For that reason, I think I'd be inclined to make it act as an
> optimization fence if used as a top-level CTE, but otherwise flatten
> it, so that the handling is consistent with what we've proposed to do
> elsewhere.

Well, the point of the WITH RETURNING restriction is that just because a
subselect isn't flattened doesn't mean you have evaluate-exactly-once
semantics for the subselect. The upper query is still free to execute
the subselect multiple times, partially, or not at all. A CTE, on the
other hand, is more than just an optimization fence --- we've decided to
give it evaluate-exactly-once semantics. So SELECT FOR UPDATE at the
top of a CTE is really irrelevant to this discussion; it's going to be
treated the same no matter what.

regards, tom lane