Re: pgAgent exceptions error
От | Dave Page |
---|---|
Тема | Re: pgAgent exceptions error |
Дата | |
Msg-id | CA+OCxow=jkLnu-UwH+KZL82CmK59G0U1OT+0k1tyCmh35SLjxA@mail.gmail.com обсуждение исходный текст |
Ответ на | pgAgent exceptions error (Bartosz Dmytrak <bdmytrak@gmail.com>) |
Список | pgadmin-support |
Thanks - I've committed a patch to fix this based on your suggestion. Regards, Dave. On Thu, Sep 20, 2012 at 4:11 AM, Bartosz Dmytrak <bdmytrak@gmail.com> wrote: > Hi all, > I've tried to create pgAgent exception using pgAdmin, unfortunately without > success. > > Logs: > 2012-09-20 09:49:23 STATUS : Retrieving details on pgAgent job ETL tasks... > (0.02 secs) > 2012-09-20 09:49:25 STATUS : Retrieving details on pgAgent schedule EveryDay > at 5:59... > 2012-09-20 09:49:25 STATUS : Retrieving details on pgAgent schedule EveryDay > at 5:59... (0.00 secs) > 2012-09-20 09:49:37 QUERY : Void query (host:5432): BEGIN TRANSACTION > 2012-09-20 09:49:37 QUERY : Void query (host:5432): INSERT INTO > pgagent.pga_exception (jexscid, jexdate, jextime) > VALUES (33, '2012-09-21', null); > INSERT INTO pgagent.pga_exception (jexscid, jexdate, jextime) > VALUES (33, '2012-09-22', null); > INSERT INTO pgagent.pga_exception (jexscid, jexdate, jextime) > VALUES (33, '2012-09-23', null); > INSERT INTO pgagent.pga_exception (jexscid, jexdate, jextime) > VALUES (33, '2012-09-24', null); > > 2012-09-20 09:49:37 ERROR : ERROR: column reference "jobid" is ambiguous > LINE 3: WHERE jobenabled AND jobid=jobid > ^ > DETAIL: It could refer to either a PL/pgSQL variable or a table column. > QUERY: UPDATE pgagent.pga_job > > SET jobnextrun = NULL > > WHERE jobenabled AND jobid=jobid > CONTEXT: PL/pgSQL function pgagent.pga_exception_trigger() line 22 at SQL > statement > 2012-09-20 09:49:38 QUERY : Void query (host:5432): ROLLBACK TRANSACTION > > > This shows error in pgagent.pga_exception_trigger() function > Here is a hotfix I applied to my server: > > -- Function: pgagent.pga_exception_trigger() > > -- DROP FUNCTION pgagent.pga_exception_trigger(); > > CREATE OR REPLACE FUNCTION pgagent.pga_exception_trigger() > RETURNS trigger AS > $BODY$ > 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; > $BODY$ > LANGUAGE plpgsql VOLATILE > COST 100; > ALTER FUNCTION pgagent.pga_exception_trigger() > OWNER TO postgres; > COMMENT ON FUNCTION pgagent.pga_exception_trigger() IS 'Update the job''s > next run time whenever an exception changes'; > > Hope this helps. > env: PgAdmin 1.16, pgAgent 3.0.0-win32 > > > > Regards, > Bartek -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
В списке pgadmin-support по дате отправления: