PostgreSQL中如何检测递归中的死循环？

在我们的一个测试环境发现服务器响应非常慢，听测试反馈上午还好好的，下午就非常慢了，不知道具体原因，上服务器top一看负载非常大，有大量的PG进程在运行，一直未结束，连接数据库查询pg_stat_activity表看哪些活跃的查询在运行，所有的长时间会话都是由于同一条语句导致，初步定位是由于此语句有问题，怀疑语句性能有问题，但是通过EXPLAIN ANALYZE查看语句的真实的执行计划，数分钟都无法响应，通过EXPLAIN查看执行计划，计划成本都不高，不至于运行这么久，查看数据库日志也没有其它错误信息，查看服务器也没有报错信息，测试IO也没有问题。虽然此语句业务逻辑比较复杂，但是统计语句中的各表数据量并不大，最大的表也就几十万数据，对语句中的所有涉及的表做一次分析统计，问题依旧。最后没办法只有仔细分析下这个业务逻辑复杂的SQL语句，语句中有个点引起了注意，WITH RECURSIVE递归查询，如果存在死循环那么上面的现象就能解释通了，那么怎么查询表中是否有死循环呢？如何才能定位到死循环的节点？

下面分享一个递归中定位死循环的SQL语句：

--构造测试数据
postgres=# CREATE TABLE sys_cbp_test (id INT,parent_id INT);
CREATE TABLE
postgres=# INSERT INTO sys_cbp_test VALUES (1 , NULL ) ,(2, 1) ,(3 , 2) ,(4 , 3) ,(5 , 1) ,(6 , 5) ,(7 , 2) ,(20 , NULL ) ,(21 , 20) ,(22 , 21);
INSERT 0 10

--现有表数据层级关系如下
postgres=# WITH RECURSIVE x ( id , prior_id , parent_id , level , path , root ) AS
postgres-# ( SELECT id , NULL::INT AS prior_id , NULL::INT AS parent_id , 1 ,array[id] , id as root
postgres(#     FROM sys_cbp_test
postgres(#    WHERE parent_id IS NULL
postgres(#    UNION ALL
postgres(#   SELECT b.id , x.id AS prior_id , b.parent_id , level +1 , x.path || b.id , x.root
postgres(#     FROM x, sys_cbp_test b
postgres(#    WHERE x.id = b.parent_id
postgres(# )
postgres-# SELECT id , prior_id , parent_id , level ,'/' || array_to_string( path , '/' ) AS path, root, path
postgres-#   FROM x 
postgres-#  ORDER BY id;
 id | prior_id | parent_id | level |   path    | root |    path    
----+----------+-----------+-------+-----------+------+------------
  1 |          |           |     1 | /1        |    1 | {1}
  2 |        1 |         1 |     2 | /1/2      |    1 | {1,2}
  3 |        2 |         2 |     3 | /1/2/3    |    1 | {1,2,3}
  4 |        3 |         3 |     4 | /1/2/3/4  |    1 | {1,2,3,4}
  5 |        1 |         1 |     2 | /1/5      |    1 | {1,5}
  6 |        5 |         5 |     3 | /1/5/6    |    1 | {1,5,6}
  7 |        2 |         2 |     3 | /1/2/7    |    1 | {1,2,7}
 20 |          |           |     1 | /20       |   20 | {20}
 21 |       20 |        20 |     2 | /20/21    |   20 | {20,21}
 22 |       21 |        21 |     3 | /20/21/22 |   20 | {20,21,22}
(10 rows)

--现插入一条问题数据，形成死循环
postgres=# INSERT INTO sys_cbp_test VALUES (1 , 4);
INSERT 0 1
--此时再运行上面的递归查询语句就陷入了死循环

--用下面语句来检查死循环的节点，cycle为真代表就是循环节点
postgres=# WITH RECURSIVE x ( id, parent_id, path, cycle) AS
postgres-# ( SELECT id, parent_id, array[id], false
postgres(#     FROM sys_cbp_test
postgres(#    WHERE parent_id IS NULL
postgres(#    UNION ALL
postgres(#   SELECT b.id, b.parent_id, x.path || b.id, b.id = ANY(path)
postgres(#     FROM x, sys_cbp_test b
postgres(#    WHERE x.id = b.parent_id
postgres(#      AND NOT cycle
postgres(# )
postgres-# SELECT id, parent_id, array_to_string( path , '->' ) AS path, cycle
postgres-#   FROM x
postgres-#  WHERE cycle
postgres-#  ORDER BY id;
 id | parent_id |     path      | cycle 
----+-----------+---------------+-------
  1 |         4 | 1->2->3->4->1 | t
(1 row)

--下面语句可兼容有死循环的情况，把死循环的节点过滤掉（建议还是应该删除问题节点）
postgres=# WITH RECURSIVE x ( id , prior_id , parent_id , level , path , root, cycle) AS
postgres-# ( SELECT id , NULL::INT AS prior_id , NULL::INT AS parent_id , 1 ,array[id] , id as root, false
postgres(#     FROM sys_cbp_test
postgres(#    WHERE parent_id IS NULL
postgres(#    UNION ALL
postgres(#   SELECT b.id , x.id AS prior_id , b.parent_id , level +1 , x.path || b.id , x.root, b.id = ANY(path)
postgres(#     FROM x, sys_cbp_test b
postgres(#    WHERE x.id = b.parent_id
postgres(#      AND NOT cycle
postgres(# )
postgres-# SELECT id , prior_id , parent_id , level ,'/' || array_to_string( path , '/' ) AS path, root, path, cycle
postgres-#   FROM x
postgres-#  WHERE NOT cycle
postgres-#  ORDER BY id;
 id | prior_id | parent_id | level |   path    | root |    path    | cycle 
----+----------+-----------+-------+-----------+------+------------+-------
  1 |          |           |     1 | /1        |    1 | {1}        | f
  2 |        1 |         1 |     2 | /1/2      |    1 | {1,2}      | f
  3 |        2 |         2 |     3 | /1/2/3    |    1 | {1,2,3}    | f
  4 |        3 |         3 |     4 | /1/2/3/4  |    1 | {1,2,3,4}  | f
  5 |        1 |         1 |     2 | /1/5      |    1 | {1,5}      | f
  6 |        5 |         5 |     3 | /1/5/6    |    1 | {1,5,6}    | f
  7 |        2 |         2 |     3 | /1/2/7    |    1 | {1,2,7}    | f
 20 |          |           |     1 | /20       |   20 | {20}       | f
 21 |       20 |        20 |     2 | /20/21    |   20 | {20,21}    | f
 22 |       21 |        21 |     3 | /20/21/22 |   20 | {20,21,22} | f
(10 rows)

复制