[问题记录.pg]使用 WITH RECURSIVE 时遇到问题数据，引发死循环。如何避免？

问题现象：

某项目偶发出现服务器内存和CPU涨爆的问题。

问题排查：

通过抓取问题出现时的dump，定位到是在执行一句pg库的sql。而这段sql是递归查询，通过参数检查数据，确认是出现了死循环。

问题解决：

1、修正问题数据

2、完善sql，应对递归循环很多层的问题

顺道把验证案例备忘在这里

1、创建测试表并插入验证数据

注意，此时数据不会出现死循环

 CREATE TABLE test_recursive (id int,pid int);
 
 INSERT INTO test_recursive VALUES (1 , NULL) ,(2, 1) ,(3 , 2) ,(4 , 3) ,(5 , 4) ,(6 , 5) ,(7 , 6);
 

2、未处理死循环的执行SQL

 WITH RECURSIVE x(id, pid, root) AS ( 
 	SELECT id, pid, id as root
      FROM test_recursive 
     WHERE id=1
    UNION ALL
    SELECT t.id, t.pid, x.root
      FROM test_recursive t, x
     WHERE x.id = t.pid
)
 SELECT id, pid, root
   FROM x  
  ORDER BY id;

3、写入问题数据，引发死循环

 update test_recursive set pid = 7 where id = 1;

4、完善后的SQL（遇到死循环会终止）

 WITH RECURSIVE x(id, pid, root, level, path, cycle) AS ( 
 	SELECT id, pid, id as root, 1, array[id], false
      FROM test_recursive 
     WHERE id=1
    UNION ALL
    SELECT t.id, t.pid, x.root, x.level+1, x.path||t.id, t.id=ANY(path)
      FROM test_recursive t, x
     WHERE x.id = t.pid and not x.cycle 
)
 SELECT id, pid, root, level, array_to_string(path,'\') AS path, cycle
   FROM x 
  where not x.cycle 
  ORDER BY id;