Infinite Recursion Guards

With recursive is the only SQL construct that is not guaranteed to terminate, eventually.0 As the DBMS is typically a very crucial system, infinite loops must be prevented at all times. Therefore, the SQL standard provides a distinct feature for that: the cycle clause. A subset of recursive queries can be protected by the use of union [distinct] inside the recursion. Finally, some systems put a limit on the number of times the recursive leg is processed. Most of those systems allow manual adjustment and removal of that limit.

1. Use cycle … restrict • No using … sub-clause
2. No using … sub-clause
3. Configurable
4. False positives possible

Configuring Limits

Some of the systems that abort execution if an execution threshold has exceeded, allow to adjust or even remove that limit.

1. Use 0 for “unlimited”

The following examples show the respective, non-standard syntax to increase the limit to its maximum. The first two variants are added to the respective statement:

WITH … ()
SELECT …
OPTION (maxrecursion 0)

WITH … ()
SELECT /*+ SET_VAR(cte_max_recursion_depth = 4294967295) */ …

The following affects all recursive queries of the current session. These settings can be applied on a global (server) level as well.

SET SESSION max_recursive_iterations = 4294967295

SET SESSION cte_max_recursion_depth = 4294967295

Related

• Tutorial: with — Organize Complex Queries

• Standard Features

  • T131, “Recursive query”

    • cycle: Preventing Infinite Loops in Recursive SQL Queries

  • E071-01, “UNION DISTINCT table operator“

• Non-Standard Features

  • Syntax variants of the cycle clause

    • MariaDB: cycle … restrict

    • Oracle Database: cycle without using

  • Related Features

    • Oracle Database: connect by … nocycle