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