Pivotal Knowledge Base

Follow

Function cannot execute on segment node in Pivotal Greenplum

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.x, 4.2.x
OS RHEL 6.x

Symptom

The error messages are shown below: 

ERROR: This query is not currently supported by GPDB.

ERROR: function cannot execute on segment because it accesses relation

Cause

GPDB doesn't support running functions at the segment node directly. Running SQL as shown below will trigger this 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 these steps for 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);

And try to run a query as follows:

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 using the following function :

1. Create 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 a 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;

 

Comments

Powered by Zendesk