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 <[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-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 > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers