Thread: [PROPOSAL] Max recursion depth in WITH Queries (Common Table Expressions)

Hi, Hackers

Recursive queries are typically used to deal with hierarchical or
tree-structured data.
In some conditions when data contain  relationships with cycles recursive query will loop
unlimited and significantly slows the client's session.
To prevent "infinite" loop I suggest the max_recursion_depth parameter,
which defines the maximum recursion level during the execution of recursive
query.
When max_recursion_depth > 0 and the recursion level of query exceeds
specified value then the execution of query interrupts with error message.
In the MS SQL Server there is MAXRECURSION hint for the same purpose.


Thanks!
--

Regards,
Valery Popov
Postgres Professional http://www.postgrespro.com
The Russian Postgres Company

Valery Popov <v.popov@postgrespro.ru> writes:
> Recursive queries are typically used to deal with hierarchical or
> tree-structured data.
> In some conditions when data contain  relationships with cycles recursive query will loop
> unlimited and significantly slows the client's session.

The standard way of dealing with that is to include logic in the query to
limit the recursion depth, for example

WITH RECURSIVE t(n) AS (   SELECT 1 UNION ALL   SELECT n+1 FROM t WHERE n < 10
)
SELECT n FROM t;

I don't see an example of this technique in the documentation, which maybe
is a documentation improvement opportunity.

> To prevent "infinite" loop I suggest the max_recursion_depth parameter,
> which defines the maximum recursion level during the execution of recursive
> query.

Controlling this via a GUC is a seriously awful idea.  We learned a long
time ago to avoid GUCs that have a direct impact on query semantics; the
scope of their effects is just about never what you want.

Also, there are already ways to constrain queries-gone-crazy; particularly
statement_timeout, which has the advantage that it works for other types
of badly-written queries not only this one.
        regards, tom lane

Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2015-10-28 14:33 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
>> Also, there are already ways to constrain queries-gone-crazy; particularly
>> statement_timeout, which has the advantage that it works for other types
>> of badly-written queries not only this one.

> isn't the recursive limits much more a resource limit like work_mem etc?

Exceeding work_mem isn't generally supposed to result in an error --- it
causes, or should cause, the system to shift execution strategy so that
you get the same answer with less memory and more time consumption.

In any case, the question is what purpose this would serve that isn't
already covered perfectly well by existing features like
statement_timeout.
        regards, tom lane

28.10.2015 16:33, Tom Lane пишет:
> Valery Popov <v.popov@postgrespro.ru> writes:
>> Recursive queries are typically used to deal with hierarchical or
>> tree-structured data.
>> In some conditions when data contain  relationships with cycles recursive query will loop
>> unlimited and significantly slows the client's session.
> The standard way of dealing with that is to include logic in the query to
> limit the recursion depth, for example
>
> WITH RECURSIVE t(n) AS (
>      SELECT 1
>    UNION ALL
>      SELECT n+1 FROM t WHERE n < 10
> )
> SELECT n FROM t;
>
Yes, I agree with this thesis.  But I think in some cases would be 
better to receive error message and stop execution than results will 
incomplete.

-- 
Regards,
Valery Popov
Postgres Professional http://www.postgrespro.com
The Russian Postgres Company

Valery Popov <v.popov@postgrespro.ru> writes:
> 28.10.2015 16:33, Tom Lane �����:
>> The standard way of dealing with that is to include logic in the query to
>> limit the recursion depth, for example ...

> Yes, I agree with this thesis.  But I think in some cases would be 
> better to receive error message and stop execution than results will 
> incomplete.

Sure, but you can do that at the SQL level if you have a mind to, as well.

In practice, I think people tend to use recursive queries mainly for data
layouts where the maximum recursion depth isn't terribly clear, so that
setting this GUC to a useful value would be a difficult task anyway.
If you end up setting it to 100X or 1000X more than you think your queries
could possibly recurse, you might as well use some other approach like
statement_timeout, which has got a closer relationship to what you care
about, ie how long you want to wait.
        regards, tom lane