Function cannot execute on the segment node in Pivotal Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

The following error messages are produced: 

"ERROR: This query is not currently supported by GPDB".

"ERROR: function cannot execute on segment because it accesses relation".

Environment

Cause

GPDB doesn't support running functions at the segment node directly. Running the SQL query as shown below will trigger the "function cannot execute on segment because it accesses relation" error:

Insert into xxx select * from function_name();
create table xxxx as select * from function_name();
BEGIN
FOR r IN SELECT * FROM gp_toolkit.gp_size_of_table_uncompressed where sotutablename like '%archive%'
LOOP

Resolution

Follow the steps below to address the failed SQL:

CREATE TABLE size_of_table_uncompressed
(
rundt timestamp without time zone DEFAULT now(),
sotuoid oid,
sotusize double precision,
sotuschemaname name,
sotutablename name
)
WITH (
OIDS=FALSE
)
DISTRIBUTED BY (rundt);

Run the following query: 

insert into public.size_of_table_uncompressed (sotuoid,sotusize,sotuschemaname,sotutablename) 
select sotuoid,sotusize,sotuschemaname,sotutablename from gp_toolkit.gp_size_of_table_uncompressed

This can be rewritten creating the table and using the following function:

1. Create the table:

create table s2
(
rundt timestamp without time zone DEFAULT now(),
sotuoid oid,
sotusize bigint,
sotuschemaname text,
sotutablename text
)
WITH (
OIDS=FALSE
)
DISTRIBUTED BY (rundt);

2. Create the function:

CREATE or replace FUNCTION test_function(nsname text) RETURNS void
AS $$
DECLARE
ns_sotuoid oid;
ns_sotusize bigint;
ns_sotuschemaname text;
ns_sotutablename text;
BEGIN
select sotuoid from gp_toolkit.gp_size_of_table_uncompressed where
sotutablename = nsname into ns_sotuoid;
select sotusize from gp_toolkit.gp_size_of_table_uncompressed where
sotutablename = nsname into ns_sotusize;
select sotuschemaname from gp_toolkit.gp_size_of_table_uncompressed where
sotutablename = nsname into ns_sotuschemaname;
select sotutablename from gp_toolkit.gp_size_of_table_uncompressed where
sotutablename = nsname into ns_sotutablename;
insert into s2 (rundt ,
sotuoid ,
sotusize ,
sotuschemaname ,
sotutablename)
values (now(),ns_sotuoid,ns_sotusize,ns_sotuschemaname,nsname);
RETURN;
END;
$$ LANGUAGE plpgsql;

3. Run this function with all user tables:

select test_function(tablename) from pg_tables where schemaname not like '%pg_%' and schemaname not in ('information_schema','gp_toolkit');

4. Check the output:

select * from s2;