Re: Hierarchical Queries--Status

On Mon, Sep 04, 2006 at 05:15:57PM +0100, Mark Cave-Ayland wrote:
> 3) Add planner support so that WITH clauses are mapped to a new type of
> node that utilises two tuplestores - an output tuplestore and a working
> tuplestore. The output tuple store will in effect be the contents of the
> table expression while the working tuplestore holds the results of the
> last iteration if recursive. Also implement some kind of WithState node
> which keeps track of the recursion state.

That's basically what I came up with. Basically you have a sort of loop
in the execution plan where tuples that come out are copied into a
tuplestore and run through a particular part of the executor again. The
top-down approach of the executor makes it a bit trickier...

> Having spent some more time today looking at 1) and also at the SQL 2003
> spec, it would seem that other databases offer the WITH clause within
> subqueries and also as part of a view. I'd be interested to hear
> feedback from other developers as to whether it would be possible to
> achieve this level of support within PostgreSQL.

Absolutly possible. The question is how much work :)

Incidently, if you find a way to support common subplans (where a part
of the executor is shared between two executions) that might provide a
way to solve some of the trickier multiple evaluation problems with
rules. Again, it would just be a tuplestore the stored the results for
multiple executions.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.