Re: [HACKERS] WITH RECURSIVE updated to CVS TIP

Hi,

From: Hans-Juergen Schoenig <[email protected]>
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
[email protected]


> 
> 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 <[email protected]> http://fetter.org/
> > Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> > Skype: davidfetter      XMPP: [email protected]
> >
> > Remember to vote!
> > Consider donating to Postgres: http://www.postgresql.org/about/ 
> > donate<recursive_query-7.patch.bz2>
> > -- 
> > Sent via pgsql-hackers mailing list ([email protected])
> > 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
> 

-- 
Sent via pgsql-patches mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches