query plan with multiple segworker groups is not supported
likely caused by a function that reads or modifies data in a distributed table
使用Greenplum的朋友可能会遇到过这样一个问题,即使没有遇到的话,社区关于这个错误也包含了很多issue。
这个问题说实话不是第一次遇到了,但是当时第一次对内核还不够熟悉,加上时间紧任务重,就没仔细看,放过去了。这次选择了硬刚一波,遗憾的是这次的为比第一次复杂的多得多,最后导致也没有完全理清,大概明白了出错原因,找个方式绕过了。
出错场景: UDF调用:
create or replace function pg_tiered_storage_move_to_storage_cold4(target_oid bigint) returns bigint
as
$$
declare
table_name text;
schema_name text;
tmp_sql text;
catalog_exists bool;
nattrs int;
max_content_id int;
tmp_count1 int;
tmp_count2 int;
state_id bigint;
new_oid bigint;
target text;
filesize_arr bigint[];
slicesize_arr bigint[];
progress_arr bigint[];
handle_arr text[];
md5_arr text[];
etags_arr text[];
md5_local text;
md5_remote text;
md5_str text;
all_complete boolean;
tmp bigint;
tmpstr text;
tmpsegid int;
tmpatt int;
tmp_sql2 text;
filesizestr text;
slicesizestr text;
database_oid bigint;
is_hdfs_storage_type bool;
is_oss_storage_type bool;
storage_type int;
is_origin_aocs bool;
begin
nattrs:=2;
tmp_sql:='';
-- 7. State Schedule_TASK_STATE_GETINFO: get column size:
select case when exists(select task_id from pg_tiered_storage_tasks where table_oid = target_oid and current_state='G') then true else false end into catalog_exists;
if catalog_exists then
filesizestr := 'array[';
slicesizestr := 'array[';
tmp_count1 := 1;
LOOP
IF tmp_count1 >= nattrs + 1 THEN
EXIT; -- exit loop
END IF;
tmp_count2 := 0;
FOR target IN select pg_tiered_storage_column_size(target_oid, tmp_count1) LOOP
filesize_arr[ tmp_count2 * nattrs + tmp_count1 - 1] := split_part(target, ',', 2)::bigint;
slicesize_arr[ tmp_count2 * nattrs + tmp_count1 - 1] := split_part(target, ',', 3)::bigint;
tmp_sql := tmp_sql || 'set allow_system_table_mods = true;update pg_tiered_storage_tasks set current_state=''I'', filesize=' || filesize_arr[ tmp_count2 * nattrs + tmp_count1 - 1] || ',slicesize=' || slicesize_arr[ tmp_count2 * nattrs + tmp_count1 - 1] || ' where table_oid=' || target_oid || ' and attnum =' || tmp_count1 || ' and segment_id=' || tmp_count2 || ';';
raise notice 'get column sql: %', tmp_sql;
tmp_count2 := tmp_count2 + 1;
END LOOP;
tmp_count1 := tmp_count1 + 1;
end loop;
tmp_count1 := 0;
LOOP
IF