Log Message:
-----------
Updated pgAgent schema with cron-style scheduling.
Modified Files:
--------------
pgagent:
pgagent.sql (r1.4 -> r1.5)
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 <jsclist[1]> after <jscsched>
- 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
