Re: WITH RECURSIVE patch V0.1

On Mon, May 19, 2008 at 12:21:20AM -0400, Gregory Stark wrote:
> "Zoltan Boszormenyi" <zb@cybertec.at> writes:
> > 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.

That would be a very good thing for libpq (and its descendants) to
have :)

> 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.

I'd think not, as it's (in some sense) a Halting Problem.

> 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.

I seem to recall Oracle's implementation can do this traversal on
write operations, but maybe that's just their marketing.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate