PostgreSQL运维案例--递归查询死循环_死循环的sql 语句

link管理
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
一、问题背景

某日，开发同事上报一sql性能问题，一条查询好似一直跑不出结果，查询了n小时，还未返回结果。比较诡异的是同样的sql，相同的数据量，相同的表大小，且在服务器硬件配置相同的情况下，在另外一套环境查询非常快，毫秒级。
第一时间排查了异常环境的查询进程stack，并抓取了一分钟的strace。从结果得知进程是正常执行的，那么看起来就是查询慢的问题了。
最终发现是递归查询出现了死循环，以下内容均是在个人电脑进行的模拟复现
sql语句如下：
with s as (select * from emp_info where empno='200' and emp_type>'5' and emp_status='Y')
  select 
  s.empno as "staffNo",
  s.emp_type as "empType",
  s.emp_tel_info as "empNum",
  a.cust_name as "Name",
  a.cust_position as "Postion",
  a.cust_addr as "Addr",
  a.cust_tel_info as "Mobile",
    with  recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status 
	from region_tbl f where f.region_code=s.region_code union all select f.ctid, f.region_code,f.parent_region_code,
	f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)
	select r.region_code as "FirstRegCode" 
	from r where r.region_type='5'
	and r.region_status='Y'
    with  recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status 
	from region_tbl f where f.region_code=s.region_code union all select f.ctid, f.region_code,f.parent_region_code,
	f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)
	select r.region_code as "SecondRegCode" 
	from r where r.region_type='4'
	and r.region_status='Y'
    with  recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status 
	from region_tbl f where f.region_code=s.region_code union all select f.ctid, f.region_code,f.parent_region_code,
	f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)
	select r.region_code as "ThirdRegCode" 
	from r where r.region_type='3'
	and r.region_status='Y'
    with  recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status 
	from region_tbl f where f.region_code=s.region_code union all select f.ctid, f.region_code,f.parent_region_code,
	f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)
	select r.region_code as "FurthRegCode" 
	from r where r.region_type='2'
	and r.region_status='Y'
  from s left join cust_info a on s.empno=a.cust_id;
二、问题分析
 
