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

Re: WITH RECURSIVE patch V0.1

From: Zoltan Boszormenyi <zb(at)cybertec(dot)at>
To: Gregory Stark <stark(at)enterprisedb(dot)com>
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 06:19:17
Message-ID: 48311BE5.2060403@cybertec.at (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Gregory Stark írta:
> 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.
>   

That would be even cooler to have it implemented as well.

>> 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 it's the other way around. The server should not emit infinite 
number 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.
>   

Well, a maintenance resjunk could be used like the branch column in 
tablefunc::connectby().

-- 
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/



In response to

Responses

pgsql-hackers by date

Next:From: Hannu KrosingDate: 2008-05-19 07:46:18
Subject: Re: notification information functions
Previous:From: Gregory StarkDate: 2008-05-19 05:22:54
Subject: Re: WITH RECURSIVE patch V0.1

pgsql-patches by date

Next:From: Martijn van OosterhoutDate: 2008-05-19 08:42:23
Subject: Re: [HACKERS] WITH RECURSIVE patch V0.1
Previous:From: Gregory StarkDate: 2008-05-19 05:22:54
Subject: Re: WITH RECURSIVE patch V0.1

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