Re: wCTE behaviour

On Sat, Nov 13, 2010 at 02:28:35PM +0100, Yeb Havinga wrote:
> On 2010-11-12 16:51, David Fetter wrote:
> >On Fri, Nov 12, 2010 at 10:25:51AM -0500, Tom Lane wrote:
> >>
> >>Yeah, that's another interesting question: should we somehow force
> >>unreferenced CTEs to be evaluated anyhow?
> >Yes.
> After a night's sleep I'm still thinking no. Arguments:
> 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.

> 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.

>
> What should the result be of
> WITH t AS (INSERT INTO foo SELECT nextval('seq') RETURNING *)
> SELECT * FROM t
> UNION
> SELECT * FROM t;
>
> 1 or 1,2 ?

1.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate