Error while using Trigger in Greenplum

Question

If any of you created/tried Triggers on Greenplum, Please help me to resolve this

I have a table where a "id" column has some value, and i want to put a trigger Before insert any data in this table, it should call a function/trigger to check
a) if the data is available for "id" in Parent table or not
b) there is already a row available for given "id"

--Table DDL

create table test_trigger(id integer, details text);

--Trigger Function

create or replace function insert_row_trigger() returns trigger as $$
begin
if exists (SELECT 1 FROM test_trigger WHERE id = NEW.id)
Then
Return NULL;
else
Return NEW;
End If;
End;
$$ language plpgsql;

--Trigger Creation

create trigger my_trigger before insert on test_trigger for each row execute procedure insert_row_trigger();

--Drop Trigger

drop trigger my_trigger on test_trigger

ERROR

ERROR: function cannot execute on segment because it accesses relation "jiodba.test_trigger" (functions.c:151) (seg1 SRDCB0002GPM02:40001 pid=11366) (cdbdisp.c:1477) DETAIL: SQL statement "SELECT exists (SELECT 1 FROM test_trigger WHERE id = $1 )" PL/pgSQL function "insert_row_trigger" line 2 at if

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

ERROR: function cannot execute on segment because it accesses relation "jiodba.test_trigger" (functions.c:151) (seg1 SRDCB0002GPM02:40001 pid=11366) (cdbdisp.c:1477) SQL state: XX000 Detail: SQL statement "SELECT exists (SELECT 1 FROM test_trigger WHERE id = $1 )" PL/pgSQL function "insert_row_trigger" line 2 at if

Please help me on this.
~I also read somewhere that triggers are not supported in GP

0x0FFF 0x0FFF · Accepted Answer · 2014-12-22T09:14:46

Trigger is a function executed on the segment level for each of the input data rows. The issue is that in Greenplum you cannot execute any query from the segment level as it would require each segment to reconnect to the master to execute it separately, which will cause a connection bloat for a big systems. The way to overcome this is for instance this way:

Have an unique index on the Parent table
In a single transaction, execute two statements: first, insert into parent select all the rows that does not exist in parent table. Second, insert into target table all the input rows with the keys just inserted to the parent table.

In general, you will have the same logic, but without trigger

Error while using Trigger in Greenplum

1 Answers