PostgreSQL 递归死循环案例及解法

PostgreSQL 提供的递归语法是很棒的，例如可用来解决树形查询的问题，解决Oracle用户 connect by的语法兼容性。

请参考
https://yq.aliyun.com/articles/54657

但是如果参与递归查询的数据集有问题，例如数据打结的问题。则会导致递归死循环，可能导致临时文件暴增，把空间占满，影响业务。

假设c1,c2是上下级关系，c2是c1的上级ID。
创建测试表如下

create table test(c1 int, c2 int, info text);

create index idx_test_01 on test(c1);

create index idx_test_02 on test(c2);

插入一组测试数据，其中(1,1,test)是个结，如果用递归查询的话，会导致无法退出循环。

insert into test values 

(9,8,test), 

(8,7,test), 

(7,6,test), 

(6,5,test), 

(5,4,test), 

(4,3,test), 

(3,2,test), 

(2,1,test), 

(1,1,test);

递归查询，从c1=9开始往上检索，到1之后会一直往下走，无法终结。

with recursive t(c1,c2,info) as (

 select * from test where c1=9 

 union all 

 select t2.* from test t2 join t on (t.c2 =t2.c1) 

select count(*) from t;

可以在数据库的临时文件目录，看到不停增长的临时文件。

total 96M

-rw------- 1 digoal digoal 89M Jul 23 20:07 pgsql_tmp8997.5

一段时间后

-rw------- 1 digoal digoal 575M Jul 23 20:09 pgsql_tmp8997.5

total 2.5G

-rw------- 1 digoal digoal 1.0G Jul 23 20:10 pgsql_tmp8997.5

-rw------- 1 digoal digoal 1.0G Jul 23 20:14 pgsql_tmp8997.6

-rw------- 1 digoal digoal 435M Jul 23 20:15 pgsql_tmp8997.7

如何解决死循环的问题

临时文件相关的数据库参数介绍

#temp_buffers = 8MB # min 800kB

临时空间buffer大小

#temp_file_limit = -1 # limits per-session temp file space

 # in kB, or -1 for no limit

单个会话最多允许产生多少临时文件

log_temp_files = 102400 # log temporary files equal or larger

 # than the specified size in kilobytes;

 # -1 disables, 0 logs all temp files

当临时文件使用超过多少时，记录日志 

但是别被它误解，是QUERY结束的时候记录的，中途不记录。 

可以通过改内核实现阶段性的记录。 

#temp_tablespaces =  # a list of tablespace names,  uses

 # only default tablespace

可以指定临时目录的表空间，默认是default tablespace

#

看完以上几个参数，大家应该心里有数了。
通过设置temp_file_limit即可限制当前会话允许使用的最大临时空间。

测试
手工退出刚才的死循环QUERY

postgres=# with recursive t(c1,c2,info) as (select * from test where c1=9 union all select t2.* from test t2 join t on (t.c2 =t2.c1) ) select count(*) from t;

^CCancel request sent

ERROR: 57014: canceling statement due to user request

LOCATION: ProcessInterrupts, postgres.c:2988

QUERY退出后才记录临时文件的日志，社区版本的问题，没有阶段性记录临时空间的使用

2016-07-23 20:17:42.227 CST,"postgres","postgres",8997,"[local]",5793598b.2325,10,"SELECT",2016-07-23 19:48:27 CST,2/2490781,0,ERROR,57014,"canceling statement due to user request",,,,,,"with recursive t(c1,c2,info) as (select * from test where c1=9 union all select t2.* from test t2 join t on (t.c2 =t2.c1) ) select count(*) from t;",,,"psql"

2016-07-23 20:17:43.521 CST,"postgres","postgres",8997,"[local]",5793598b.2325,11,"SELECT",2016-07-23 19:48:27 CST,2/0,0,LOG,00000,"temporary file: path ""base/pgsql_tmp/pgsql_tmp8997.7"", size 1073741824",,,,,,,,,"psql"

2016-07-23 20:17:43.747 CST,"postgres","postgres",8997,"[local]",5793598b.2325,12,"SELECT",2016-07-23 19:48:27 CST,2/0,0,LOG,00000,"temporary file: path ""base/pgsql_tmp/pgsql_tmp8997.6"", size 1073741824",,,,,,,,,"psql"

2016-07-23 20:17:43.991 CST,"postgres","postgres",8997,"[local]",5793598b.2325,13,"SELECT",2016-07-23 19:48:27 CST,2/0,0,LOG,00000,"temporary file: path ""base/pgsql_tmp/pgsql_tmp8997.5"", size 1073741824",,,,,,,,,"psql"

设置会话的临时文件使用为10MB，继续测试，可以看到效果很明显

死循环的问题解决了

postgres=# set temp_file_limit=10MB;

postgres=# with recursive t(c1,c2,info) as (select * from test where c1=9 union all select t2.* from test t2 join t on (t.c2 =t2.c1) ) select count(*) from t;

ERROR: 53400: temporary file size exceeds temp_file_limit (10240kB)

LOCATION: FileWrite, fd.c:1491

RDS PG内核改进建议 建议可以动态设置temp_file_limit，根据实际的剩余空间设置反馈机制，保证有足够的剩余空间，不至于TEMP文件把空间全部撑爆。 可以将会话级别的临时空间限制，改为分组限制。
例如group a 允许使用100MB，group b允许使用1GB。

又或者是用户或数据库级别的限制。
借鉴Greenplum的resource queue的管理手段，把资源控制做起来也是一种方法。
《Greenplum 资源隔离的原理与源码分析》
https://yq.aliyun.com/articles/57763

阶段性的记录临时文件的日志，而不是QUERY结束时记录 用户使用递归语句时一定要注意防止死循环，通过设置会话级别的temp_file_limit可以预防，还有一种方法是使用pg_hint_plan，在语句中使用HINT，例如：

/*+ 

 Set (temp_file_limit=10MB)

with recursive t(c1,c2,info) as (select * from test where c1=9 union all select t2.* from test t2 join t on (t.c2 =t2.c1) ) select count(*) from t;

临时文件会在QUERY结束后自动清理。 数据库启动时，startup进程也会清理temp文件。

祝大家玩得开心，欢迎随时来 阿里云促膝长谈 业务需求 ，恭候光临。

阿里云的小伙伴们加油，努力做 最贴地气的云数据库 。

【走进RDS】之SQL Server性能诊断案例分析 数据库性能诊断不仅对其数据库技能要求较高，而且需要大量的前期准备工作，如收集各种性能基线、性能指标和慢SQL日志等，尤其是面对多数据库性能调优时，往往事倍功半。
一个相同查询在不同 RDS for MySQL 实例上性能差异的案例分析 一个相同查询在不同 RDS for MySQL 实例上性能差异的案例分析 1. 问题出现 2. 问题原因 3. 问题解决 4. 问题结论 相同查询在数据量相近的情况下在不同 RDS for MySQL 实例上有不同的性能表现，容易引发用户对 RDS for MySQL 实例的性能差异性的疑虑，本文分享下近期碰到的一个原因比较隐蔽但很常见的案例。
【云栖号案例 | 新零售】数据库RDS为跨境电商管理平台支撑亿级流水 智赢科技每天面对用户修改价格库存对更新即时性要求高，索引和表结构变更不易。RDS支持数组和分区，降低中间表的数量又可以自动分区，加快开发速度。
【云栖号案例 | 物联网&人工智能】RDS为慧联无限数据库运维减负 业务持续增长没有专业运维人员，导致 MySQL 不堪重负。上云后RDS数据库实现了纵向弹性扩缩容，提供自助服务能力，短期内不需要专业运维人员，控制了成本。
阿里云PostgreSQL案例精选1 - 实时精准营销、人群圈选 PostgreSQL , 阿里云 , 实时精准营销 , 人群圈选 , 广告 几乎所有行业, 如互联网、新零售、教育、游戏等. 应用场景: 根据目标群体的特征, 快速提取目标群体.例如, 在电商行业中, 商家在搞运营活动前, 根据活动的目标群体的特征, 圈选出一批目标用户进行广告推送或活动条件的命中. 在游戏行业中, 运营经常会根据游戏玩家的某些特征圈
阿里云PostgreSQL精选案例 - 实时精准营销、人群圈选 PostgreSQL , 阿里云 , 实时精准营销 , 人群圈选 , 广告 几乎所有行业, 如互联网、新零售、教育、游戏等. 应用场景: 根据目标群体的特征, 快速提取目标群体.