Re: WITH RECURSIVE patches V0.1 TODO items
От | Hans-Juergen Schoenig |
---|---|
Тема | Re: WITH RECURSIVE patches V0.1 TODO items |
Дата | |
Msg-id | B973AC50-0591-489B-B5A8-0754B37336FD@cybertec.at обсуждение исходный текст |
Ответ на | Re: WITH RECURSIVE patches V0.1 TODO items (David Fetter <david@fetter.org>) |
Ответы |
Re: WITH RECURSIVE patches V0.1 TODO items
|
Список | pgsql-hackers |
hello everybody,
i did some testing with the existing WITH RECURSIVE patch.
i found two issues with patch version 6.
here are the details:
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)
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:
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. Hereare TODO items so far. Lines starting with "*" are my comments andquestions.- 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. AlwaysUNION ALL" is requried. (i.e. "non_recursive_term UNION ALLrecursive_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 0This 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 afull-on materialized views implementation? I'm guessing we'd need tobe 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 workThis 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!: dfetterSkype: davidfetter XMPP: david.fetter@gmail.comRemember to vote!--Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)To make changes to your subscription:
--
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-hackers по дате отправления: