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

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.

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)

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