To Find Infinite Recursive Loop in Cte

To find infinite recursive loop in CTE

You haven't specified the dialect or your column names, so it is difficult to make the perfect example...

-- Some random data
IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL
    DROP TABLE #MyTable

CREATE TABLE #MyTable (ID INT PRIMARY KEY, ParentID INT NULL, Description VARCHAR(100))
INSERT INTO #MyTable (ID, ParentID, Description) VALUES
(1, NULL, 'Parent'), -- Try changing the second value (NULL) to 1 or 2 or 3
(2, 1, 'Child'), -- Try changing the second value (1) to 2 
(3, 2, 'SubChild')
-- End random data

;WITH RecursiveCTE (StartingID, Level, Parents, Loop, ID, ParentID, Description) AS
(
    SELECT ID, 1, '|' + CAST(ID AS VARCHAR(MAX)) + '|', 0, * FROM #MyTable
    UNION ALL
    SELECT R.StartingID, R.Level + 1, 
        R.Parents + CAST(MT.ID AS VARCHAR(MAX)) + '|',
        CASE WHEN R.Parents LIKE '%|' + CAST(MT.ID AS VARCHAR(MAX)) + '|%' THEN 1 ELSE 0 END,
        MT.*
        FROM #MyTable MT
        INNER JOIN RecursiveCTE R ON R.ParentID = MT.ID AND R.Loop = 0
)

SELECT StartingID, Level, Parents, MAX(Loop) OVER (PARTITION BY StartingID) Loop, ID, ParentID, Description 
    FROM RecursiveCTE 
    ORDER BY StartingID, Level

Something like this will show if/where there are loops in the recursive cte. Look at the column Loop. With the data as is, there is no loops. In the comments there are examples on how to change the values to cause a loop.

In the end the recursive cte creates a VARCHAR(MAX) of ids in the form |id1|id2|id3| (called Parents) and then checks if the current ID is already in that "list". If yes, it sets the Loop column to 1. This column is checked in the recursive join (the ABD R.Loop = 0).

The ending query uses a MAX() OVER (PARTITION BY ...) to set to 1 the Loop column for a whole "block" of chains.

A little more complex, that generates a "better" report:

-- Some random data
IF OBJECT_ID('tempdb..#MyTable') IS NOT NULL
    DROP TABLE #MyTable

CREATE TABLE #MyTable (ID INT PRIMARY KEY, ParentID INT NULL, Description VARCHAR(100))
INSERT INTO #MyTable (ID, ParentID, Description) VALUES
(1, NULL, 'Parent'), -- Try changing the second value (NULL) to 1 or 2 or 3
(2, 1, 'Child'), -- Try changing the second value (1) to 2 
(3, 3, 'SubChild')
-- End random data

