Re: Re: Should we have an optional limit on the recursion depth of recursive CTEs?

Greg Stark wrote:
> On Mon, Aug 15, 2011 at 9:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > ... and that would be a seriously bad API. ?There are not SUSET
> > restrictions on other resources such as work_mem. ?Why do we need
> > one for this?
> 
> I think a better analogy would be imposing a maximum number of rows a
> query can output. That might be a sane thing to have for some
> circumstances but it's not useful in general.
> 
> Consider for instance my favourite recursive query application,
> displaying the lock dependency graph for pg_locks. What arbitrary
> maximum number of locks would you like to impose at which the query
> should error out?
> 
> There is a situation though that I think is motivating this though
> where it would be nice to detect a problem: when the query is such
> that it *can't* produce a record because there's an infinite loop
> before the first record. Ideally you want some way to detect that
> you've recursed and haven't changed anything that could lead to a
> change in the recursion condition. But that seems like a pretty hard
> thing to detect, probably impossible.

Actually, using UNION instead of UNION ALL does prevent some infinite
loops:
WITH RECURSIVE source AS (        SELECT 'Hello'        UNION        SELECT 'Hello' FROM source)SELECT * FROM source;

Change that to UNION ALL and you have an infinite loop.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +