Robert Haas <robertmh...@gmail.com> writes: > On Sun, Oct 11, 2009 at 12:35 PM, Tom Lane <t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers