Re: WITH RECUSIVE patches 0723

>>>>> "Tatsuo" == Tatsuo Ishii <ishii(at)postgresql(dot)org> writes:

>> This behaviour is clearly intentional, since the entire mechanism of
>> estate-> es_disallow_tuplestore exists for no other reason, but it
>> seems to me to be clearly wrong. What is the justification for it?

Tatsuo> Yes, this is due to prevent infinit recursion caused by
Tatsuo> following case for example.

[...]

Tatsuo> WITH RECURSIVE x AS (
Tatsuo> SELECT * FROM test WHERE a = 'aaa'

Tatsuo> UNION ALL

Tatsuo> SELECT test.* FROM x LEFT JOIN test on test.a = x.b
Tatsuo> ) SELECT * FROM x;

Tatsuo> Now we think that we were wrong. This type of query should
Tatsuo> run into infinit recursion and it's user's responsibility
Tatsuo> that he does not make such a query.

I agree.

Tatsuo> Another idea would be prohibiting *any* outer joins in the
Tatsuo> recursive term (DB2 style), but this may be overkill.

There are legitimate cases for wanting to do a left join in the
recursion - for example, to use the content of another table to
prune the tree where matching records exist (consider the standard
bill-of-materials example with the addition of another table listing
components already in stock).

--
Andrew (irc:RhodiumToad)