Re: [HACKERS] WITH RECURSIVE updated to CVS TIP - Mailing list pgsql-patches

From Yoshiyuki Asaba
Subject Re: [HACKERS] WITH RECURSIVE updated to CVS TIP
Date
Msg-id 20080707.162221.725436840180024396.y-asaba@sraoss.co.jp
Whole thread Raw
In response to Re: [HACKERS] WITH RECURSIVE updated to CVS TIP  (Hans-Juergen Schoenig <postgres@cybertec.at>)
Responses Re: [HACKERS] WITH RECURSIVE updated to CVS TIP  (David Fetter <david@fetter.org>)
List pgsql-patches
Hi,

From: Hans-Juergen Schoenig <postgres@cybertec.at>
Subject: Re: [PATCHES] [HACKERS] WITH RECURSIVE updated to CVS TIP
Date: Sat, 5 Jul 2008 10:43:57 +0200

> i did some quick testing with this wonderful patch.
> it seems there are some flaws in there still:
>
> test=# explain select count(*)
> test-#         from ( WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL
> SELECT DISTINCT n+1 FROM t )
> test(#                 SELECT * FROM t WHERE n < 5000000000) as t
> test-#         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.
> !> \q
>
> this one will kill the planner :(
> removing the (totally stupid) distinct avoids the core dump.

Thanks. I've fixed on local repository.

> i found one more issue;
>
> -- broken: wrong result
> 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) ;

I've fixed. However, this query enters infinite loop.

WITH RECURSIVE t(n) AS (
         SELECT 1 UNION ALL SELECT n + 1 FROM t)
SELECT * FROM t WHERE n < 5000000000

The planner distributed WHERE-clause into WITH-clause with previous
recursive-patch.

WITH RECURSIVE t(n) AS (
         SELECT 1 WHERE n < 5000000000
         UNION ALL
         SELECT n + 1 FROM t WHERE n < 5000000000)
SELECT * FROM t;

This optimization is in qual_is_pushdown_safe(). So, I've fixed not to
optimize WITH-clause in the function.

Regards,
--
Yoshiyuki Asaba
y-asaba@sraoss.co.jp


>
> if i am not totally wrong, this should give us a different result.
>
> i am looking forward to see this patch in core :).
> it is simply wonderful ...
>
>     many thanks,
>
>         hans
>
>
>
>
>
>
> On Jul 3, 2008, at 1:11 AM, David Fetter wrote:
>
> > Folks,
> >
> > Please find patch enclosed, including some documentation.
> >
> > Can we see about getting this in this commitfest?
> >
> > 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-7.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
>

pgsql-patches by date:

Previous
From: Tom Lane
Date:
Subject: Re: pgbench minor fixes
Next
From: Alvaro Herrera
Date:
Subject: Re: GIN improvements