What actually stops the recursion

Question

Accepted Answer

Recursion stops naturally when the recursive member finds no new rows to add. For a hierarchy traversal, that happens when there are no more children. For a sequence, that happens when the WHERE condition fails. There is no automatic limit - if you have a cycle in your data and no cycle detection, the query will run until it hits the database's max recursion depth (PostgreSQL defaults to 1000 iterations) and then error.