Обсуждение: pgagent on Greenplum
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
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';
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';
On Mon, May 13, 2013 at 4:55 PM, Kurics János <kurics40@freemail.hu> wrote: > > 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. If that's the case, then it'll never work I'm afraid. Use of triggers is fundamental to the way pgAgent works. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company