Why we got error like "function cannot execute on a QE slice because it issues a non-SELECT statement"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Greenplum is an MPP database, under such architecture, in most cases, the master (AKA: QD, Query Dispatcher) will dispatch the job to the segments (AKA: Query Executor), then gather the result from QE at the end.
one of the limitations of such architecture is, we can not execute  non-SELECT statement queries on QE

An example use case that may trigger such error is like below: 
the user would like to collect the AO compaction info based on the list inside a table. 

gpadmin=# select oid,gp_toolkit.__gp_aovisimap_compaction_info(oid) from oid_list ;
ERROR:  function cannot execute on a QE slice because it issues a non-SELECT statement  (seg0 slice1 192.168.6.198:20000 pid=100663)

That is because:

- the user table "oid_list" was distributed across all the segments 
- the query plan will first indentify the segmentID of target row of table "oid_list", the run the UDF __gp_aovisimap_compaction_info() on it 
- In this UDF there is an "EXECUTE" command inside it, Since EXECUTE is a non-select query, it can not run on QE, so get such error. 

Environment

Product Version: 6.18

Resolution

The workaround:

- For such kind of issue, one of the workarounds is, to re-write the UDF, make sure the is no such query like "EXECUTE"
- Please refer to the below example:

1. rewrite the UDF, remove or comment the "EXECUTE" code. 

CREATE FUNCTION __gp_aovisimap_compaction_info_new(ao_oid oid,
    OUT content int, OUT datafile int, OUT compaction_possible boolean,
    OUT hidden_tupcount bigint, OUT total_tupcount bigint, OUT percent_hidden numeric)
    RETURNS SETOF RECORD AS $$
DECLARE
    hinfo_row RECORD;
    threshold int := 10;   -- hardcode the limit to 10
BEGIN
    -- EXECUTE 'show gp_appendonly_compaction_threshold' INTO threshold;
    FOR hinfo_row IN SELECT gp_segment_id,
    gp_toolkit.__gp_aovisimap_hidden_typed(ao_oid)::gp_toolkit.__gp_aovisimap_hidden_t
    FROM gp_dist_random('gp_id') LOOP
        content := hinfo_row.gp_segment_id;
        datafile := (hinfo_row.__gp_aovisimap_hidden_typed).seg;
        hidden_tupcount := (hinfo_row.__gp_aovisimap_hidden_typed).hidden;
        total_tupcount := (hinfo_row.__gp_aovisimap_hidden_typed).total;
        compaction_possible := false;
        IF total_tupcount > 0 THEN
            percent_hidden := (100 * hidden_tupcount / total_tupcount::numeric)::numeric(5,2);
        ELSE
            percent_hidden := 0::numeric(5,2);
        END IF;
        IF percent_hidden > threshold THEN
            compaction_possible := true;
        END IF;
        RETURN NEXT;
    END LOOP;
    RAISE NOTICE 'gp_appendonly_compaction_threshold = %', threshold;
    RETURN;
END;
$$ LANGUAGE plpgsql;

2. Create a user table, with distributed as "replicated", so all the segments will have the same data 

# create table oid_list_replicated (oid oid) distributed replicated;
<... insert some data ....>
# select * from oid_list_replicated limit 3;
  oid
-------
 25949
 25956
 67634

3. Finally, run the query, with gp_dist_random() against the user table, so all the QE will run the UDF.

gpadmin=# select oid, __gp_aovisimap_compaction_info_new(oid) from gp_dist_random('oid_list_replicated') ;
  oid  | __gp_aovisimap_compaction_info_new
-------+------------------------------------
 25956 | (0,1,t,317440,317440,100.00)
 67634 | (0,1,t,2480,2480,100.00)
 25956 | (1,1,t,322560,322560,100.00)
 67634 | (1,1,t,2520,2520,100.00)