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

Re: WITH RECURSIVE patch V0.1

From: Zoltan Boszormenyi <zb(at)cybertec(dot)at>
To: Tatsuo Ishii <ishii(at)postgresql(dot)org>
Cc: 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-18 21:22:02
Message-ID: 48309DFA.8080703@cybertec.at (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
David Fetter írta:
> On Sun, May 18, 2008 at 08:51:29PM +0900, Tatsuo Ishii wrote:
>   
>> WITH RECURSIVE patch V0.1
>>
>> Here are patches to implement WITH RECURSIVE clause. There are some
>> limitiations and TODO items(see the "Current limitations" section
>> below). Comments are welcome.
>>
>> 1. Credit
>>
>> These patches were developed by Yoshiyuki Asaba (y-asab(at)sraoss(dot)co(dot)jp)
>> with some discussions with Tatsuo Ishii (ishii(at)sraoss(dot)co(dot)jp).
>>     
>
> This is really great!  Kudos to all who made this happen :)
>
> I tried a bunch of different queries, and so far, only these two
> haven't worked.  Any ideas what I'm doing wrong here?
>
> WITH RECURSIVE t(n) AS (
>     SELECT 1
> UNION ALL
>     SELECT n+1
>     FROM t
>     WHERE n < 100
> )
> SELECT * FROM t;
> ERROR:  cannot extract attribute from empty tuple slot
>
> WITH RECURSIVE t(n) AS (
>     VALUES (1)
> UNION ALL
>     SELECT n+1
>     FROM t
>     WHERE n < 100
> )
> SELECT * FROM t;
> ERROR:  cannot extract attribute from empty tuple slot
>
> Cheers,
> David.
>   

Here's a test case attached shamelessly stolen from
http://www.adp-gmbh.ch/ora/sql/connect_by.html

This query (without naming toplevel columns) works:

# with recursive x as (select * from test_connect_by where parent is 
null union all select base.* from test_connect_by as base, x where 
base.parent = x.child) select * from x;
 parent | child
--------+-------
        |    38
        |    26
        |    18
     18 |    11
     18 |     7
     26 |    13
     26 |     1
     26 |    12
     38 |    15
     38 |    17
     38 |     6
     17 |     9
     17 |     8
     15 |    10
     15 |     5
      5 |     2
      5 |     3
(17 rows)

It even works when I add my "level" column:

# with recursive x(level, parent, child) as (select 1::bigint, * from 
test_connect_by where parent is null union all select x.level + 1, 
base.* from test_connect_by as base, x where base.parent = x.child) 
select * from x;
 level | parent | child
-------+--------+-------
     1 |        |    38
     1 |        |    26
     1 |        |    18
     2 |     18 |    11
     2 |     18 |     7
     2 |     26 |    13
     2 |     26 |     1
     2 |     26 |    12
     2 |     38 |    15
     2 |     38 |    17
     2 |     38 |     6
     3 |     17 |     9
     3 |     17 |     8
     3 |     15 |    10
     3 |     15 |     5
     4 |      5 |     2
     4 |      5 |     3
(17 rows)

But I have a little problem with the output.
If it's not obvious, here is the query tweaked a little below.

# 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.parent = x.child) 
select lpad(' ', 4*level - 1) || child from x;
     ?column?    
------------------
    38
    26
    18
        11
        7
        13
        1
        12
        15
        17
        6
            9
            8
            10
            5
                2
                3
(17 rows)

Can we get the rows in tree order, please? I.e. something like this:

     ?column?    
------------------
    38
        15
            10
            5
                2
                3
        17
            9
            8
        6
    26
        13
        1
        12
    18
        11
        7
(17 rows)

After all, I didn't specify any ORDER BY clauses in the base, recursive 
or the final queries.

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

Also, there's another rough edge:

# with recursive x as (select * from test_connect_by where parent is 
null) select * from x;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

Best regards,
Zoltán Böszörményi

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


Attachment: testconn.sql
Description: text/x-sql (878 bytes)

In response to

Responses

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2008-05-18 21:50:30
Subject: Re: Link requirements creep
Previous:From: Dave PageDate: 2008-05-18 21:16:17
Subject: Re: odd output in restore mode

pgsql-patches by date

Next:From: Merlin MoncureDate: 2008-05-18 23:02:04
Subject: Re: WITH RECURSIVE patch V0.1
Previous:From: Dave PageDate: 2008-05-18 21:16:17
Subject: Re: odd output in restore mode

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