Prevent infinite loop in recursive query in Postgresql

Question

Sorry - this is kind of vague...

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?

Answer 1

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.

The method described by a_horse_with_no_name is obviously very effective, also in the production environment, but it can not always be used (or it is too expensive).