Marko Tiikkaja <marko.tiikk...@cs.helsinki.fi> writes: > On 2010-11-11 6:41 PM +0200, David Fetter wrote: >> On Thu, Nov 11, 2010 at 04:15:34AM +0200, Marko Tiikkaja wrote: >>> The discussion around wCTE during the last week or so has brought to >>> my attention that we don't actually have a consensus on how exactly >>> wCTEs should behave. The question seems to be whether or not a >>> statement should see the modifications of statements ran before it.
>> +1 for letting writeable CTEs see the results of previous CTEs, just >> as current non-writeable ones do. A lot of the useful cases for this >> feature depend on this visibility. > Just to be clear, the main point is whether they see the data > modifications or not. The simplest case to point out this behaviour is: > WITH t AS (DELETE FROM foo) > SELECT * FROM foo; > And the big question is: what state of "foo" should the SELECT statement > see? You've already predetermined the outcome of the argument by phrasing it that way: if you assume that the CTE runs "before" the main statement then the conclusion is foregone. To my mind, they should be thought of as running in parallel, or at least in an indeterminate order, just exactly the same way that different data modifications made in a single INSERT/UPDATE/DELETE command are considered to be made simultaneously. If someone came to us and complained because his ON UPDATE trigger couldn't reliably see changes made to other rows by the same UPDATE command, and could we please make UPDATE more deterministic, we'd tell him to rethink what he was doing. This is the same thing. It is already the case that a user who pushes on things hard enough can see that a WITH isn't really run "before" the main command. For example, regression=# create sequence s1; CREATE SEQUENCE regression=# with tt(x,y) as (select x, nextval('s1') from generate_series(1,10) x) regression-# select x,y, nextval('s1') as z from tt; x | y | z ----+----+---- 1 | 1 | 2 2 | 3 | 4 3 | 5 | 6 4 | 7 | 8 5 | 9 | 10 6 | 11 | 12 7 | 13 | 14 8 | 15 | 16 9 | 17 | 18 10 | 19 | 20 (10 rows) If we establish a precedent that WITHs can be thought of as executing before the main command, we will eventually have to de-optimize existing WITH behavior. Or else make up reasons why the inconsistency is okay in some cases and not others, but that will definitely be a case of rationalizing after the fact. 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