EmmaYang
1 Copper

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

I have googled it, it is said some triggers is not supported in GP ?  my version of GP is the single node edition :

I have one trigger on table :shipments , and the trigger function is listed as below:

CREATE OR REPLACE FUNCTION public.check_shipment_addition()

RETURNS trigger AS

$BODY$

DECLARE

-- Declare a variable to hold the customer ID.

id_number INTEGER;

-- Declare a variable to hold the ISBN.

book_isbn TEXT;

BEGIN

SELECT INTO id_number id FROM customers WHERE id = NEW.customer_id;

-- If there was no matching ID number, raise an exception.

IF NOT FOUND THEN

RAISE EXCEPTION 'Invalid customer ID number.';

END IF;

-- If there is an ISBN that matches the ISBN specified in the

-- new table, retrieve it from the editions table.

SELECT INTO book_isbn isbn FROM editions WHERE isbn = NEW.isbn;

-- If there is no matching ISBN, raise an exception.

IF NOT FOUND THEN

RAISE EXCEPTION 'Invalid ISBN.';

END IF;

-- If the previous checks succeeded, update the stock amount

-- for INSERT commands.

IF TG_OP = 'INSERT' THEN

UPDATE stock SET stock = stock -1 WHERE isbn = NEW.isbn;

END IF;

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql VOLATILE;

ALTER FUNCTION public.check_shipment_addition() OWNER TO gpadmin;

And then I insert one record to table :shipments :

Insert into shipments values(x,x,x,x);

however,I got the error msg as below showed:

/************************************************** ***/

ERROR: function cannot execute on segment because it accesses relation "public.customers" (functions.c:147) (seg1 ssc1-njred3:40001 pid=27567) (cdbdisp.c:1489)

DETAIL:

SQL statement "SELECT id FROM customers WHERE id = $1 "

PL/pgSQL function "check_shipment_addition" line 11 at SQL statement

CONTEXT: SQL statement "INSERT INTO shipments VALUES ( $1 , $2 , $3 , $4 )"

PL/pgSQL function "add_shipment2" line 10 at SQL statement

********** Error **********

ERROR: function cannot execute on segment because it accesses relation "public.customers" (functions.c:147) (seg1 ssc1-njred3:40001 pid=27567) (cdbdisp.c:1489)

SQL state: XX000

Detail:

SQL statement "SELECT id FROM customers WHERE id = $1 "

PL/pgSQL function "check_shipment_addition" line 11 at SQL statement

Context: SQL statement "INSERT INTO shipments VALUES ( $1 , $2 , $3 , $4 )"

PL/pgSQL function "add_shipment2" line 10 at SQL statement

/************************************************** ***/

and if I drop the trigger , it works well, anyone who tell me the cause ?

Your advice is really appreciated !

Tags (2)
0 Kudos
1 Reply
Highlighted
ViadeaZhu
1 Nickel

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

Hi Emma,

Referring to GP Admin Guide:

CREATE TRIGGER

Defines a new trigger. User-defined triggers are not supported in Greenplum

Database.

Due to the distributed nature of a Greenplum Database system, the use of triggers is very limited in Greenplum Database. The function used in the trigger must be IMMUTABLE, meaning it cannot use information not directly present in its argument list. The function specified in the trigger also cannot execute any SQL or modify distributed database objects in any way. Given that triggers are most often used to alter tables (for example, update these other rows when this row is updated), these limitations offer very little practical use of triggers in Greenplum Database. For that reason, Greenplum does not support the use of user-defined triggers in Greenplum Database. Triggers cannot be used on append-only tables.

===========Reproduce Test===========

So we can simply reproduce your error message by following test:

create table xx(id int,id2 int);

insert into xx values(1,1);

create table xx2(id int,id2 int);

insert into xx2 values(1,1);

CREATE OR REPLACE FUNCTION myinsert() RETURNS trigger AS

$$

DECLARE

b_count integer;

BEGIN

SELECT INTO b_count id FROM xx2 WHERE id = NEW.id;

END;

$$

LANGUAGE plpgsql;

viadea=# update xx set id2=2; 

ERROR:  function cannot execute on segment because it accesses relation "public.xx2" (functions.c:150)  (seg2 sdw5:20567 pid=29051) (cdbdisp.c:1457)

DETAIL: 

         SQL statement "SELECT id FROM xx2 WHERE id =  $1 "

PL/pgSQL function "myinsert" line 4 at SQL statement

===========Reproduce Test===========

So, please use very simple trigger which is IMMUTABLE.