Pivotal Knowledge Base

Follow

Function cannot Execute on the Segment Node in Pivotal Greenplum

Environment

  • Pivotal Greenplum Database (GPDB) 4.2.x and 4.3.x
  • Operating System- Red Hat Enterprise Linux 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