Re: WITH RECURSIVE patch V0.1

This is indeed really cool. I'm sorry I haven't gotten to doing what I
promised in this area but I'm glad it's happening anyways.

"Zoltan Boszormenyi" <zb(at)cybertec(dot)at> writes:

> Can we get the rows in tree order, please?
>...
> After all, I didn't specify any ORDER BY clauses in the base, recursive or the
> final queries.

The standard has a clause to specify depth-first order. However doing a
depth-first traversal would necessitate quite a different looking plan and
it's far less obvious (to me anyways) how to do it.

> Also, it seems there are no infinite recursion detection:
>
> # with recursive x(level, parent, child) as (
> select 1::integer, * from test_connect_by where parent is null
> union all
> select x.level + 1, base.* from test_connect_by as base, x where base.child
> = x.child
> ) select * from x;
> ... it waits and waits and waits ...

Well, psql might wait and wait but it's actually receiving rows. A cleverer
client should be able to deal with infinite streams of records.

I think DB2 does produce a warning if there is no clause it can determine will
bound the results. But that's not actually reliable. It's quite possible to
have clauses which will limit the output but not in a way the database can
determine. Consider for example a tree-traversal for a binary tree stored in a
recursive table reference. The DBA might know that the data contains no loops
but the database doesn't.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!