Re: pgAgent exceptions error - Mailing list 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