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';
SET
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文件。