Robert Haas <robertmhaas@gmail.com> writes:
> On Sun, Oct 11, 2009 at 12:35 PM, Tom Lane <tgl@sss.pgh.pa.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