postgresqlcommon-table-expressionrecursive-query

Prevent infinite loop in recursive query in Postgresql


Here: (To find infinite recursive loop in CTE) is a discussion how to prevent an infinite loop in a recursive query. There the recursion is prevented on the "query level" - at least in an answer about Postgresql.

Is there a way in Postgresql (10) to implement some kind of safety net to prevent infinite recursions? Is it a feasible way to use statement_timeout for this or is there any other widely accepted way?


Solution

  • In my development environment, I always use two fuses for recursive queries or functions. My client automatically sets on startup

    set statement_timeout to '10s'
    

    It is very rare that I need more and quite often it saves me from a dead loop.

    When I write a recursive query from scratch I always use an additional column that limits the number of levels involved, something like this:

    with recursive cte (root, parent, depth) as (
        select id, parent_id, 1
        from ...
    union all
        select c.id, t.parent_id, depth+ 1
        from ...
        where depth < 10
    )
    select * 
    from cte;
    

    In production both these ways may be problematic. Instead, you can adjust the value of the configuration parameter max_stack_depth (integer) to the anticipated needs and capabilities of the operating system and/or hardware.

    See also this answer for an alternative approach and example of the new feature in Postgres 14+.