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(at)sss(dot)pgh(dot)pa(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +