Re: wCTE behaviour

Yeb Havinga <yebhavinga(at)gmail(dot)com> writes:
> On 2010-11-11 17:50, Marko Tiikkaja wrote:
>> 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?

> Since t is not referenced in the query, foo should not be deleted at
> all,

Yeah, that's another interesting question: should we somehow force
unreferenced CTEs to be evaluated anyhow? Now that I think about it,
there was also some concern about the possibility of the outer query
not reading the CTE all the way to the end, ie

WITH t AS (DELETE FROM foo RETURNING *)
SELECT * FROM t LIMIT 1;

How many rows does this delete? I think we concluded that we should
force the DELETE to be run to conclusion even if the outer query didn't
read it all. From an implementation standpoint that makes it more
attractive to do the DELETE first and stick its results in a tuplestore
--- but I still think we should view that as an implementation detail,
not as part of the specification.

regards, tom lane