对比了两个坏境的执行计划，代价预估及扫描算子、连接算子看起来都是一样的。 
执行计划如下： 
                                                            QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=8.58..1944.99 rows=1 width=866)
   CTE s
     ->  Index Scan using emp_info_pkey on emp_info  (cost=0.28..8.30 rows=1 width=57)
           Index Cond: ((empno)::text = '200'::text)
           Filter: (((emp_type)::text > '5'::text) AND ((emp_status)::text = 'Y'::text))
   ->  CTE Scan on s  (cost=0.00..0.02 rows=1 width=256)
   ->  Index Scan using cust_info_pkey on cust_info a  (cost=0.28..8.29 rows=1 width=200)
         Index Cond: ((s.empno)::text = (cust_id)::text)
   SubPlan 3
     ->  CTE Scan on r r_1  (cost=479.57..482.09 rows=1 width=118)
           Filter: (((region_type)::text = '5'::text) AND ((region_status)::text = 'Y'::text))
           CTE r
             ->  Recursive Union  (cost=0.28..479.57 rows=101 width=19)
                   ->  Index Scan using region_tbl_pkey on region_tbl f  (cost=0.28..8.29 rows=1 width=19)
                         Index Cond: ((region_code)::text = (s.region_code)::text)
                   ->  Hash Join  (cost=0.33..46.93 rows=10 width=19)
                         Hash Cond: ((f_1.region_code)::text = (r.parent_region_code)::text)
                         ->  Seq Scan on region_tbl f_1  (cost=0.00..39.00 rows=2000 width=19)
                         ->  Hash  (cost=0.20..0.20 rows=10 width=118)
                               ->  WorkTable Scan on r  (cost=0.00..0.20 rows=10 width=118)
   SubPlan 5
     ->  CTE Scan on r r_3  (cost=479.57..482.09 rows=1 width=118)
           Filter: (((region_type)::text = '4'::text) AND ((region_status)::text = 'Y'::text))
           CTE r
             ->  Recursive Union  (cost=0.28..479.57 rows=101 width=19)
                   ->  Index Scan using region_tbl_pkey on region_tbl f_2  (cost=0.28..8.29 rows=1 width=19)
                         Index Cond: ((region_code)::text = (s.region_code)::text)
                   ->  Hash Join  (cost=0.33..46.93 rows=10 width=19)
                         Hash Cond: ((f_3.region_code)::text = (r_2.parent_region_code)::text)
                         ->  Seq Scan on region_tbl f_3  (cost=0.00..39.00 rows=2000 width=19)
                         ->  Hash  (cost=0.20..0.20 rows=10 width=118)
                               ->  WorkTable Scan on r r_2  (cost=0.00..0.20 rows=10 width=118)
   SubPlan 7
     ->  CTE Scan on r r_5  (cost=479.57..482.09 rows=1 width=118)
           Filter: (((region_type)::text = '3'::text) AND ((region_status)::text = 'Y'::text))
           CTE r
             ->  Recursive Union  (cost=0.28..479.57 rows=101 width=19)
                   ->  Index Scan using region_tbl_pkey on region_tbl f_4  (cost=0.28..8.29 rows=1 width=19)
                         Index Cond: ((region_code)::text = (s.region_code)::text)
                   ->  Hash Join  (cost=0.33..46.93 rows=10 width=19)
                         Hash Cond: ((f_5.region_code)::text = (r_4.parent_region_code)::text)
                         ->  Seq Scan on region_tbl f_5  (cost=0.00..39.00 rows=2000 width=19)
                         ->  Hash  (cost=0.20..0.20 rows=10 width=118)
                               ->  WorkTable Scan on r r_4  (cost=0.00..0.20 rows=10 width=118)
   SubPlan 9
     ->  CTE Scan on r r_7  (cost=479.57..482.09 rows=1 width=118)
           Filter: (((region_type)::text = '2'::text) AND ((region_status)::text = 'Y'::text))
           CTE r
             ->  Recursive Union  (cost=0.28..479.57 rows=101 width=19)
                   ->  Index Scan using region_tbl_pkey on region_tbl f_6  (cost=0.28..8.29 rows=1 width=19)
                         Index Cond: ((region_code)::text = (s.region_code)::text)
                   ->  Hash Join  (cost=0.33..46.93 rows=10 width=19)
                         Hash Cond: ((f_7.region_code)::text = (r_6.parent_region_code)::text)
                         ->  Seq Scan on region_tbl f_7  (cost=0.00..39.00 rows=2000 width=19)
                         ->  Hash  (cost=0.20..0.20 rows=10 width=118)
                               ->  WorkTable Scan on r r_6  (cost=0.00..0.20 rows=10 width=118)
(56 rows)
postgres=#
从执行计划来看，代价预估中没有发现非常耗时的步骤。对正常的环境中explain analyze查看实际消耗，实际执行300ms，最终返回了一条数据，和代价预估基本一致。逐步排查，最终将重心放在了递归查询这部分。




    
 
递归部分sql： 
with recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status
 from region_tbl f where f.region_code=s.region_code union all select f.ctid, f.region_code,f.parent_region_code,
 f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code) 
分析sql逻辑，递归条件为f.region_code=r.parent_region_code，并且递归开始的f.region_code字段值为s.region_code=‘1200’，这里的1200是通过对s表进行查询得到的，如下： 
postgres=# select * from emp_info where empno='200' and emp_type>'5' and emp_status='Y';
 region_code | emp_type | emp_tel_info |             emp_name             | emp_status | empno
-------------+----------+--------------+----------------------------------+------------+-------
 1200        | 6        | 85192900896  | d7bcf68fc9d88d8b3f5ed6fa2713abcf | Y          | 200
