Re: wCTE behaviour

On 2010-11-13 14:41, David Fetter wrote:
> On Sat, Nov 13, 2010 at 02:28:35PM +0100, Yeb Havinga wrote:
>> 1) the name "Common Table Expression" suggests that t must be
>> regarded as an expression, hence syntactically / proof theoretic and
>> not as a table, set of rows / model theoretic. I.e. it is not a
>> "Common Table".
> Disagree. A table never referred to in a query still exists.
> Similarly, if a normal CTE called a data-changing function but was
> nevertheless not referred to, it would still run.
with t as (select nextval('seq'))
select 1;

does not update the sequence.

>> 2) The expressions can be referenced zero, one or more times. To me
>> it therefore makes the most sense that a DML expressions that is
>> defined but not references has no effect. Referenced once: run the
>> plan once. Referenced again: run the plan again.
> No. When I designed this feature, it was precisely to take advantage
> of the "run exactly once" behavior of CTEs. Under no circumstances
> should we break this.
I found the pgday2009 presentation
http://wiki.postgresql.org/images/c/c0/PGDay2009-EN-Writeable_CTEs_The_Next_Big_Thing.pdf
- the IO minimization example is cool, and I now understand that it
would be artificial if the CTE had to be referenced, for it to be
executed. Makes sense.

regards,
Yeb Havinga