Re: wCTE behaviour

Marko Tiikkaja <marko.tiikkaja@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 |
1810| 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