Statistics function and sub-object size information query bug

I HAVA below-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. subquery] can ' t work well, it ' s occur error:
Error:query plan with multiple segworker groups are not supported (cdbdisp.c:500)
Hint:likely caused by a function then 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:

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 14:31:08 (1 row) gtlions=# Select A.schemaname, Pg_size_pretty (pg_t Otal_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 isn't supported (cdbdisp.c:500) hint:likely caused by a function that re Ads or modifies data in a distributed tablecontext: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 row s=676419 width=72) Filter:relkind = ' R ':: ' char ' and relname is not null-> Hash (cost=1.02..1.02 rows=2 width=4) Se Q 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 Sca N on Pg_namespace N (cost=0.00..365.35 rows=2240 width=68), Hash (cost=106.61..106.61 rows=83 width=274), Gather M Otion 64:1 (Slice1; segments:64) (cost=0.00..106.61 rows=83 width=274), subquery Scan "Notin_subquery" (cost=0.00..5 2.66 rows=2 width=274), Seq Scan on tB (cost=0.00..51.83 rows=2 width=24) (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 | Kbpublic | Kb............public | kbgtlions=# 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=4 45.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-> Re Distribute Motion 1:64 (Slice1) (cost=0.00..9621.26 rows=10570 width=72) Hash key:c.relname-> Seq Scan on Pg_class C (c ost=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 Sc  An 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 W idth=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) (rows)  

The problem should be a bug, wait for the TSE in view of fix or not fix and just wait until the next version is upgraded.
-eof-

Copyright notice: This article Bo Master original articles, blogs, without consent may not be reproduced.

Statistics function and sub-object size information query bug