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

2015-10-28 14:33 GMT+01:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> 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.
>

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

Regards

Pavel

>
> regards, tom lane
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>