Re: WITH RECURSIVE patches V0.1 TODO items

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.

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?

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(at)fetter(dot)org> http://fetter.org/
> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
> Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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(at)postgresql(dot)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