Prevent infinite loop in recursive query in Postgresql

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