Re: wCTE behaviour

On Fri, Nov 12, 2010 at 10:25 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

Yeah, I think we have to force any DML statements in CTEs to run to
completion, whether we need the results or not, and even if they are
unreferenced. Otherwise it's going to be really confusing, I fear.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company