Re: wCTE behaviour

On 2010-11-14 21:06, Marko Tiikkaja wrote:
> On 2010-11-14 8:51 PM +0200, Yeb Havinga wrote:
>> On 2010-11-14 19:35, Robert Haas wrote:
>>> On Sun, Nov 14, 2010 at 1:01 PM, Marko Tiikkaja
>>> <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> wrote:
>>>> In my opinion, all of these should have the same effect: DELETE all
>>>> rows
>>>> from "foo". Any other option means we're going to have trouble
>>>> predicting
>>>> how a query is going to behave.
>>> I think it's clear that's the only sensible behavior.
>> What if CTE's ever get input parameters?
>
> What about input parameters?
With input parameters there is a clear link between a CTE and a caller.
If a CTE is called more than once, it must be executed more than once,
e.g. (notation t:x means cte has parameter x)

WITH t:x AS (INSERT INTO foo VALUES(x) RETURNING *)
SELECT (SELECT * FROM t(1)), (SELECT * FROM t(2));
runs the cte two times, hence two new rows in foo.

But what about
WITH t:x AS (INSERT INTO foo VALUES(x) RETURNING *)
SELECT (SELECT t(1)), (SELECT t(1));
it would be strange to expect a single row in foo here, since the only
thing different from the previous query is a constant value.

Though I like the easyness of "run exactly once" for uncorrelated cte's,
I still have the feeling that it somehow mixes the expression and
operational realm. In logic there's a difference between a proposition
and an assertion. With "run exactly once", stating a proposition is made
synonymous to asserting it. That makes syntactic operations or rewriting
of writable CTEs hard, if not impossible. For instance, variable
substitution in the second example makes a CTE without parameters:
WITH t' AS (INSERT INTO foo VALUES(1) RETURNING *),
t'' AS AS (INSERT INTO foo VALUES(1) RETURNING *),
SELECT (SELECT t'), (SELECT t'');

since t' and t'' are equal,

WITH t' AS (INSERT INTO foo VALUES(1) RETURNING *)
SELECT (SELECT t'), (SELECT t');

A syntactic operation like this on the query should not result in a
different operation when it's run. Hence two new rows in foo are still
expected, but the "run exactly once" dictates one new row for that query.

regards,
Yeb Havinga