public inbox for [email protected]
help / color / mirror / Atom feedFrom: Ashesh Vashi <[email protected]>
To: Neel Patel <[email protected]>
Cc: Mehmet Emin KARAKAŞ <[email protected]>
Cc: Dave Page <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: PgAgent leap year problem
Date: Wed, 4 Mar 2020 12:22:46 +0530
Message-ID: <CAG7mmow0UvptW972pfHM3yJs+NDLpdv8E9Bw7GGdBOKed=Kokw@mail.gmail.com> (raw)
In-Reply-To: <CACCA4P2A=t9UcKYyiB3-Z0J3zNQNsa48UwWrwUJ90koTxNL99g@mail.gmail.com>
References: <CANQrdXBzJNK+_tDgju_qTq-OuG7dKdqNZ6Xv=-n0-RoYM0PXhA@mail.gmail.com>
<CACCA4P2A=t9UcKYyiB3-Z0J3zNQNsa48UwWrwUJ90koTxNL99g@mail.gmail.com>
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
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: PgAgent leap year problem
In-Reply-To: <CAG7mmow0UvptW972pfHM3yJs+NDLpdv8E9Bw7GGdBOKed=Kokw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox