From: | Bartosz Dmytrak <bdmytrak(at)gmail(dot)com> |
---|---|
To: | PgAdmin Support <pgadmin-support(at)postgresql(dot)org> |
Subject: | pgAgent exceptions error |
Date: | 2012-09-20 08:11:54 |
Message-ID: | CAD8_UcaLmi4F7vnm4PU3PSMOmJHQTO4z49x9mrzGFKLH4OkDQQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Александр Ющенко | 2012-09-20 09:02:59 | Pgadmin bug. |
Previous Message | Dave Page | 2012-09-19 15:59:52 | Re: Error: CGContextRestoreGState: invalid context 0x0 |