Skip site navigation (1) Skip section navigation (2)

Re: WITH RECUSIVE patches 0723

From: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
To: Tatsuo Ishii <ishii(at)postgresql(dot)org>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: WITH RECUSIVE patches 0723
Date: 2008-07-28 14:06:31
Message-ID: 87d4kyrtlk.fsf@news-spur.riddles.org.uk (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
>>>>> "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)

In response to

pgsql-hackers by date

Next:From: Zdenek KotalaDate: 2008-07-28 14:13:11
Subject: Re: Review: DTrace probes (merged version) ver_03
Previous:From: Heikki LinnakangasDate: 2008-07-28 13:46:14
Subject: Re: [PATCHES] odd output in restore mode

pgsql-patches by date

Next:From: Tom LaneDate: 2008-07-28 14:56:31
Subject: Re: WITH RECUSIVE patches 0723
Previous:From: Heikki LinnakangasDate: 2008-07-28 13:46:14
Subject: Re: [PATCHES] odd output in restore mode

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group