(1 row)
改写递归部分的sql，查看实际执行，并打印了元组的ctid，如下是limit 10的结果： 
postgres=# with  recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status from region_tbl f where f.region_code='1200' union all select f.ctid, f.region_code,f.parent_region_code,f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)select * from r limit 10;
  ctid   | region_code | parent_region_code | region_type | region_status
---------+-------------+--------------------+-------------+---------------
 (18,75) | 1200        | 1020               | 5           | Y
 (18,76) | 1020        | 1002               | 4           | Y
 (9,108) | 1002        | 120                | 3           | Y
 (18,79) | 120         | 12                 | 2           | N
 (18,81) | 12          | 1                  | 1           | N
 (0,110) | 1           | 3                  | 3           | N
 (0,108) | 3           | 4                  | 6           | N
 (0,109) | 4           | 3                  | 3           | N
 (0,108) | 3           | 4                  | 6           | N
 (0,109) | 4           | 3                  | 3           | N
(10 rows)
再看limit 15的结果： 
postgres=# with  recursive r as (select f.ctid,f.region_code,f.parent_region_code,f.region_type,f.region_status from region_tbl f where f.region_code='1200' union all select f.ctid, f.region_code,f.parent_region_code,f.region_type,f.region_status from region_tbl f,r where f.region_code=r.parent_region_code)select * from r limit 15;
  ctid   | region_code | parent_region_code | region_type | region_status
---------+-------------+--------------------+-------------+---------------
 (18,75) | 1200        | 1020               | 5           | Y
 (18,76) | 1020        | 1002               | 4           | Y
 (9,108) | 1002        | 120                | 3           | Y
 (18,79) | 120         | 12                 | 2           | N
 (18,81) | 12          | 1                  | 1           | N
 (0,110) | 1           | 3                  | 3           | N
 (0,108) | 3           | 4                  | 6           | N
 (0,109) | 4           | 3                  | 3           | N
 (0,108) | 3           | 4                  | 6           | N
 (0,109) | 4           | 3                  | 3           | N
 (0,108) | 3           | 4                  | 6           | N
 (0,109) | 4           | 3                  | 3           | N
 (0,108) | 3           | 4                  | 6           | N
 (0,109) | 4           | 3                  | 3           | N
 (0,108) | 3           | 4                  | 6           | N
(15 rows)
之后还打印了limit 1000，limit 10000的结果。发现一个现象，ctid为(0,108) (0,109)这两条数据一直在交替迭代，所以sql执行慢是一直在交替扫描这两条数据，这条sql在这个环境中是永远都跑不出结果的。 
这两条数据很有特点，目前的递归条件为f.region_code=r.parent_region_code，而这两条数据的值刚好形成了一个闭环，导致递归陷入了死循环。 
postgres=# select ctid,region_code,parent_region_code from region_tbl where region_code in ('3','4');
  ctid   | region_code | parent_region_code
---------+-------------+--------------------
 (0,108) | 3           | 4
 (0,109) | 4           | 3
(2 rows)
另外一个正常的环境中这两条数据并没有形成闭环，如下： 
postgres=# select ctid,region_code,parent_region_code from region_tbl where region_code in ('3','4');
  ctid   | region_code | parent_region_code
---------+-------------+--------------------
 (0,245) | 3           | 0
 (0,246) | 4           | 0
(2 rows)
三、解决方案
 
1.已知是这两条数据导致的问题，那么可以参照正常环境修改数据值，或者从查询条件中剔除这两条数据。实际执行340ms返回一条数据，如下： 
 staffNo | empType |   empNum    |     Name     |            Postion             |        Addr        |   Mobile    | FirstRegCode | SecondRegCode | Thir
dRegCode | FurthRegCode
---------+---------+-------------+--------------+--------------------------------+--------------------+-------------+--------------+---------------+-----
---------+--------------
 200     | 6       | 85192900896 | 运维yuanyuan |  Database administrator | 陕西省西安市高新区 | 13512345678 | 1200         | 1020          | 1002
