Re: wCTE behaviour

On 11 Nov 2010, at 19:13, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)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 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.

I apologize, I had misunderstood what you are suggesting. But now
that I do, it seems to be an even worse idea to go your way. Based on
my research, I'm almost certain that the SQL standard says that the
execution order is deterministic if there is at least one DML
statement in the WITH list.

Can anyone confirm this?

Regards,
Marko Tiikkaja