Re: pgAgent exceptions error

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(at)gmail(dot)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