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.tiikk...@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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
