function cannot execute on segment because it accesses relation

Question

I have a function defined as following in Greenplum postgres

CREATE OR REPLACE FUNCTION vin_temp_func(j text) RETURNS integer AS $$
Declare varx integer;
BEGIN
select count(*) into varx
from T_perf a
left join T_profile b on a.sr_number = b.sr_number  where b.product_name like '%V1%' and
a.submit_date >= (('2013-02-01'::date - CAST(EXTRACT(DOW FROM '2013-02-01'::date) as int)) - 7)+'1 week'::interval and 
a.submit_date <= ('2013-02-01'::date - CAST(EXTRACT(DOW FROM '2013-02-01'::date)+1 as int)) + '1 week'::interval+'23 hours'::interval+'59 minutes'::interval+'59 seconds'::interval
and b.product_name = j;
RETURN varx;
        END;
$$ LANGUAGE plpgsql;

Table Defined like this

drop table if exists prod_week_A;

create table prod_week_A as (
select product_name 
from T_profile where product_name like '%V1%' limit 100)

When I try to execute following I get the error "function cannot execute on segment because it accesses relation"

select product_name, vin_temp_func(product_name) 
from prod_week_A limit 100;

Could some one help me out fix this . Thanks!

Answer 1

Functions in Greenplum are limited compared to Postgress. If a function access a "relation" (think table) you cannot call it in the select of another table.

So it is fine to call your function manually

select vin_temp_func('product1')

will work. But as you saw

select product_name, vin_temp_func(product_name) from prod_week_A limit 100;

is going to give you that error.

You might be able to rewrite the function as a view, that's possible in a lot of cases but here that might be difficult.

Answer 2

I was in a similar situation.

*select product_name, vin_temp_func(product_name) from prod_week_A;*

What worked is pretty strange and I do not really have an understanding for that. I limited the result of the table I am pulling the rows from. So

*select product_name, vin_temp_func(product_name) from (select product_name from prod_week_A limit 10000000) a;*

I put a huge number in front of limit to return all the rows. It takes time (which it should not) but this works.

Please try it out, and let me know if this works for you too.

Answer 3

I was having a similar problem.

INSERT INTO tableX SELECT * from function_name(...);

Wes Reing's point seems accurate AFAIK-- GPDB really doesn't want to call your function from a statement that references another table. So I decided to break apart the pieces in pl/sql.

DO $$
DECLARE 
  myrow myrowtype;
BEGIN
  SELECT * FROM function_name(...) INTO myrow;
  INSERT INTO tableX (...) (SELECT myrow.*);
  RETURN;
END;
$$ LANGUAGE plpgsql;

This gathers up the output of the function on the master(?) and then pushes it back out to segments during the INSERT. It's a bit less ideal than what we wanted in the first place, which was to shove the output of the function into a table directly on the segments, but the performance drop and added load to the master do not seem like a huge deal in our case.

Hopefully you can adapt this idea to your problem.

Good luck!