Re: [PATCHES] [HACKERS] WITH RECURSIVE updated to CVS TIP

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
>

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