pgagent on Greenplum

From: Kurics János <kurics40(at)freemail(dot)hu>
To: pgadmin-support(at)postgresql(dot)org
Subject: pgagent on Greenplum
Date: 2013-05-13 15:55:01
Message-ID: freemail.20130513175501.71834.2@xmldata02.freemail.hu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support

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&#39;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 &#39;
DECLARE
v_jobid int4 := 0;

BEGIN
IF TG_OP = &#39;&#39;DELETE&#39;&#39; 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;
&#39; LANGUAGE &#39;plpgsql&#39; VOLATILE;
COMMENT ON FUNCTION pgagent.pga_exception_trigger() IS &#39;Update the job&#39;&#39;s next run time whenever an exception changes&#39;;

Responses

Browse pgadmin-support by date

  From Date Subject
Next Message Dave Page 2013-05-13 16:06:35 Re: pgagent on Greenplum
Previous Message Bartosz Dmytrak 2013-05-13 12:22:12 Re: Grants on sequences via Grant Wizard