统计对象大小信息的函数和子查询的Bug

gtlions

于 2014-10-15 14:36:32 发布

阅读量1.7k

点赞数

本文链接：https://blog.youkuaiyun.com/gtlions/article/details/40110653

版权

在Greenplum 8.2.15版本中，使用`pg_total_relation_size`函数结合`not in`子查询出现错误：`query plan with multiple segworker groups is not supported (cdbdisp.c:500)`。然而，同样的查询结构但使用`in`子查询则能正常工作。这个问题可能是由涉及分布式表的函数引起的。详细测试展示了两种查询计划的差异。目前，解决方案可能依赖于TSE的修复或等待下一个版本的更新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

I hava below two statement sql:
0. not in subquery
select a.schemaname, pg_size_pretty(pg_total_relation_size(a.schemaname||'.'||a.tablename)) from pg_tables a where a.tablename not in (select b.tablename from t b);
1. in subquery
select a.schemaname, pg_size_pretty(pg_total_relation_size(a.schemaname||'.'||a.tablename)) from pg_tables a where a.tablename in (select b.tablename from t b);

The [0. not in subquery] can't work well, it's occur error:
ERROR: query plan with multiple segworker groups is not supported (cdbdisp.c:500)
HINT: likely caused by a function that reads or modifies data in a distributed table
CONTEXT: SQL statement "select sum(pg_total_relation_size('information_schema.sql_languages'))::int8 from gp_dist_random('gp_id');"

The [1. in subquery] work well.

Detailed below test:

最低0.47元/天解锁文章