Index: pgagent.sql =================================================================== RCS file: /projects/pgagent/pgagent.sql,v retrieving revision 1.4 retrieving revision 1.5 diff -Lpgagent.sql -Lpgagent.sql -u -w -r1.4 -r1.5 --- pgagent.sql +++ pgagent.sql @@ -11,12 +11,15 @@ CREATE SCHEMA pgagent; COMMENT ON SCHEMA pgagent IS 'pgAgent system tables'; + + CREATE TABLE pgagent.pga_jobagent ( jagpid int4 NOT NULL PRIMARY KEY, logintime timestamptz NOT NULL DEFAULT current_timestamp, station text NOT NULL ) WITHOUT OIDS; -COMMENT ON TABLE pgagent.pga_jobagent IS 'active job agents'; +COMMENT ON TABLE pgagent.pga_jobagent IS 'Active job agents'; + CREATE TABLE pgagent.pga_jobclass ( @@ -26,6 +29,12 @@ CREATE UNIQUE INDEX pga_jobclass_name ON pgagent.pga_jobclass(jclname); COMMENT ON TABLE pgagent.pga_jobclass IS 'Job classification'; +INSERT INTO pgagent.pga_jobclass (jclname) VALUES ('Routine Maintenance'); +INSERT INTO pgagent.pga_jobclass (jclname) VALUES ('Data Import'); +INSERT INTO pgagent.pga_jobclass (jclname) VALUES ('Data Export'); +INSERT INTO pgagent.pga_jobclass (jclname) VALUES ('Data Summarisation'); +INSERT INTO pgagent.pga_jobclass (jclname) VALUES ('Miscellaneous'); + CREATE TABLE pgagent.pga_job ( @@ -44,6 +53,7 @@ COMMENT ON COLUMN pgagent.pga_job.jobagentid IS 'Agent that currently executes this job.'; + CREATE TABLE pgagent.pga_jobstep ( jstid serial NOT NULL PRIMARY KEY, jstjobid int4 NOT NULL REFERENCES pgagent.pga_job (jobid) ON DELETE CASCADE ON UPDATE RESTRICT, @@ -71,16 +81,33 @@ jscenabled bool NOT NULL DEFAULT true, jscstart timestamptz NOT NULL DEFAULT current_timestamp, jscend timestamptz NULL, -jsckind char NOT NULL CHECK (jsckind IN ('n', 's', 'd', 'w', 'm', 'y')) DEFAULT 'n', -- normal, single, daily, weekly, monthly, yearly -jscsched timestamptz NULL, -jsclist interval[] NOT NULL +jscminutes bool[60] NOT NULL DEFAULT '{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}', +jschours bool[24] NOT NULL DEFAULT '{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}', +jscweekdays bool[7] NOT NULL DEFAULT '{f,f,f,f,f,f,f}', +jscmonthdays bool[32] NOT NULL DEFAULT '{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}', +jscmonths bool[12] NOT NULL DEFAULT '{f,f,f,f,f,f,f,f,f,f,f,f}', +CONSTRAINT pga_schedule_jscminutes_size CHECK (array_upper(jscminutess, 1) = 60), +CONSTRAINT pga_schedule_jschours_size CHECK (array_upper(jschours, 1) = 24), +CONSTRAINT pga_schedule_jscweekdays_size CHECK (array_upper(jscweekdays, 1) = 7), +CONSTRAINT pga_schedule_jscmonthdays_size CHECK (array_upper(jscmonthdays, 1) = 32), +CONSTRAINT pga_schedule_jscmonths_size CHECK (array_upper(jscmonths, 1) = 12), ) WITHOUT OIDS; CREATE INDEX pga_jobschedule_jobid ON pgagent.pga_schedule(jscjobid); COMMENT ON TABLE pgagent.pga_schedule IS 'Schedule for a job'; -COMMENT ON COLUMN pgagent.pga_schedule.jsckind IS 'Kind of schedule: normal periodical (jsclist has one entry), single, daily, weekly, monthly, yearly (jsclist contains list of intervals in period)'; +CREATE TABLE pgagent.pga_exception ( +jexid serial NOT NULL PRIMARY KEY, +jexscid int4 NOT NULL REFERENCES pgagent.pga_schedule (jscid) ON DELETE CASCADE ON UPDATE RESTRICT, +jexdate date NULL, +jextime time NULL +) +WITHOUT OIDS; +CREATE INDEX pga_exception_jexscid ON pgagent.pga_exception (jexscid); +CREATE UNIQUE INDEX pga_exception_datetime ON pgagent.pga_exception (jexdate, jextime); +COMMENT ON TABLE pgagent.pga_schedule IS 'Job schedule exceptions'; + CREATE TABLE pgagent.pga_jobprotocol ( @@ -113,83 +140,403 @@ -CREATE OR REPLACE FUNCTION pgagent.pga_next_schedule(timestamptz, timestamptz, timestamptz, char, interval[]) returns timestamptz as -' +CREATE OR REPLACE FUNCTION pgagent.pga_next_schedule(int4, timestamptz, timestamptz, _bool, _bool, _bool, _bool, _bool) RETURNS timestamptz AS ' DECLARE - jscstart ALIAS FOR $1; - jscend ALIAS FOR $2; - jscsched ALIAS FOR $3; - jsckind ALIAS FOR $4; - jsclist ALIAS FOR $5; - - nextrun timestamptz; - period interval; - i int4; + jscid ALIAS FOR $1; + jscstart ALIAS FOR $2; + jscend ALIAS FOR $3; + jscminutes ALIAS FOR $4; + jschours ALIAS FOR $5; + jscweekdays ALIAS FOR $6; + jscmonthdays ALIAS FOR $7; + jscmonths ALIAS FOR $8; + + nextrun timestamptz := ''1970-01-01 00:00:00-00''; + runafter timestamptz := ''1970-01-01 00:00:00-00''; + + bingo bool := FALSE; + gotit bool := FALSE; + foundval bool := FALSE; + daytweak bool := FALSE; + + i int2 := 0; + d int2 := 0; + + nextminute int2 := 0; + nexthour int2 := 0; + nextday int2 := 0; + nextmonth int2 := 0; + nextyear int2 := 0; BEGIN - -- check for validity range of schedule - IF jscstart IS NULL OR jscstart > now() THEN RETURN NULL; END IF; + -- No valid start date has been specified + IF jscstart IS NULL THEN RETURN NULL; END IF; + + -- The schedule is past its end date IF jscend IS NOT NULL AND jscend < now() THEN RETURN NULL; END IF; - IF jsckind = ''n'' THEN - -- schedule type n: every after - IF jscsched IS NULL OR jscsched > now() THEN RETURN NULL; END IF; - nextrun := jscsched - + ( 1 + floor( - (extract(EPOCH FROM now()) - extract(EPOCH FROM jscsched)) - / extract(EPOCH FROM jsclist[1])) - ) * jsclist[1]; - - ELSIF jsckind = ''s'' THEN - nextrun := jscsched; - ELSIF jsckind IN (''y'', ''m'', ''w'', ''d'') THEN - -- other schedule types: - IF jsckind = ''y'' THEN nextrun := date_trunc(''year'', now()); period := ''1 year''::interval; - ELSIF jsckind = ''m'' THEN nextrun := date_trunc(''month'', now()); period := ''1 month''::interval; - ELSIF jsckind = ''d'' THEN nextrun := date_trunc(''day'', now()); period := ''1 day''::interval; - ELSE - -- calculate monday of this week; period starts with monday 00:00:00 - nextrun := date_trunc(''day'', now()) - ((extract(DOW FROM now())-1)::text || '' days''::text)::interval ; - period := ''7 days''::interval; - END IF; - i := 1; - WHILE jsclist[i] IS NOT NULL LOOP - IF nextrun + jsclist[i] > now() THEN - nextrun := nextrun + jsclist[i]; - EXIT; - END IF; - i := i + 1; - END LOOP; - IF nextrun < now() THEN - -- one complete period further - nextrun := nextrun + jsclist[1] + period; + -- Get the time to find the next run after. It will just be the later of + -- now() and the start date for the time being, however, we might want to + -- do more complex things using this value in the future. + IF date_trunc(''MINUTE'', jscstart) > date_trunc(''MINUTE'', now()) THEN + runafter := date_trunc(''MINUTE'', jscstart); + ELSE + runafter := date_trunc(''MINUTE'', now()); + END IF; + + + -- + -- Enter a loop, generating next run timestamps until we find one + -- that falls on the required weekday, and is not matched by an exception + -- + + WHILE bingo = FALSE LOOP + + -- + -- Get the next run year + -- + nextyear := date_part(''YEAR'', runafter); + + -- + -- Get the next run month + -- + nextmonth := date_part(''MONTH'', runafter); + gotit := FALSE; + FOR i IN (nextmonth) .. 12 LOOP + IF jscmonths[i] = TRUE THEN + nextmonth := i; + gotit := TRUE; + foundval := TRUE; + EXIT; + END IF; + END LOOP; + IF gotit = FALSE THEN + FOR i IN 1 .. (nextmonth - 1) LOOP + IF jscmonths[i] = TRUE THEN + nextmonth := i; + + -- Wrap into next year + nextyear := nextyear + 1; + gotit := TRUE; + foundval := TRUE; + EXIT; + END IF; + END LOOP; + END IF; + + -- + -- Get the next run day + -- + -- If the year, or month have incremented, get the lowest day, + -- otherwise look for the next day matching or after today. + IF (nextyear > date_part(''YEAR'', runafter) OR nextmonth > date_part(''MONTH'', runafter)) THEN + nextday := 1; + FOR i IN 1 .. 32 LOOP + IF jscmonthdays[i] = TRUE THEN + nextday := i; + foundval := TRUE; + EXIT; + END IF; + END LOOP; + ELSE + nextday := date_part(''DAY'', runafter); + gotit := FALSE; + FOR i IN nextday .. 32 LOOP + IF jscmonthdays[i] = TRUE THEN + nextday := i; + gotit := TRUE; + foundval := TRUE; + EXIT; + END IF; + END LOOP; + IF gotit = FALSE THEN + FOR i IN 1 .. (nextday - 1) LOOP + IF jscmonthdays[i] = TRUE THEN + nextday := i; + + -- Wrap into next month + IF nextmonth = 12 THEN + nextyear := nextyear + 1; + nextmonth := 1; + ELSE + nextmonth := nextmonth + 1; + END IF; + gotit := TRUE; + foundval := TRUE; + EXIT; + END IF; + END LOOP; + END IF; + END IF; + + -- Was the last day flag selected? + IF nextday = 32 THEN + IF nextmonth = 1 THEN + nextday := 31; + ELSEIF nextmonth = 2 THEN + IF pgagent.pga_is_leap_year(nextyear) = TRUE THEN + nextday := 29; + ELSE + nextday := 28; + END IF; + ELSEIF nextmonth = 3 THEN + nextday := 31; + ELSEIF nextmonth = 4 THEN + nextday := 30; + ELSEIF nextmonth = 5 THEN + nextday := 31; + ELSEIF nextmonth = 6 THEN + nextday := 30; + ELSEIF nextmonth = 7 THEN + nextday := 31; + ELSEIF nextmonth = 8 THEN + nextday := 31; + ELSEIF nextmonth = 9 THEN + nextday := 30; + ELSEIF nextmonth = 10 THEN + nextday := 31; + ELSEIF nextmonth = 11 THEN + nextday := 30; + ELSEIF nextmonth = 12 THEN + nextday := 31; + END IF; + END IF; + + -- + -- Get the next run hour + -- + -- If the year, month or day have incremented, get the lowest hour, + -- otherwise look for the next hour matching or after the current one. + IF (nextyear > date_part(''YEAR'', runafter) OR nextmonth > date_part(''MONTH'', runafter) OR nextday > date_part(''DAY'', runafter) OR daytweak = TRUE) THEN + nexthour := 0; + FOR i IN 1 .. 24 LOOP + IF jschours[i] = TRUE THEN + nexthour := i - 1; + foundval := TRUE; + EXIT; + END IF; + END LOOP; + ELSE + nexthour := date_part(''HOUR'', runafter); + gotit := FALSE; + FOR i IN (nexthour + 1) .. 24 LOOP + IF jschours[i] = TRUE THEN + nexthour := i - 1; + gotit := TRUE; + foundval := TRUE; + EXIT; + END IF; + END LOOP; + IF gotit = FALSE THEN + FOR i IN 1 .. nexthour LOOP + IF jschours[i] = TRUE THEN + nexthour := i - 1; + + -- Wrap into next month + IF (nextmonth = 1 OR nextmonth = 3 OR nextmonth = 5 OR nextmonth = 7 OR nextmonth = 8 OR nextmonth = 10 OR nextmonth = 12) THEN + d = 31; + ELSEIF (nextmonth = 4 OR nextmonth = 6 OR nextmonth = 9 OR nextmonth = 11) THEN + d = 30; + ELSE + IF pgagent.pga_is_leap_year(nextyear) = TRUE THEN + d := 29; + ELSE + d := 28; + END IF; + END IF; + + IF nextday = d THEN + nextday := 1; + IF nextmonth = 12 THEN + nextyear := nextyear + 1; + nextmonth := 1; + ELSE + nextmonth := nextmonth + 1; + END IF; + ELSE + nextday := nextday + 1; + END IF; + + gotit := TRUE; + foundval := TRUE; + EXIT; + END IF; + END LOOP; + END IF; + END IF; + + -- + -- Get the next run minute + -- + -- If the year, month day or hour have incremented, get the lowest minute, + -- otherwise look for the next minute matching or after the current one. + IF (nextyear > date_part(''YEAR'', runafter) OR nextmonth > date_part(''MONTH'', runafter) OR nextday > date_part(''DAY'', runafter) OR nexthour > date_part(''HOUR'', runafter) OR daytweak = TRUE) THEN + nextminute := 0; + FOR i IN 1 .. 60 LOOP + IF jscminutes[i] = TRUE THEN + nextminute := i - 1; + foundval := TRUE; + EXIT; + END IF; + END LOOP; + ELSE + nextminute := date_part(''MINUTE'', runafter); + gotit := FALSE; + FOR i IN (nextminute + 1) .. 60 LOOP + IF jscminutes[i] = TRUE THEN + nextminute := i - 1; + gotit := TRUE; + foundval := TRUE; + EXIT; + END IF; + END LOOP; + IF gotit = FALSE THEN + FOR i IN 1 .. nextminute LOOP + IF jscminutes[i] = TRUE THEN + nextminute := i - 1; + + -- Wrap into next hour + IF (nextmonth = 1 OR nextmonth = 3 OR nextmonth = 5 OR nextmonth = 7 OR nextmonth = 8 OR nextmonth = 10 OR nextmonth = 12) THEN + d = 31; + ELSEIF (nextmonth = 4 OR nextmonth = 6 OR nextmonth = 9 OR nextmonth = 11) THEN + d = 30; + ELSE + IF pgagent.pga_is_leap_year(nextyear) = TRUE THEN + d := 29; + ELSE + d := 28; + END IF; + END IF; + + IF nexthour = 23 THEN + nexthour = 0; + IF nextday = d THEN + nextday := 1; + IF nextmonth = 12 THEN + nextyear := nextyear + 1; + nextmonth := 1; + ELSE + nextmonth := nextmonth + 1; + END IF; + ELSE + nextday := nextday + 1; END IF; ELSE - RAISE EXCEPTION ''pgagent.pga_next_schedule: unknown schedule kind''; + nexthour := nexthour + 1; + END IF; + + gotit := TRUE; + foundval := TRUE; + EXIT; + END IF; + END LOOP; END IF; + END IF; + + -- Build the result, and check it is not the same as runafter - this may + -- happen if all array entries are set to false. In this case, add a minute. + + nextrun := (nextyear::varchar || ''-''::varchar || nextmonth::varchar || ''-'' || nextday::varchar || '' '' || nexthour::varchar || '':'' || nextminute::varchar)::timestamptz; + + IF nextrun = runafter AND foundval = FALSE THEN + nextrun := nextrun + INTERVAL ''1 Minute''; + END IF; + + -- If the result is past the end date, exit. + IF nextrun > jscend THEN + RETURN NULL; + END IF; + + -- Check to ensure that the nextrun time is actually still valid. Its + -- possible that wrapped values may have carried the nextrun onto an + -- invalid time or date. + IF ((jscminutes = ''{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'' OR jscminutes[date_part(''MINUTE'', nextrun) + 1] = TRUE) AND + (jschours = ''{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'' OR jschours[date_part(''HOUR'', nextrun) + 1] = TRUE) AND + (jscmonthdays = ''{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}'' OR jscmonthdays[date_part(''DAY'', nextrun)] = TRUE OR + (jscmonthdays = ''{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,t}'' AND + ((date_part(''MONTH'', nextrun) IN (1,3,5,7,8,10,12) AND date_part(''DAY'', nextrun) = 31) OR + (date_part(''MONTH'', nextrun) IN (4,6,9,11) AND date_part(''DAY'', nextrun) = 30) OR + (date_part(''MONTH'', nextrun) = 2 AND ((pgagent.pga_is_leap_year(date_part(''DAY'', nextrun)::int2) AND date_part(''DAY'', nextrun) = 29) OR date_part(''DAY'', nextrun) = 28))))) AND + (jscmonths = ''{f,f,f,f,f,f,f,f,f,f,f,f}'' OR jscmonths[date_part(''MONTH'', nextrun)] = TRUE)) THEN + + + -- Now, check to see if the nextrun time found is a) on an acceptable + -- weekday, and b) not matched by an exception. If not, set + -- runafter = nextrun and try again. + + -- Check for a wildcard weekday + gotit := FALSE; + FOR i IN 1 .. 7 LOOP + IF jscweekdays[i] = TRUE THEN + gotit := TRUE; + EXIT; + END IF; + END LOOP; + + -- OK, is the correct weekday selected, or a wildcard? + IF (jscweekdays[date_part(''DOW'', nextrun)] = TRUE OR gotit = FALSE) THEN + + -- Check for exceptions + SELECT INTO d jexid FROM pgagent.pga_exception WHERE jexscid = jscid AND ((jexdate = nextrun::date AND jextime = nextrun::time) OR (jexdate = nextrun::date AND jextime IS NULL) OR (jexdate IS NULL AND jextime = nextrun::time)); + IF FOUND THEN + -- Nuts - found an exception. Increment the time and try again + runafter := nextrun + INTERVAL ''1 Minute''; + bingo := FALSE; + ELSE + bingo := TRUE; + END IF; + ELSE + -- We''re on the wrong week day - increment a day and try again. + runafter := nextrun + INTERVAL ''1 Day''; + bingo := FALSE; + daytweak := TRUE; + END IF; + + ELSE + runafter := nextrun + INTERVAL ''1 Minute''; + bingo := FALSE; + END IF; + + END LOOP; + RETURN nextrun; END; -' -language 'plpgsql'; +' LANGUAGE 'plpgsql' VOLATILE; +COMMENT ON FUNCTION pgagent.pga_next_schedule(int4, timestamptz, timestamptz, _bool, _bool, _bool, _bool, _bool) IS 'Calculates the next runtime for a given schedule'; + +-- +-- Test code +-- +-- SELECT pgagent.pga_next_schedule( +-- 2, -- Schedule ID +-- '2005-01-01 00:00:00', -- Start date +-- '2006-10-01 00:00:00', -- End date +-- '{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}', -- Minutes +-- '{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}', -- Hours +-- '{f,f,f,f,f,f,f}', -- Weekdays +-- '{f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f,f}', -- Monthdays +-- '{f,f,f,f,f,f,f,f,f,f,f,f}' -- Months +-- ); -CREATE OR REPLACE FUNCTION pgagent.pga_job_trigger() RETURNS trigger AS ' + + +CREATE OR REPLACE FUNCTION pgagent.pga_is_leap_year(int2) RETURNS bool AS ' BEGIN - IF NEW.jobenabled THEN - IF NEW.jobnextrun IS NULL THEN - SELECT INTO NEW.jobnextrun - MIN(pgagent.pga_next_schedule(jscstart, jscend, jscsched, jsckind, jsclist)) - FROM pgagent.pga_schedule - WHERE jscenabled AND jscjobid=OLD.jobid; + IF $1 % 4 != 0 THEN + RETURN FALSE; END IF; - ELSE - NEW.jobnextrun := NULL; + + IF $1 % 100 != 0 THEN + RETURN TRUE; END IF; - RETURN NEW; + + RETURN $1 % 400 = 0; END; -' LANGUAGE 'plpgsql'; +' LANGUAGE 'plpgsql' IMMUTABLE; +COMMENT ON FUNCTION pgagent.pga_is_leap_year(int2) IS 'Returns TRUE is $1 is a leap year'; CREATE TRIGGER pga_job_trigger BEFORE UPDATE @@ -197,43 +544,67 @@ EXECUTE PROCEDURE pgagent.pga_job_trigger(); + CREATE OR REPLACE FUNCTION pgagent.pga_schedule_trigger() RETURNS trigger AS ' BEGIN IF TG_OP = ''DELETE'' THEN -- update pga_job from remaining schedules -- the actual calculation of jobnextrun will be performed in the trigger - UPDATE pgadmin.pga_job + UPDATE pgagent.pga_job SET jobnextrun = NULL WHERE jobenabled AND jobid=OLD.jscjobid; RETURN OLD; ELSE - UPDATE pgadmin.pga_job + UPDATE pgagent.pga_job SET jobnextrun = NULL WHERE jobenabled AND jobid=NEW.jscjobid; RETURN NEW; END IF; END; ' LANGUAGE 'plpgsql'; +COMMENT ON FUNCTION pgagent.pga_schedule_trigger() IS 'Update the job\'s next run time whenever a schedule changes'; CREATE TRIGGER pga_schedule_trigger AFTER INSERT OR UPDATE OR DELETE ON pgagent.pga_schedule FOR EACH ROW EXECUTE PROCEDURE pgagent.pga_schedule_trigger(); +COMMENT ON TRIGGER pga_schedule_trigger ON pgagent.pga_schedule IS 'Update the job\'s next run time whenever a schedule changes'; -/* -delete from pgagent.pga_jobclass; +CREATE OR REPLACE FUNCTION pgagent.pga_exception_trigger() RETURNS "trigger" AS ' +DECLARE + + jobid int4 := 0; + +BEGIN -insert into pgagent.pga_jobclass (jclname) values ('misc'); + IF TG_OP = ''DELETE'' THEN + + SELECT INTO 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=jobid; + RETURN OLD; + ELSE - delete from pgagent.pga_schedule; - select * from pgagent.pga_Schedule; + SELECT INTO jobid jscjobid FROM pgagent.pga_schedule WHERE jscid = NEW.jexscid; - insert into pgagent.pga_schedule(jscjoboid,jscname, jscdesc, jscenabled, jscstart, jscsched, jsclist) - values (xxxxx, '10min', 'arbitrary 10min cycle', true, '2002-01-01', '2002-01-01', ARRAY['10 min'::interval] ); + UPDATE pgagent.pga_job + SET jobnextrun = NULL + WHERE jobenabled AND jobid=jobid; + RETURN NEW; + END IF; +END; +' LANGUAGE 'plpgsql' VOLATILE; +COMMENT ON FUNCTION pgagent.pga_schedule_trigger() IS 'Update the job\'s next run time whenever an exception changes'; --- update pgagent.pga_schedule set jscenabled=jscenabled -*/ +CREATE TRIGGER pga_exception_trigger AFTER INSERT OR UPDATE OR DELETE + ON pgagent.pga_exception FOR EACH ROW + EXECUTE PROCEDURE pgagent.pga_exception_trigger(); +COMMENT ON TRIGGER pga_exception_trigger ON pgagent.pga_exception IS 'Update the job\'s next run time whenever an exception changes'; \ No newline at end of file