-- The "terminal" childrens (that are elements that don't have childrens
-- connected to them)
;WITH WithoutChildren AS
(
    SELECT MT1.* FROM #MyTable MT1
        WHERE NOT EXISTS (SELECT 1 FROM #MyTable MT2 WHERE MT1.ID != MT2.ID AND MT1.ID = MT2.ParentID)
)

, RecursiveCTE (StartingID, Level, Parents, Descriptions, Loop, ParentID) AS
(
    SELECT ID, -- StartingID 
        1, -- Level
        '|' + CAST(ID AS VARCHAR(MAX)) + '|', 
        '|' + CAST(Description AS VARCHAR(MAX)) + '|', 
        0, -- Loop
        ParentID
        FROM WithoutChildren
    UNION ALL
    SELECT R.StartingID, -- StartingID
        R.Level + 1, -- Level
        R.Parents + CAST(MT.ID AS VARCHAR(MAX)) + '|',
        R.Descriptions + CAST(MT.Description AS VARCHAR(MAX)) + '|', 
        CASE WHEN R.Parents LIKE '%|' + CAST(MT.ID AS VARCHAR(MAX)) + '|%' THEN 1 ELSE 0 END,
        MT.ParentID
        FROM #MyTable MT
        INNER JOIN RecursiveCTE R ON R.ParentID = MT.ID AND R.Loop = 0
)

SELECT * FROM RecursiveCTE 
    WHERE ParentID IS NULL OR Loop = 1

This query should return all the "last child" rows, with the full parent chain. The column Loop is 0 if there is no loop, 1 if there is a loop.

CTE goes in infinite loop?

create table dbo.text_master_test
(
text_id int,
text_details nvarchar(max),
new_text_id int
)
go

insert into text_master_test
values(1, 'det 1',2), (2, 'det 2',3), (3, 'det 3',4), (4, 'det 4',5), (5, 'det 5',5);
go

WITH textHierarchy AS (
    SELECT tm.text_id, tm.new_text_id, nullif(tm.new_text_id, tm.text_id) as next_text_id
    FROM text_master_test tm 
    WHERE tm.text_id = 1
    UNION ALL
    SELECT tm.text_id, tm.new_text_id, nullif(tm.new_text_id, tm.text_id) as next_text_id
    FROM text_master_test as tm
    JOIN textHierarchy AS txtHr ON tm.text_id = txtHr.next_text_id 

)
SELECT * FROM textHierarchy;
go

create function dbo.textrecursion(@start_text_id int)
returns table
as
return
(
WITH textHierarchy 
AS 
(
    SELECT tm.text_id, tm.text_details, tm.new_text_id, 
        nullif(tm.new_text_id, tm.text_id) as next_text_id
    FROM dbo.text_master_test tm 
    WHERE tm.text_id = @start_text_id
    UNION ALL
    SELECT tm.text_id, tm.text_details, tm.new_text_id,
        nullif(tm.new_text_id, tm.text_id) as next_text_id
    FROM dbo.text_master_test as tm
    JOIN textHierarchy AS txtHr ON tm.text_id = txtHr.next_text_id 
)
select text_id, text_details, new_text_id
from textHierarchy
);
go

select *
from dbo.textrecursion(1)

select *
from dbo.textrecursion(4)

select *
from dbo.textrecursion(5)
go

drop function dbo.textrecursion;
go
drop table dbo.text_master_test
go

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

Infinite loop in CTE when parsing self-referencing table

The reason of an infinite loop is the first record where empid=mgrid. To handle this issue you should include a cumulative field (levels in this example) to store mgrid you have already processed and check if emid is already in this list to avoid a loop.

Here is a query:

with Tree as
        (
        SELECT  empid
        ,       mgrid
        ,       1 as lv
        ,       1 as level1
        ,       null as level2
        ,       null as level3
        ,       null as level4
        ,       null as level5
        ,       cast(mgrid as varchar(max)) levels  
        FROM    Employees
        WHERE   empid = 1 and mgrid = 1
        UNION ALL
        SELECT  E.empid
        ,       E.mgrid
        ,       T.lv + 1
        ,       T.level1
        ,       case when T.lv = 1 then E.empid else t.level2 end
        ,       case when T.lv = 2 then E.empid else t.level3 end
        ,       case when T.lv = 3 then E.empid else t.level4 end
        ,       case when T.lv = 4 then E.empid else t.level5 end
        ,       T.levels+','+cast(E.mgrid as varchar(max)) levels   

          FROM    Employees AS E
        JOIN    Tree T
        ON      E.mgrid = T.empid 
                and (','+T.levels+',' 
                      not like 
                     '%,'+cast(E.empid as varchar(max))+',%')
        )
select  *
from Tree
order by empid

And here is SQLFiddle demo

How does a SQL Recursive Query not run into an endless loop?

There is nothing "recursive" in "recursive" queries.

It should have been called "iterative".

There are some differences between vendors but the basic concept is the same:

1. The anchor part (the one that doesn't refer to the "recursive" query name) creates the initial set.

2. The iterative part (the one that refers to the "recursive" query name) is using the last set to creates a new set that now becomes the last set, and so on.
   
   It stops when it gets to an empty set.

And here is an endless query:

with recursive t as (select 1 union all select 1 from t) 
select count(*) from t

Explanation for the OP example

Initial set created by the anchor part, `VALUES (1)`: 
1 record, n=1

Sets created by the iterative part, `SELECT n+1 FROM t WHERE n < 100`:
1 record, n=2 (the initial set has 1 record with n=1 so `SELECT n+1 from t` returns 2)
1 record, n=3 
1 record, n=4
1 record, n=5
.
.
.
1 record, n=99
1 record, n=100 

When n=100 the WHERE condition `WHERE n < 100` causes an empty set to be created 
and the iteration stops.

One way to think on iterative queries:

with        t0 as (select ...)
           ,t1 as (select ... t0 ...)
           ,t2 as (select ... t1 ...)
           ,t3 as (select ... t2 ...)
            .
            .
            .

            select * from t0
union all   select * from t1
union all   select * from t2
union all   select * from t3
union all   ...

t0 is a CTE with no dependencies in other CTE.

t1 is a CTE with dependency in t0.

t2 is a CTE with dependency in t1 (and only t1!).

t3 is a CTE with dependency in t2 (and only t2!).

etc.

t1, t2, t3 etc.. are declared with identical queries, different only in their dependencies.

How can I stop my Postgres recusive CTE from indefinitely looping?

In catalog_listings table listing_name and parent_name is same for child1 and child2
In relator table parent_name and child_name is also same for child1 and child2

These rows are creating cycling recursion.

Just remove those two rows from both the tables:

delete from catalog_listings where id in (4,5)
delete from relator where id in (7,8)

Then your desired output will be as below:

CREATE OR REPLACE FUNCTION get_text_history(textId integer)
RETURNS TABLE (
    text_id integer,
    text_details varchar,
    new_text_id integer
) LANGUAGE sql AS
$$WITH RECURSIVE textHierarchy AS (
    SELECT tm.text_id, tm.text_details, tm.new_text_id
    FROM demotexttable tm 
    WHERE tm.text_id = textId
  UNION
    SELECT tm.text_id, tm.text_details, tm.new_text_id
    FROM textHierarchy AS txtHr 
        JOIN demotexttable AS tm ON tm.text_id = txtHr.new_text_id
    WHERE txtHr.new_text_id IS NOT NULL
)
SELECT * FROM textHierarchy$$;