Hello all, Could someone make pgagent on Greenplum work? Env info:Linux gp 2.6.32-358.2.1.el6.x86_64 #1 SMP Wed Mar 13 00:26:49 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux pgAgent-3.3.0-Source.tar.gz greenplum-db-4.2.2.4-build-1-CE-RHEL5-x86_64.bin pgadmin3-1.16.1.zip I could install, start daemon and see it in pgAdmin client but I can not schedule a job. Error message:function cannot execute on segment because it issues a non-SELECT statement (fucntion.c133) (seg0 localhost.localdomain:40000 pid=2780 (cdbisp.c1475) DETAIL SQL statement "UPDATE pgagent.pga_job SET jobnextrun = NULL WHERE jobenalbed AND jobid=$1"
PL/pgSQL function "pga_schedule_trigger" line 10 at SQL statement So GP doesn't support triggers however I could create them. Does someone has anyidea how to transform it to GP compatible one? CREATE OR REPLACE FUNCTION pgagent.pga_exception_trigger() RETURNS "trigger" AS ' DECLARE v_jobid int4 := 0; BEGIN IF TG_OP = ''DELETE'' THEN SELECT INTO v_jobid jscjobid FROM pgagent.pga_schedule WHERE jscid = OLD.jexscid; -- update pga_job from remaining schedules -- the actual calculation of jobnextrun will be performed in the trigger UPDATE pgagent.pga_job SET jobnextrun = NULL WHERE jobenabled AND jobid = v_jobid; RETURN OLD; ELSE SELECT INTO v_jobid jscjobid FROM pgagent.pga_schedule WHERE jscid = NEW.jexscid; UPDATE pgagent.pga_job SET jobnextrun = NULL WHERE jobenabled AND jobid = v_jobid; RETURN NEW; END IF; END; ' LANGUAGE 'plpgsql' VOLATILE; COMMENT ON FUNCTION pgagent.pga_exception_trigger() IS 'Update the job''s next run time whenever an exception changes';
