public inbox for [email protected]help / color / mirror / Atom feed
PgAgent leap year problem 5+ messages / 3 participants [nested] [flat]
* PgAgent leap year problem @ 2020-03-02 11:56 Mehmet Emin KARAKAŞ <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Mehmet Emin KARAKAŞ @ 2020-03-02 11:56 UTC (permalink / raw) To: pgadmin-hackers Hi, Scheduled tasks set as the last day of February this year did not work. In your schedule function have a small bug. Leap year function get year parameter, but your code is sending day as a parameter. Fixing patch attached in this mail. Best Regards... -- MEHMET EMİN KARAKAŞ PostgreSQL DBA Turksat Satellite Communication and Cable TV Operations Konya Yolu 40. Km. 06839 Golbasi / ANKARA - TÜRKİYE Tel : +90 312 615 32 73 Faks : +90 312 615 32 77 Attachments: [text/x-patch] pgagent_leap_year_problem.diff (976B, 3-pgagent_leap_year_problem.diff) download | inline diff: diff --git a/sql/pgagent.sql b/sql/pgagent.sql index 7eb42ba..7ff552b 100644 --- a/sql/pgagent.sql +++ b/sql/pgagent.sql @@ -476,7 +476,7 @@ BEGIN (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 + (date_part(''MONTH'', nextrun) = 2 AND ((pgagent.pga_is_leap_year(date_part(''YEAR'', 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PgAgent leap year problem @ 2020-03-04 05:23 Neel Patel <[email protected]> parent: Mehmet Emin KARAKAŞ <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Neel Patel @ 2020-03-04 05:23 UTC (permalink / raw) To: Mehmet Emin KARAKAŞ <[email protected]>; Dave Page <[email protected]>; Ashesh Vashi <[email protected]>; +Cc: pgadmin-hackers Hi Mehmet, It was a bug in pgAgent. Thank you for sharing the patch. I have reviewed and tested, it is working fine. Dave/Ashesh:- Can we commit this patch ? Thanks, Neel Patel On Mon, Mar 2, 2020 at 5:26 PM Mehmet Emin KARAKAŞ <[email protected]> wrote: > Hi, > > Scheduled tasks set as the last day of February this year did not work. In > your schedule function have a small bug. Leap year function get year > parameter, but your code is sending day as a parameter. Fixing patch > attached in this mail. > > Best Regards... > > -- > MEHMET EMİN KARAKAŞ > PostgreSQL DBA > > Turksat Satellite Communication and Cable TV Operations > Konya Yolu 40. Km. 06839 Golbasi / ANKARA - TÜRKİYE > Tel : +90 312 615 32 73 > Faks : +90 312 615 32 77 > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PgAgent leap year problem @ 2020-03-04 06:52 Ashesh Vashi <[email protected]> parent: Neel Patel <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Ashesh Vashi @ 2020-03-04 06:52 UTC (permalink / raw) To: Neel Patel <[email protected]>; +Cc: Mehmet Emin KARAKAŞ <[email protected]>; Dave Page <[email protected]>; pgadmin-hackers On Wed, Mar 4, 2020 at 10:53 AM Neel Patel <[email protected]> wrote: > Hi Mehmet, > > It was a bug in pgAgent. Thank you for sharing the patch. I have reviewed > and tested, it is working fine. > > Dave/Ashesh:- Can we commit this patch ? > Mehmet, Thanks for your patch. Changes look good except it does not take care of the upgrade scenarios. Hence - updated the patch for the same. Neel, Would you please review the updated patch with the upgrade scenarios? -- Thanks, Ashesh > > Thanks, > Neel Patel > > On Mon, Mar 2, 2020 at 5:26 PM Mehmet Emin KARAKAŞ <[email protected]> > wrote: > >> Hi, >> >> Scheduled tasks set as the last day of February this year did not work. >> In your schedule function have a small bug. Leap year function get year >> parameter, but your code is sending day as a parameter. Fixing patch >> attached in this mail. >> >> Best Regards... >> >> -- >> MEHMET EMİN KARAKAŞ >> PostgreSQL DBA >> >> Turksat Satellite Communication and Cable TV Operations >> Konya Yolu 40. Km. 06839 Golbasi / ANKARA - TÜRKİYE >> Tel : +90 312 615 32 73 >> Faks : +90 312 615 32 77 >> > Attachments: [application/octet-stream] pgagent_leap_year.patch (49.8K, 3-pgagent_leap_year.patch) download | inline diff: diff --git a/CMakeLists.txt b/CMakeLists.txt index 6f1eaa0..04f53cd 100755 --- a/CMakeLists.txt +++ b/CMakeLists.txt @@ -66,7 +66,7 @@ SET(VERSION "4.0.0") # CPack stuff SET(CPACK_PACKAGE_VERSION_MAJOR 4) -SET(CPACK_PACKAGE_VERSION_MINOR 0) +SET(CPACK_PACKAGE_VERSION_MINOR 1) SET(CPACK_PACKAGE_VERSION_PATCH 0) SET(CPACK_PACKAGE_NAME "pgAgent") SET(CPACK_PACKAGE_DESCRIPTION_SUMMARY "pgAgent is a job scheduling engine for PostgreSQL") diff --git a/sql/pgagent--3.4--4.1.sql b/sql/pgagent--3.4--4.1.sql new file mode 100644 index 0000000..94859fc --- /dev/null +++ b/sql/pgagent--3.4--4.1.sql @@ -0,0 +1,395 @@ +/* +// pgAgent - PostgreSQL Tools +// +// Copyright (C) 2002 - 2018 The pgAdmin Development Team +// This software is released under the PostgreSQL Licence +// +// pgagent--3.4--4.0.sql - Upgrade the pgAgent schema to 4.0 +// +*/ + +\echo Use "CREATE EXTENSION pgagent UPDATE" to load this file. \quit + +CREATE OR REPLACE FUNCTION pgagent.pgagent_schema_version() RETURNS int2 AS ' +BEGIN + -- RETURNS PGAGENT MAJOR VERSION + -- WE WILL CHANGE THE MAJOR VERSION, ONLY IF THERE IS A SCHEMA CHANGE + RETURN 4; +END; +' LANGUAGE 'plpgsql' VOLATILE; + +CREATE OR REPLACE FUNCTION pgagent.pga_next_schedule(int4, timestamptz, timestamptz, _bool, _bool, _bool, _bool, _bool) RETURNS timestamptz AS ' +DECLARE + 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 timestamp := ''1970-01-01 00:00:00-00''; + runafter timestamp := ''1970-01-01 00:00:00-00''; + + bingo bool := FALSE; + gotit bool := FALSE; + foundval bool := FALSE; + daytweak bool := FALSE; + minutetweak bool := FALSE; + + i int2 := 0; + d int2 := 0; + + nextminute int2 := 0; + nexthour int2 := 0; + nextday int2 := 0; + nextmonth int2 := 0; + nextyear int2 := 0; + + +BEGIN + -- 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; + + -- Get the time to find the next run after. It will just be the later of + -- now() + 1m 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() + ''1 Minute''::interval)) THEN + runafter := date_trunc(''MINUTE'', jscstart); + ELSE + runafter := date_trunc(''MINUTE'', (now() + ''1 Minute''::interval)); + 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; + ELSIF nextmonth = 2 THEN + IF pgagent.pga_is_leap_year(nextyear) = TRUE THEN + nextday := 29; + ELSE + nextday := 28; + END IF; + ELSIF nextmonth = 3 THEN + nextday := 31; + ELSIF nextmonth = 4 THEN + nextday := 30; + ELSIF nextmonth = 5 THEN + nextday := 31; + ELSIF nextmonth = 6 THEN + nextday := 30; + ELSIF nextmonth = 7 THEN + nextday := 31; + ELSIF nextmonth = 8 THEN + nextday := 31; + ELSIF nextmonth = 9 THEN + nextday := 30; + ELSIF nextmonth = 10 THEN + nextday := 31; + ELSIF nextmonth = 11 THEN + nextday := 30; + ELSIF 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; + ELSIF (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; + IF minutetweak = TRUE THEN + d := 1; + ELSE + d := date_part(''YEAR'', runafter)::int2; + END IF; + FOR i IN d .. 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; + ELSIF (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 + 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(''YEAR'', 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) + 1] = 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; + minutetweak := TRUE; + daytweak := 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; + minutetweak := FALSE; + daytweak := TRUE; + END IF; + + ELSE + runafter := nextrun + INTERVAL ''1 Minute''; + bingo := FALSE; + minutetweak := TRUE; + daytweak := FALSE; + END IF; + + END LOOP; + + RETURN nextrun; +END; +' 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'; diff --git a/sql/pgagent--4.0--4.1.sql b/sql/pgagent--4.0--4.1.sql new file mode 100644 index 0000000..7d30d24 --- /dev/null +++ b/sql/pgagent--4.0--4.1.sql @@ -0,0 +1,387 @@ +/* +// pgAgent - PostgreSQL Tools +// +// Copyright (C) 2002 - 2018 The pgAdmin Development Team +// This software is released under the PostgreSQL Licence +// +// pgagent--3.4--4.0.sql - Upgrade the pgAgent schema to 4.0 +// +*/ + +\echo Use "CREATE EXTENSION pgagent UPDATE" to load this file. \quit + +CREATE OR REPLACE FUNCTION pgagent.pga_next_schedule(int4, timestamptz, timestamptz, _bool, _bool, _bool, _bool, _bool) RETURNS timestamptz AS ' +DECLARE + 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 timestamp := ''1970-01-01 00:00:00-00''; + runafter timestamp := ''1970-01-01 00:00:00-00''; + + bingo bool := FALSE; + gotit bool := FALSE; + foundval bool := FALSE; + daytweak bool := FALSE; + minutetweak bool := FALSE; + + i int2 := 0; + d int2 := 0; + + nextminute int2 := 0; + nexthour int2 := 0; + nextday int2 := 0; + nextmonth int2 := 0; + nextyear int2 := 0; + + +BEGIN + -- 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; + + -- Get the time to find the next run after. It will just be the later of + -- now() + 1m 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() + ''1 Minute''::interval)) THEN + runafter := date_trunc(''MINUTE'', jscstart); + ELSE + runafter := date_trunc(''MINUTE'', (now() + ''1 Minute''::interval)); + 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; + ELSIF nextmonth = 2 THEN + IF pgagent.pga_is_leap_year(nextyear) = TRUE THEN + nextday := 29; + ELSE + nextday := 28; + END IF; + ELSIF nextmonth = 3 THEN + nextday := 31; + ELSIF nextmonth = 4 THEN + nextday := 30; + ELSIF nextmonth = 5 THEN + nextday := 31; + ELSIF nextmonth = 6 THEN + nextday := 30; + ELSIF nextmonth = 7 THEN + nextday := 31; + ELSIF nextmonth = 8 THEN + nextday := 31; + ELSIF nextmonth = 9 THEN + nextday := 30; + ELSIF nextmonth = 10 THEN + nextday := 31; + ELSIF nextmonth = 11 THEN + nextday := 30; + ELSIF 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; + ELSIF (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; + IF minutetweak = TRUE THEN + d := 1; + ELSE + d := date_part(''YEAR'', runafter)::int2; + END IF; + FOR i IN d .. 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; + ELSIF (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 + 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(''YEAR'', 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) + 1] = 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; + minutetweak := TRUE; + daytweak := 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; + minutetweak := FALSE; + daytweak := TRUE; + END IF; + + ELSE + runafter := nextrun + INTERVAL ''1 Minute''; + bingo := FALSE; + minutetweak := TRUE; + daytweak := FALSE; + END IF; + + END LOOP; + + RETURN nextrun; +END; +' 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'; diff --git a/sql/pgagent--unpackaged--4.1.sql b/sql/pgagent--unpackaged--4.1.sql new file mode 100644 index 0000000..6dbe042 --- /dev/null +++ b/sql/pgagent--unpackaged--4.1.sql @@ -0,0 +1,428 @@ +/* +// pgAgent - PostgreSQL Tools +// +// Copyright (C) 2002 - 2018 The pgAdmin Development Team +// This software is released under the PostgreSQL Licence +// +// pgagent--unpackaged--4.0.sql - Convert pgAgent existing tables and functions to an extension +// +*/ + +\echo Use "CREATE EXTENSION pgagent FROM unpackaged" to load this file. \quit + +ALTER EXTENSION pgagent ADD TABLE pgagent.pga_jobagent; +ALTER EXTENSION pgagent ADD TABLE pgagent.pga_jobclass; +ALTER EXTENSION pgagent ADD TABLE pgagent.pga_job; +ALTER EXTENSION pgagent ADD TABLE pgagent.pga_jobstep; +ALTER EXTENSION pgagent ADD TABLE pgagent.pga_schedule; +ALTER EXTENSION pgagent ADD TABLE pgagent.pga_exception; +ALTER EXTENSION pgagent ADD TABLE pgagent.pga_joblog; +ALTER EXTENSION pgagent ADD TABLE pgagent.pga_jobsteplog; + +ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_exception_jexid_seq; +ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_job_jobid_seq; +ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_jobclass_jclid_seq; +ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_joblog_jlgid_seq; +ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_jobstep_jstid_seq; +ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_jobsteplog_jslid_seq; +ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_schedule_jscid_seq; + +ALTER EXTENSION pgagent ADD FUNCTION pgagent.pgagent_schema_version(); +ALTER EXTENSION pgagent ADD FUNCTION pgagent.pga_next_schedule(int4, timestamptz, timestamptz, _bool, _bool, _bool, _bool, _bool); +ALTER EXTENSION pgagent ADD FUNCTION pgagent.pga_is_leap_year(int2); +ALTER EXTENSION pgagent ADD FUNCTION pgagent.pga_job_trigger(); +ALTER EXTENSION pgagent ADD FUNCTION pgagent.pga_schedule_trigger(); +ALTER EXTENSION pgagent ADD FUNCTION pgagent.pga_exception_trigger(); + +SELECT pg_catalog.pg_extension_config_dump('pga_jobagent', ''); +SELECT pg_catalog.pg_extension_config_dump('pga_jobclass', $$WHERE jclname NOT IN ('Routine Maintenance', 'Data Import', 'Data Export', 'Data Summarisation', 'Miscellaneous')$$); +SELECT pg_catalog.pg_extension_config_dump('pga_job', ''); +SELECT pg_catalog.pg_extension_config_dump('pga_jobstep', ''); +SELECT pg_catalog.pg_extension_config_dump('pga_schedule', ''); +SELECT pg_catalog.pg_extension_config_dump('pga_exception', ''); +SELECT pg_catalog.pg_extension_config_dump('pga_joblog', ''); +SELECT pg_catalog.pg_extension_config_dump('pga_jobsteplog', ''); + +CREATE OR REPLACE FUNCTION pgagent.pgagent_schema_version() RETURNS int2 AS ' +BEGIN + -- RETURNS PGAGENT MAJOR VERSION + -- WE WILL CHANGE THE MAJOR VERSION, ONLY IF THERE IS A SCHEMA CHANGE + RETURN 4; +END; +' LANGUAGE 'plpgsql' VOLATILE; + +CREATE OR REPLACE FUNCTION pgagent.pga_next_schedule(int4, timestamptz, timestamptz, _bool, _bool, _bool, _bool, _bool) RETURNS timestamptz AS ' +DECLARE + 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 timestamp := ''1970-01-01 00:00:00-00''; + runafter timestamp := ''1970-01-01 00:00:00-00''; + + bingo bool := FALSE; + gotit bool := FALSE; + foundval bool := FALSE; + daytweak bool := FALSE; + minutetweak bool := FALSE; + + i int2 := 0; + d int2 := 0; + + nextminute int2 := 0; + nexthour int2 := 0; + nextday int2 := 0; + nextmonth int2 := 0; + nextyear int2 := 0; + + +BEGIN + -- 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; + + -- Get the time to find the next run after. It will just be the later of + -- now() + 1m 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() + ''1 Minute''::interval)) THEN + runafter := date_trunc(''MINUTE'', jscstart); + ELSE + runafter := date_trunc(''MINUTE'', (now() + ''1 Minute''::interval)); + 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; + ELSIF nextmonth = 2 THEN + IF pgagent.pga_is_leap_year(nextyear) = TRUE THEN + nextday := 29; + ELSE + nextday := 28; + END IF; + ELSIF nextmonth = 3 THEN + nextday := 31; + ELSIF nextmonth = 4 THEN + nextday := 30; + ELSIF nextmonth = 5 THEN + nextday := 31; + ELSIF nextmonth = 6 THEN + nextday := 30; + ELSIF nextmonth = 7 THEN + nextday := 31; + ELSIF nextmonth = 8 THEN + nextday := 31; + ELSIF nextmonth = 9 THEN + nextday := 30; + ELSIF nextmonth = 10 THEN + nextday := 31; + ELSIF nextmonth = 11 THEN + nextday := 30; + ELSIF 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; + ELSIF (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; + IF minutetweak = TRUE THEN + d := 1; + ELSE + d := date_part(''YEAR'', runafter)::int2; + END IF; + FOR i IN d .. 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; + ELSIF (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 + 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(''YEAR'', 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) + 1] = 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; + minutetweak := TRUE; + daytweak := 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; + minutetweak := FALSE; + daytweak := TRUE; + END IF; + + ELSE + runafter := nextrun + INTERVAL ''1 Minute''; + bingo := FALSE; + minutetweak := TRUE; + daytweak := FALSE; + END IF; + + END LOOP; + + RETURN nextrun; +END; +' 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'; diff --git a/sql/pgagent.sql b/sql/pgagent.sql index 7eb42ba..7ff552b 100644 --- a/sql/pgagent.sql +++ b/sql/pgagent.sql @@ -476,7 +476,7 @@ BEGIN (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 + (date_part(''MONTH'', nextrun) = 2 AND ((pgagent.pga_is_leap_year(date_part(''YEAR'', 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PgAgent leap year problem @ 2020-03-04 07:06 Neel Patel <[email protected]> parent: Ashesh Vashi <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Neel Patel @ 2020-03-04 07:06 UTC (permalink / raw) To: Ashesh Vashi <[email protected]>; +Cc: Mehmet Emin KARAKAŞ <[email protected]>; Dave Page <[email protected]>; pgadmin-hackers Hi Ashesh, I have reviewed it and looks good. I have updated the sql version in header of each sql file. Thanks, Neel Patel On Wed, Mar 4, 2020 at 12:22 PM Ashesh Vashi <[email protected]> wrote: > On Wed, Mar 4, 2020 at 10:53 AM Neel Patel <[email protected]> > wrote: > >> Hi Mehmet, >> >> It was a bug in pgAgent. Thank you for sharing the patch. I have reviewed >> and tested, it is working fine. >> >> Dave/Ashesh:- Can we commit this patch ? >> > Mehmet, > > Thanks for your patch. > Changes look good except it does not take care of the upgrade scenarios. > > Hence - updated the patch for the same. > > Neel, > > Would you please review the updated patch with the upgrade scenarios? > > > -- Thanks, Ashesh > >> >> Thanks, >> Neel Patel >> >> On Mon, Mar 2, 2020 at 5:26 PM Mehmet Emin KARAKAŞ <[email protected]> >> wrote: >> >>> Hi, >>> >>> Scheduled tasks set as the last day of February this year did not work. >>> In your schedule function have a small bug. Leap year function get year >>> parameter, but your code is sending day as a parameter. Fixing patch >>> attached in this mail. >>> >>> Best Regards... >>> >>> -- >>> MEHMET EMİN KARAKAŞ >>> PostgreSQL DBA >>> >>> Turksat Satellite Communication and Cable TV Operations >>> Konya Yolu 40. Km. 06839 Golbasi / ANKARA - TÜRKİYE >>> Tel : +90 312 615 32 73 >>> Faks : +90 312 615 32 77 >>> >> Attachments: [application/octet-stream] pgagent_leap_year_v2.patch (49.8K, 3-pgagent_leap_year_v2.patch) download | inline diff: diff --git a/CMakeLists.txt b/CMakeLists.txt index 6f1eaa0..04f53cd 100755 --- a/CMakeLists.txt +++ b/CMakeLists.txt @@ -66,7 +66,7 @@ SET(VERSION "4.0.0") # CPack stuff SET(CPACK_PACKAGE_VERSION_MAJOR 4) -SET(CPACK_PACKAGE_VERSION_MINOR 0) +SET(CPACK_PACKAGE_VERSION_MINOR 1) SET(CPACK_PACKAGE_VERSION_PATCH 0) SET(CPACK_PACKAGE_NAME "pgAgent") SET(CPACK_PACKAGE_DESCRIPTION_SUMMARY "pgAgent is a job scheduling engine for PostgreSQL") diff --git a/sql/pgagent--3.4--4.1.sql b/sql/pgagent--3.4--4.1.sql new file mode 100644 index 0000000..2e6b5f6 --- /dev/null +++ b/sql/pgagent--3.4--4.1.sql @@ -0,0 +1,395 @@ +/* +// pgAgent - PostgreSQL Tools +// +// Copyright (C) 2002 - 2018 The pgAdmin Development Team +// This software is released under the PostgreSQL Licence +// +// pgagent--3.4--4.1.sql - Upgrade the pgAgent schema to 4.1 +// +*/ + +\echo Use "CREATE EXTENSION pgagent UPDATE" to load this file. \quit + +CREATE OR REPLACE FUNCTION pgagent.pgagent_schema_version() RETURNS int2 AS ' +BEGIN + -- RETURNS PGAGENT MAJOR VERSION + -- WE WILL CHANGE THE MAJOR VERSION, ONLY IF THERE IS A SCHEMA CHANGE + RETURN 4; +END; +' LANGUAGE 'plpgsql' VOLATILE; + +CREATE OR REPLACE FUNCTION pgagent.pga_next_schedule(int4, timestamptz, timestamptz, _bool, _bool, _bool, _bool, _bool) RETURNS timestamptz AS ' +DECLARE + 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 timestamp := ''1970-01-01 00:00:00-00''; + runafter timestamp := ''1970-01-01 00:00:00-00''; + + bingo bool := FALSE; + gotit bool := FALSE; + foundval bool := FALSE; + daytweak bool := FALSE; + minutetweak bool := FALSE; + + i int2 := 0; + d int2 := 0; + + nextminute int2 := 0; + nexthour int2 := 0; + nextday int2 := 0; + nextmonth int2 := 0; + nextyear int2 := 0; + + +BEGIN + -- 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; + + -- Get the time to find the next run after. It will just be the later of + -- now() + 1m 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() + ''1 Minute''::interval)) THEN + runafter := date_trunc(''MINUTE'', jscstart); + ELSE + runafter := date_trunc(''MINUTE'', (now() + ''1 Minute''::interval)); + 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; + ELSIF nextmonth = 2 THEN + IF pgagent.pga_is_leap_year(nextyear) = TRUE THEN + nextday := 29; + ELSE + nextday := 28; + END IF; + ELSIF nextmonth = 3 THEN + nextday := 31; + ELSIF nextmonth = 4 THEN + nextday := 30; + ELSIF nextmonth = 5 THEN + nextday := 31; + ELSIF nextmonth = 6 THEN + nextday := 30; + ELSIF nextmonth = 7 THEN + nextday := 31; + ELSIF nextmonth = 8 THEN + nextday := 31; + ELSIF nextmonth = 9 THEN + nextday := 30; + ELSIF nextmonth = 10 THEN + nextday := 31; + ELSIF nextmonth = 11 THEN + nextday := 30; + ELSIF 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; + ELSIF (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; + IF minutetweak = TRUE THEN + d := 1; + ELSE + d := date_part(''YEAR'', runafter)::int2; + END IF; + FOR i IN d .. 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; + ELSIF (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 + 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(''YEAR'', 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) + 1] = 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; + minutetweak := TRUE; + daytweak := 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; + minutetweak := FALSE; + daytweak := TRUE; + END IF; + + ELSE + runafter := nextrun + INTERVAL ''1 Minute''; + bingo := FALSE; + minutetweak := TRUE; + daytweak := FALSE; + END IF; + + END LOOP; + + RETURN nextrun; +END; +' 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'; diff --git a/sql/pgagent--4.0--4.1.sql b/sql/pgagent--4.0--4.1.sql new file mode 100644 index 0000000..b2efb62 --- /dev/null +++ b/sql/pgagent--4.0--4.1.sql @@ -0,0 +1,387 @@ +/* +// pgAgent - PostgreSQL Tools +// +// Copyright (C) 2002 - 2018 The pgAdmin Development Team +// This software is released under the PostgreSQL Licence +// +// pgagent--4.0--4.1.sql - Upgrade the pgAgent schema to 4.1 +// +*/ + +\echo Use "CREATE EXTENSION pgagent UPDATE" to load this file. \quit + +CREATE OR REPLACE FUNCTION pgagent.pga_next_schedule(int4, timestamptz, timestamptz, _bool, _bool, _bool, _bool, _bool) RETURNS timestamptz AS ' +DECLARE + 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 timestamp := ''1970-01-01 00:00:00-00''; + runafter timestamp := ''1970-01-01 00:00:00-00''; + + bingo bool := FALSE; + gotit bool := FALSE; + foundval bool := FALSE; + daytweak bool := FALSE; + minutetweak bool := FALSE; + + i int2 := 0; + d int2 := 0; + + nextminute int2 := 0; + nexthour int2 := 0; + nextday int2 := 0; + nextmonth int2 := 0; + nextyear int2 := 0; + + +BEGIN + -- 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; + + -- Get the time to find the next run after. It will just be the later of + -- now() + 1m 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() + ''1 Minute''::interval)) THEN + runafter := date_trunc(''MINUTE'', jscstart); + ELSE + runafter := date_trunc(''MINUTE'', (now() + ''1 Minute''::interval)); + 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; + ELSIF nextmonth = 2 THEN + IF pgagent.pga_is_leap_year(nextyear) = TRUE THEN + nextday := 29; + ELSE + nextday := 28; + END IF; + ELSIF nextmonth = 3 THEN + nextday := 31; + ELSIF nextmonth = 4 THEN + nextday := 30; + ELSIF nextmonth = 5 THEN + nextday := 31; + ELSIF nextmonth = 6 THEN + nextday := 30; + ELSIF nextmonth = 7 THEN + nextday := 31; + ELSIF nextmonth = 8 THEN + nextday := 31; + ELSIF nextmonth = 9 THEN + nextday := 30; + ELSIF nextmonth = 10 THEN + nextday := 31; + ELSIF nextmonth = 11 THEN + nextday := 30; + ELSIF 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; + ELSIF (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; + IF minutetweak = TRUE THEN + d := 1; + ELSE + d := date_part(''YEAR'', runafter)::int2; + END IF; + FOR i IN d .. 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; + ELSIF (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 + 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(''YEAR'', 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) + 1] = 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; + minutetweak := TRUE; + daytweak := 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; + minutetweak := FALSE; + daytweak := TRUE; + END IF; + + ELSE + runafter := nextrun + INTERVAL ''1 Minute''; + bingo := FALSE; + minutetweak := TRUE; + daytweak := FALSE; + END IF; + + END LOOP; + + RETURN nextrun; +END; +' 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'; diff --git a/sql/pgagent--unpackaged--4.1.sql b/sql/pgagent--unpackaged--4.1.sql new file mode 100644 index 0000000..d8c5602 --- /dev/null +++ b/sql/pgagent--unpackaged--4.1.sql @@ -0,0 +1,428 @@ +/* +// pgAgent - PostgreSQL Tools +// +// Copyright (C) 2002 - 2018 The pgAdmin Development Team +// This software is released under the PostgreSQL Licence +// +// pgagent--unpackaged--4.1.sql - Convert pgAgent existing tables and functions to an extension +// +*/ + +\echo Use "CREATE EXTENSION pgagent FROM unpackaged" to load this file. \quit + +ALTER EXTENSION pgagent ADD TABLE pgagent.pga_jobagent; +ALTER EXTENSION pgagent ADD TABLE pgagent.pga_jobclass; +ALTER EXTENSION pgagent ADD TABLE pgagent.pga_job; +ALTER EXTENSION pgagent ADD TABLE pgagent.pga_jobstep; +ALTER EXTENSION pgagent ADD TABLE pgagent.pga_schedule; +ALTER EXTENSION pgagent ADD TABLE pgagent.pga_exception; +ALTER EXTENSION pgagent ADD TABLE pgagent.pga_joblog; +ALTER EXTENSION pgagent ADD TABLE pgagent.pga_jobsteplog; + +ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_exception_jexid_seq; +ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_job_jobid_seq; +ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_jobclass_jclid_seq; +ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_joblog_jlgid_seq; +ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_jobstep_jstid_seq; +ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_jobsteplog_jslid_seq; +ALTER EXTENSION pgagent ADD SEQUENCE pgagent.pga_schedule_jscid_seq; + +ALTER EXTENSION pgagent ADD FUNCTION pgagent.pgagent_schema_version(); +ALTER EXTENSION pgagent ADD FUNCTION pgagent.pga_next_schedule(int4, timestamptz, timestamptz, _bool, _bool, _bool, _bool, _bool); +ALTER EXTENSION pgagent ADD FUNCTION pgagent.pga_is_leap_year(int2); +ALTER EXTENSION pgagent ADD FUNCTION pgagent.pga_job_trigger(); +ALTER EXTENSION pgagent ADD FUNCTION pgagent.pga_schedule_trigger(); +ALTER EXTENSION pgagent ADD FUNCTION pgagent.pga_exception_trigger(); + +SELECT pg_catalog.pg_extension_config_dump('pga_jobagent', ''); +SELECT pg_catalog.pg_extension_config_dump('pga_jobclass', $$WHERE jclname NOT IN ('Routine Maintenance', 'Data Import', 'Data Export', 'Data Summarisation', 'Miscellaneous')$$); +SELECT pg_catalog.pg_extension_config_dump('pga_job', ''); +SELECT pg_catalog.pg_extension_config_dump('pga_jobstep', ''); +SELECT pg_catalog.pg_extension_config_dump('pga_schedule', ''); +SELECT pg_catalog.pg_extension_config_dump('pga_exception', ''); +SELECT pg_catalog.pg_extension_config_dump('pga_joblog', ''); +SELECT pg_catalog.pg_extension_config_dump('pga_jobsteplog', ''); + +CREATE OR REPLACE FUNCTION pgagent.pgagent_schema_version() RETURNS int2 AS ' +BEGIN + -- RETURNS PGAGENT MAJOR VERSION + -- WE WILL CHANGE THE MAJOR VERSION, ONLY IF THERE IS A SCHEMA CHANGE + RETURN 4; +END; +' LANGUAGE 'plpgsql' VOLATILE; + +CREATE OR REPLACE FUNCTION pgagent.pga_next_schedule(int4, timestamptz, timestamptz, _bool, _bool, _bool, _bool, _bool) RETURNS timestamptz AS ' +DECLARE + 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 timestamp := ''1970-01-01 00:00:00-00''; + runafter timestamp := ''1970-01-01 00:00:00-00''; + + bingo bool := FALSE; + gotit bool := FALSE; + foundval bool := FALSE; + daytweak bool := FALSE; + minutetweak bool := FALSE; + + i int2 := 0; + d int2 := 0; + + nextminute int2 := 0; + nexthour int2 := 0; + nextday int2 := 0; + nextmonth int2 := 0; + nextyear int2 := 0; + + +BEGIN + -- 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; + + -- Get the time to find the next run after. It will just be the later of + -- now() + 1m 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() + ''1 Minute''::interval)) THEN + runafter := date_trunc(''MINUTE'', jscstart); + ELSE + runafter := date_trunc(''MINUTE'', (now() + ''1 Minute''::interval)); + 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; + ELSIF nextmonth = 2 THEN + IF pgagent.pga_is_leap_year(nextyear) = TRUE THEN + nextday := 29; + ELSE + nextday := 28; + END IF; + ELSIF nextmonth = 3 THEN + nextday := 31; + ELSIF nextmonth = 4 THEN + nextday := 30; + ELSIF nextmonth = 5 THEN + nextday := 31; + ELSIF nextmonth = 6 THEN + nextday := 30; + ELSIF nextmonth = 7 THEN + nextday := 31; + ELSIF nextmonth = 8 THEN + nextday := 31; + ELSIF nextmonth = 9 THEN + nextday := 30; + ELSIF nextmonth = 10 THEN + nextday := 31; + ELSIF nextmonth = 11 THEN + nextday := 30; + ELSIF 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; + ELSIF (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; + IF minutetweak = TRUE THEN + d := 1; + ELSE + d := date_part(''YEAR'', runafter)::int2; + END IF; + FOR i IN d .. 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; + ELSIF (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 + 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(''YEAR'', 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) + 1] = 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; + minutetweak := TRUE; + daytweak := 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; + minutetweak := FALSE; + daytweak := TRUE; + END IF; + + ELSE + runafter := nextrun + INTERVAL ''1 Minute''; + bingo := FALSE; + minutetweak := TRUE; + daytweak := FALSE; + END IF; + + END LOOP; + + RETURN nextrun; +END; +' 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'; diff --git a/sql/pgagent.sql b/sql/pgagent.sql index 7eb42ba..7ff552b 100644 --- a/sql/pgagent.sql +++ b/sql/pgagent.sql @@ -476,7 +476,7 @@ BEGIN (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 + (date_part(''MONTH'', nextrun) = 2 AND ((pgagent.pga_is_leap_year(date_part(''YEAR'', 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PgAgent leap year problem @ 2020-03-04 07:52 Ashesh Vashi <[email protected]> parent: Neel Patel <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Ashesh Vashi @ 2020-03-04 07:52 UTC (permalink / raw) To: Neel Patel <[email protected]>; +Cc: Mehmet Emin KARAKAŞ <[email protected]>; Dave Page <[email protected]>; pgadmin-hackers On Wed, Mar 4, 2020 at 12:36 PM Neel Patel <[email protected]> wrote: > Hi Ashesh, > > I have reviewed it and looks good. I have updated the sql version in > header of each sql file. > Thanks Neel. Committed the patch with the change suggested offline regarding the updated version in the windows resource (.rc) files. -- Thanks, Ashesh > > Thanks, > Neel Patel > > On Wed, Mar 4, 2020 at 12:22 PM Ashesh Vashi < > [email protected]> wrote: > >> On Wed, Mar 4, 2020 at 10:53 AM Neel Patel <[email protected]> >> wrote: >> >>> Hi Mehmet, >>> >>> It was a bug in pgAgent. Thank you for sharing the patch. I have >>> reviewed and tested, it is working fine. >>> >>> Dave/Ashesh:- Can we commit this patch ? >>> >> Mehmet, >> >> Thanks for your patch. >> Changes look good except it does not take care of the upgrade scenarios. >> >> Hence - updated the patch for the same. >> >> Neel, >> >> Would you please review the updated patch with the upgrade scenarios? >> >> >> -- Thanks, Ashesh >> >>> >>> Thanks, >>> Neel Patel >>> >>> On Mon, Mar 2, 2020 at 5:26 PM Mehmet Emin KARAKAŞ <[email protected]> >>> wrote: >>> >>>> Hi, >>>> >>>> Scheduled tasks set as the last day of February this year did not work. >>>> In your schedule function have a small bug. Leap year function get year >>>> parameter, but your code is sending day as a parameter. Fixing patch >>>> attached in this mail. >>>> >>>> Best Regards... >>>> >>>> -- >>>> MEHMET EMİN KARAKAŞ >>>> PostgreSQL DBA >>>> >>>> Turksat Satellite Communication and Cable TV Operations >>>> Konya Yolu 40. Km. 06839 Golbasi / ANKARA - TÜRKİYE >>>> Tel : +90 312 615 32 73 >>>> Faks : +90 312 615 32 77 >>>> >>> ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-03-04 07:52 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-03-02 11:56 PgAgent leap year problem Mehmet Emin KARAKAŞ <[email protected]> 2020-03-04 05:23 ` Neel Patel <[email protected]> 2020-03-04 06:52 ` Ashesh Vashi <[email protected]> 2020-03-04 07:06 ` Neel Patel <[email protected]> 2020-03-04 07:52 ` Ashesh Vashi <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox