Re: How to share the result data of separated plan

2010/11/9 Tom Lane <tgl@sss.pgh.pa.us>:
> Hitoshi Harada <umi.tanuki@gmail.com> writes:
>> 2010/11/9 Tom Lane <tgl@sss.pgh.pa.us>:
>>> My opinion is still the same as here:
>>> http://archives.postgresql.org/pgsql-hackers/2010-02/msg00688.php
>
>> Current consensus says:
>
>> WITH x AS (SELECT count(*) FROM t), y AS (DELETE FROM t), z AS (SELECT
>> count(*) FROM t) SELECT x.count, z.count FROM x, z;
>
>> should return 0 for z.count but some number of original rows for
>> x.count.
>
> Consensus according to who?  It's at least as consistent for all the
> queries to start from the same snapshot, meaning that x and z would
> produce the same results (independent of what y does).

Consensus according to the pgsql-hackers archive (I'll collect some
links if you want). But to tell the truth, while trying to implement
it I've been feeling it is really bad design to separate plans not
queries (as rewriter). Through overall the PostgreSQL as an SQL
engine, it is not designed to do so. Especially if you imagine letting
the CCI-style writeable CTEs in SQL functions makes disaster; SQL
functions prepare the plan on the function init and wCTEs need to
execute the child plan since it is necessary before the
CreateQueryDesc, then it results in that only child plans are executed
but main query are not in case of the function wasn't called. Very
inconsistent.

> It might be worth inspecting the SQL2011 draft to see if they provide
> any guidance on what ought to happen here.

I agree we need decision of what to do (or what we want) with this feature.


Regards,

--
Hitoshi Harada