Re: Common Table Expressions (WITH RECURSIVE) patch

2008/9/9 Robert Haas <robertmhaas@gmail.com>:
>>>> >> My interpretation of 7.13: General Rules: 2.b is that it should be
>>>> >> single evaluation, even if RECURSIVE is present.
>>>> >>
>>>> >> The previous discussion was here:
>>>> >>
>>>> >> http://archives.postgresql.org/pgsql-hackers/2008-07/msg01292.php
>>
>> I am  blind, I didn't find any reason, why materialisation isn't useable.
>
> I believe it's because of these two (closely related) problems:
>
> # The basic
> # implementation clearly ought to be to dump the result of the subquery
> # into a tuplestore and then have the upper level read out from that.
> # However, we don't have any infrastructure for having multiple
> # upper-level RTEs reference the same tuplestore.  (Perhaps the InitPlan
> # infrastructure could be enhanced in that direction, but it's not ready
> # for non-scalar outputs today.)  Also, I think we'd have to teach
> # tuplestore how to support multiple readout cursors.  For example,
> # consider
> #       WITH foo AS (SELECT ...) SELECT ... FROM foo a, foo b WHERE ...
> # If the planner chooses to do the join as a nested loop then each
> # Scan node needs to keep track of its own place in the tuplestore,
> # concurrently with the other node having a different place.
>

hmm. I solve similar problem in grouping sets :( etc

SELECT ... FROM ... GROUP BY GROUPING SETS (a,b)

is almost same as

With foo AS (SELECT ... FROM) SELECT ... FROM foo GROUP BY a UNION ALL
SELECT ... FROM foo GROUP BY b;

Regards
Pavel





> ...Robert
>