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

Valery Popov <v(dot)popov(at)postgrespro(dot)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