Skip site navigation (1) Skip section navigation (2)

Re: WITH RECURSIVE patch V0.1

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: "Zoltan Boszormenyi" <zb(at)cybertec(dot)at>
Cc: "Tatsuo Ishii" <ishii(at)postgresql(dot)org>, "David Fetter" <david(at)fetter(dot)org>, "PG Hackers" <pgsql-hackers(at)postgresql(dot)org>, <pgsql-patches(at)postgresql(dot)org>
Subject: Re: WITH RECURSIVE patch V0.1
Date: 2008-05-19 04:21:20
Message-ID: 8763tarkjj.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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!

In response to

Responses

pgsql-hackers by date

Next:From: David FetterDate: 2008-05-19 05:17:17
Subject: Re: WITH RECURSIVE patch V0.1
Previous:From: Tom LaneDate: 2008-05-19 02:42:35
Subject: Re: Link requirements creep

pgsql-patches by date

Next:From: David FetterDate: 2008-05-19 05:17:17
Subject: Re: WITH RECURSIVE patch V0.1
Previous:From: Mark MielkeDate: 2008-05-18 23:30:06
Subject: Re: [HACKERS] WITH RECURSIVE patch V0.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group