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.tiikkaja@cs.helsinki.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