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