Re: wCTE behaviour

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> WITH t AS (DELETE FROM foo RETURNING *)
> SELECT * FROM t LIMIT 1;
>
> How many rows does this delete? I think we concluded that we should
> force the DELETE to be run to conclusion even if the outer query didn't
> read it all

The counter-example that jumps to mind is unix pipes. It's read-only at
the consumer level but as soon as you stop reading, the producer stops.
I guess that's only talking about the surprise factor, though.

I'm not sure how far we go with the SIGPIPE analogy, but I wanted to say
that maybe that would not feel so strange to some people if the DELETE
were not run to completion but only until the reader is done.

What about this one:

WITH d AS (DELETE FROM foo RETURNING id),
q AS (INSERT INTO queue SELECT 'D', id FROM d)
SELECT * FROM q ORDER BY id LIMIT 10;

For next example, replace INSERT with a MERGE to remove a previously
existing 'I' or 'U' event in the queue when we add a 'D'. Bonus points
if wCTE allows to implement the query without resorting to MERGE at all,
which would be nice in my mind.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support