PG报错too many range table entries
Tags： PG故障处理PostgreSQL
👉 本文共约3291个字，系统预计阅读时间或需13分钟。
问题现象
postgresql中update执行语句报错too many range table entries
源sql
1 2 3 4 5 6 | with t as (select id from LZLTAB where id=8723 limit 100 ) update LZLTAB set STATUS = '00', FILE_ID = null, DATE_UPDATED = localtimestamp(0) where id in (select id from t) |
如果把update改写成select，可以执行成功
1 2 3 4 5 6 | with t as (select id from LZLTAB where id=8723 limit 100 ) select * from LZLTAB where id in (selec tid from t) id | date_created ------+----------------------------+... 8723 | 2023-06-2118:02:21.161687 (1row) |
主键和分区是这样，分区总共有400个
1 2 3 4 5 6 7 | Partition key: RANGE (partition_key) Indexes: "pk_lzl" PRIMARY KEY, btree (id, partition_key) ... Partitions: lzl_p20230601 FOR VALUES FROM ('20230601') TO ('20230602'), lzl_p20230602 FORVALUES FROM ('20230602') TO ('20230603'), lzl_p20230603 FORVALUES FROM ('20230603') TO ('20230604') |
sql逻辑有很多优化点，但是这里不讨论优化。重点是分析为什么update会报错，和为什么select和update会有区别。
执行explain update报错如下：
1 2 3 4 5 6 7 8 9 10 | explain with t as (selec tid from LZLTAB where id=8723 limit 100 ) update LZLTAB set STATUS = '00', FILE_ID = null, DATE_UPDATED = localtimestamp(0) where id in (select id from t); ERROR: 54000: too many range table entries LOCATION: add_rte_to_flat_rtable, setrefs.c:451 Time: 18341.171 ms (00:18.341) |
explain卡了18秒，然后报错
源码分析
报错直接抛出了源码的位置LOCATION: add_rte_to_flat_rtable, setrefs.c:451 ，直接找到该源码
src/backend/optimizer/plan/setrefs.c其注释是说setrefs.c是完成计划树后的相关工作的
1 2 3 4 | /* *Post-processing of a completed plan tree: fix references to subplan * vars, compute regproc values for operators, etc */ |
找到第451行的函数：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | /* * Add (a copy of) the given RTE to the final rangetable * * In the flat rangetable, we zero out substructure pointers that are not * needed by the executor; this reduces the storage space and copying cost * for cached plans. We keep only the ctename, alias and eref Alias fields, * which are needed by EXPLAIN, and the selectedCols, insertedCols, * updatedCols, and extraUpdatedCols bitmaps, which are needed for * executor-startup permissions checking and for trigger event checking. */ static void add_rte_to_flat_rtable(PlannerGlobal *glob, RangeTblEntry *rte) { ... /* * Check for RT index overflow; it's very unlikely, but if it did happen, * the executor would get confused by varnos that match the special varno * values. */ if (IS_SPECIAL_VARNO(list_length(glob->finalrtable))) ereport(ERROR, (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED), errmsg("too many range table entries"))); ... } |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
