Re: WITH RECURSIVE patches V0.1 TODO items - Mailing list pgsql-hackers

Thanks for the report.

> hello everybody,
>
> i did some testing with the existing WITH RECURSIVE patch.
> i found two issues with patch version 6.
> here are the details:
>
> test=# explain select count(*) from ( WITH RECURSIVE t(n) AS ( SELECT
> 1 UNION ALL SELECT n+1 FROM t ) SELECT * FROM t WHERE n < 5000000000)
> as t WHERE n < 100;
>                                 QUERY PLAN
> ------------------------------------------------------------------------
> -
>   Aggregate  (cost=0.06..0.07 rows=1 width=0)
>     ->  Recursion on t  (cost=0.00..0.05 rows=2 width=0)
>           ->  Append  (cost=0.00..0.03 rows=2 width=4)
>                 ->  Result  (cost=0.00..0.01 rows=1 width=0)
>                 ->  Recursive Scan on t  (cost=0.00..0.00 rows=1
> width=4)
> (5 rows)
>
>
> this works nicely and gives me the correct result.
> if i add a DISTINCT clause to the scenario i get a core dump inside
> the planner code:
>
> test=# explain select count(*) from ( WITH RECURSIVE t(n) AS ( SELECT
> 1 UNION ALL SELECT DISTINCT n+1 FROM t ) SELECT * FROM t WHERE n <
> 5000000000) as t WHERE n < 100;
> 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: Failed.

This is new to me. I will add this to the TODO list.

> the second problem seems to be even a little more tricky:
>
> test=# select count(*) from ( WITH RECURSIVE t(n) AS ( SELECT 1 UNION
> ALL SELECT n + 1 FROM t   )    SELECT * FROM t WHERE n < 5000000000)
> as t WHERE n < 100;
>   count
> -------
>      99
> (1 row)
>
> this gives me proper answers - 99 is absolutely correct. it even
> executes fast so it is not producing the giant subselect before
> applying the outer WHERE clause.
> all perfect. but what happens when the < 100 is replaced with a
> subselect containing a WITH RECURSIVE?
>
> test=# select count(*) from ( WITH RECURSIVE t(n) AS (
>     SELECT 1 UNION ALL SELECT n + 1 FROM t
>   )
> SELECT * FROM t WHERE n < 5000000000) as t WHERE n <
>   (
>     select count(*) from ( WITH RECURSIVE t(n) AS
> (
>   SELECT 1 UNION ALL SELECT n + 1 FROM t
> )
> SELECT * FROM t WHERE n < 5000000000) as t WHERE n <
> 100)                                             ;
>   count
> -------
>       1
> (1 row)
>
>
> the result should definitely not be 1 if i am not totally wrong.
> the subselect will give me 99; so the next level should see 99 and
> give me 98 as the answer.
> my plan looks like that:
>
>   Aggregate  (cost=0.13..0.14 rows=1 width=0)
>     InitPlan
>       ->  Aggregate  (cost=0.06..0.07 rows=1 width=0)
>             ->  Recursion on t  (cost=0.00..0.05 rows=2 width=0)
>                   ->  Append  (cost=0.00..0.03 rows=2 width=4)
>                         ->  Result  (cost=0.00..0.01 rows=1 width=0)
>                         ->  Recursive Scan on t  (cost=0.00..0.00
> rows=1 width=4)
>     ->  Recursion on t  (cost=0.00..0.06 rows=2 width=0)
>           ->  Append  (cost=0.00..0.04 rows=2 width=4)
>                 ->  Result  (cost=0.00..0.01 rows=1 width=0)
>                       One-Time Filter: (1 < $0)
>                 ->  Recursive Scan on t  (cost=0.00..0.00 rows=1
> width=4)
> (12 rows)
>
> is this a known issue already?

This is new too.

Other issue I found yesterday was VIEW + WITH RECURSIVE combo case.

I will update the TODO list today.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

>     best regards,
>
>         hans
>
>
>
>
>
>
> On May 27, 2008, at 4:23 AM, David Fetter wrote:
>
> > On Tue, May 27, 2008 at 10:10:13AM +0900, Tatsuo Ishii wrote:
> >> Hi,
> >>
> >> Thanks to all who respnoded to the WITH RECURSIVE patches V0.1. Here
> >> are TODO items so far. Lines starting with "*" are my comments and
> >> questions.
> >>
> >> - SEARCH clause not supported
> >>
> >>   * do we need this for 8.4?
> >
> > This would be very handy.
> >
> >> - CYCLE clause not supported
> >>
> >>   * do we need this for 8.4?
> >>
> >> - the number of "partition" is limited to up to 1
> >>
> >>   * do we need this for 8.4?
> >>
> >> - "non_recursive_term UNION recursive_term" is not supported.  Always
> >>   UNION ALL" is requried. (i.e. "non_recursive_term UNION ALL
> >>   recursive_term" is supported)
> >>
> >>   * do we need this for 8.4?
> >
> > Probably not.
> >
> >> - mutually recursive queries are not supported
> >>
> >>   * do we need this for 8.4?
> >>
> >> - mutually recursive queries are not detected
> >>
> >>   * do we need this for 8.4?
> >>
> >> - cost of Recursive Scan is always 0
> >
> > This should probably be fixed, but it leads to problems like:
> >
> >> - infinit recursion is not detected
> >>
> >>   * Tom suggested let query cancel and statement_timeout handle it.
> >
> > Right for this case.  Is there some way to estimate this short of a
> > full-on materialized views implementation?  I'm guessing we'd need to
> > be able to cache the transitive closure of such searches.
> >
> >> - only the last SELECT of UNION ALL can include self recursion name
> >>
> >> - outer joins for recursive name and tables does not work
> >
> > This would be good to fix.
> >
> >> - need regression tests
> >>
> >> - need docs (at least SELECT reference manual)
> >
> > I started on some of that, patch attached.
> >
> > 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<recursive_query-6.patch.bz2>
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
>
>
>
> --
> Cybertec Schönig & Schönig GmbH
> PostgreSQL Solutions and Support
> Gröhrmühlgasse 26, 2700 Wiener Neustadt
> Tel: +43/1/205 10 35 / 340
> www.postgresql-support.de, www.postgresql-support.com
>