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

我有下面两条语句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.来自 t b) 的表名； 1. 在子查询中选择a.

我有下面两条语句sql：
0. 不在子查询中
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. 在子查询
中 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);

[0。不在子查询中]无法正常工作，出现错误：
错误：不支持具有多个 segworker 组的查询计划 (cdbdisp.c:500)
提示：可能是由读取或修改分布式表中的数据的函数引起的
上下文：SQL 语句“select sum(pg_total_relation_size('information_schema.sql_languages'))::int8 from gp_dist_random('gp_id');”

[1.在子查询中]工作得很好。

详细测试如下：

gtlions=# select version();
version
------------------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 8.2.15 (Greenplum Database 4.2.7.3 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on May 7 2014 14:31:08
(1 row)

gtlions=# 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);
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');"
gtlions=# explain 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);
QUERY PLAN
-----------------------------------------------------------------------------------------------
Hash Left Anti Semi Join (cost=568.98..235912.69 rows=676396 width=128)
Hash Cond: c.relname = "NotIn_SUBQUERY".tablename::name
-> Hash Left Join (cost=395.97..223194.68 rows=676419 width=128)
Hash Cond: c.relnamespace = n.oid
-> Hash Left Join (cost=2.62..112777.67 rows=676419 width=68)
Hash Cond: c.reltablespace = t.oid
-> Seq Scan on pg_class c (cost=0.00..2751.39 rows=676419 width=72)
Filter: relkind = 'r'::"char" AND relname IS NOT NULL
-> Hash (cost=1.02..1.02 rows=2 width=4)
-> Seq Scan on pg_tablespace t (cost=0.00..1.02 rows=128 width=4)
-> Hash (cost=365.35..365.35 rows=35 width=68)
-> Seq Scan on pg_namespace n (cost=0.00..365.35 rows=2240 width=68)
-> Hash (cost=106.61..106.61 rows=83 width=274)
-> Gather Motion 64:1 (slice1; segments: 64) (cost=0.00..106.61 rows=83 width=274)
-> Subquery Scan "NotIn_SUBQUERY" (cost=0.00..52.66 rows=2 width=274)
-> Seq Scan on t b (cost=0.00..51.83 rows=2 width=24)
(16 rows)

gtlions=# 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);
schemaname | size-1
-------------+---------
public | 32 kB
public | 32 kB
......
......
public | 96 kB
gtlions=# explain 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);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Gather Motion 64:1 (slice7; segments: 64) (cost=445.41..10096.03 rows=1 width=128)
-> Hash Left Join (cost=445.41..10096.03 rows=1 width=128)
Hash Cond: c.reltablespace = t.oid
-> Redistribute Motion 64:64 (slice5; segments: 64) (cost=443.06..10092.81 rows=1 width=132)
Hash Key: c.reltablespace
-> Hash Left Join (cost=443.06..10092.22 rows=1 width=132)
Hash Cond: c.relnamespace = n.oid
-> Redistribute Motion 64:64 (slice3; segments: 64) (cost=54.53..9703.24 rows=1 width=72)
Hash Key: c.relnamespace
-> Hash EXISTS Join (cost=54.53..9702.65 rows=1 width=72)
Hash Cond: c.relname = b.tablename::name
-> Redistribute Motion 1:64 (slice1) (cost=0.00..9621.26 rows=10570 width=72)
Hash Key: c.relname
-> Seq Scan on pg_class c (cost=0.00..2751.39 rows=676419 width=72)
Filter: relkind = 'r'::"char"
-> Hash (cost=53.49..53.49 rows=2 width=24)
-> Redistribute Motion 64:64 (slice2; segments: 64) (cost=0.00..53.49 rows=2 width=24)
Hash Key: b.tablename::name
-> Seq Scan on t b (cost=0.00..51.83 rows=2 width=24)
-> Hash (cost=388.10..388.10 rows=1 width=68)
-> Redistribute Motion 1:64 (slice4) (cost=0.00..388.10 rows=35 width=68)
Hash Key: n.oid
-> Seq Scan on pg_namespace n (cost=0.00..365.35 rows=2240 width=68)
-> Hash (cost=2.32..2.32 rows=1 width=4)
-> Redistribute Motion 1:64 (slice6) (cost=0.00..2.32 rows=2 width=4)
Hash Key: t.oid
-> Seq Scan on pg_tablespace t (cost=0.00..1.02 rows=128 width=4)
(27 rows) 

该问题应该是一个 Bug，等待 TSE 给出修复或者不修复而只能等到下个版本升级了。
-EOF-