(1 row)
Time: 339.986 ms
2.为什么要用到递归？递归条件是否可以修改？ 
sql中一些字段的查询是通过递归完成的，例如SecondRegCode字段值为1020是递归输出的第二条结果，即通过1200递归查询出1020。如果不使用递归，那么只能查询到1200（FirstRegCode字段值），这个字段的值是查不到的。也就是说查询某些字段是依赖递归的。 
以当前的sql逻辑，递归条件是无法修改的。 
建议：
 1）如果sql中继续使用递归，那么对于region_code和parent_region_code字段关系一定要做明确的规则处理，比如建立检查约束，明确region_code大于parent_region_code，这样存入的数据就不会出现闭环。 
2）sql作为结构化查询语言对比应用语言，对复杂逻辑的处理存在很多局限性，不如应用代码灵活。因此是否可以考虑将递归处理从sql中拉出来，放到应用代码中处理
PostgreSQL 提供的递归语法是很棒的，例如可用来解决树形查询的问题，解决Oracle用户 connect by的语法兼容性。  
请参考  https://yq.aliyun.com/articles/54657  
但是如果参与递归查询的数据集有问题，例如数据打结的问题。则会导致递归死循环，可能导致临时文件暴增，把空间占满，影响业务。
                                    文章目录前言准备工作实验WITH RECURSIVE 使用限制总结
RECURSIVE，递归查询。他将WITH从单纯的句法便利变成了一种在标准SQL中不能完成的特性。通过使用RECURSIVE，一个WITH查询可以引用它自己的输出。
CREATE TABLE "resource" (
  "id" int4 NOT NULL constraint resource_pk primary key,
  "name" text,
  "parent_id" int4
INSERT INT
                                    The following code generates the same information found in sp_who2, along with some additional troubleshooting information. It also contains the SQL Statement being run, so instead of having to e...
                                    近日，在程序调试时，明明结果集只有两条记录的情况下，执行 查询语句却会一直显示正在执行SQL命令，陷入不能停止的查找状态。
最开始，我还怀疑是不是自己语句写错，后来敢十分肯定语句完全是对的。那么此时我就纠结到抓狂。
经过一番思考，我重新关闭plsql。再次打开查询时，突然就好了。最后，我确定是以下问题；
数据库重复查询时，由于某个原因锁表了。此时的我感觉略无语啊，我们遇到问题总是会陷入程序有
                                    怎么及时发现程序陷入死循环？
首先根据死循环的判断依据：周期性调用相同方法名传入相同参数。那么只需要判断这个方法连续或非连续（多线程）传入的参数是不是和之前一样就可以了，这里我定义了两个静态参数，一个是这个方法当前传入的参数（这里暂时定为一个），另一个参数是连续传入的参数的次数，只要传入参数和当前参数不一样，那么就将当前参数进行修改覆盖，计数置为0。这样在计数达到一定值（10或20或100，具体视业务的复杂程度）的时候就判定此方法陷入了死循环。
发现死循环之后需要让程序执行什么操作呢？
在发现死循环之后及时
                                    由于一些原因，我需要通过接口做个sql查询的功能。通过mybatis的注解来实现，传入任意查询sql，返回结果@Select("${sql}")List> commonSelect(@Param("sql")String sql);为了防止sql有update和insert之类的操作，使用了Druid连接池的过滤功能，让sql变成只读操作，项目本身数据源使用了sharding-jdbc做分表...
                                    一、问题现象
使用pg_basebackup重建从库失败，报错如下：
[postgres@postgres_standby:pg11.5:6548 ~]$ pg_basebackup  -D /opt/postgres/postgresql-11.5/pg11debug/data    -U repuser  -h 192.168.92.128 -P -v
Password:
pg_basebackup: initiating base backup, waiting for checkpoint to c