Re: RFC: pgAgent Scheduler Design

Dave Page wrote:
> Hi,
>
> I'm working on the scheduler design for pgAgent - this is basically a
> service/daemon similar to SQL Server's 'SQL Server Agent' that runs
> multi step jobs based on specified schedules, in which each step may be
> an SQL script to run against any database in the cluster, or a
> batch/shell script.
>
> The basic design that I'm leaning towards is as follows, which each
> schedule being represented in one row in a table.
>
> Start/End/Run
> -------------
>
> The start date of a given schedule will be a date value (StartDate) -
> the schedule will not be active until this date has been reached.
>
> End date - a schedule may have date value (EndDate), after which, no
> more instances of the schedule will run. If null, the job will continue
> indefinately, or until the end count is reached (see next para).
>
> End count - each schedule will include a run count value (RunCount),
> which will be incremented with each run. When this value reaches the end
> count value (EndCount), no more instances of the schedule will run. If
> set to zero or null, the schedule will run indefinately, or until
> EndDate is reached.
>
> If both EndDate and EndCount are set, the schedule will end after the
> first has been reached.
>
> Run time - each schedule will run at the rime specified in the time
> field RunTime
>
> Schedule types
> --------------
>
> A variety of schedule types will allow most requirements to be met. The
> proposed types, and their representations are:
>
> One shot - This type of schedule will execute once only at the date and
> time specified in the StartDate and RunTime values.
>
> Hourly - This schedule will execute repeatedly at the time specified in
> RunTime, on or after the date specified in StartDate. A bool[24] value
> (Hours) will specify which hours of the day the schedule will run at.
>
> Daily - This schedule will execute repeatedly at the time specified in
> RunTime, on or after the date specified in StartDate. An integer value
> will specify the number of days between each run.
>
> Weekly - This schedule will execute repeatedly at the time specified in
> RunTime, on or after the date specified in StartDate. A bool[7] value
> (WeekDays) will specify which days of the week to run on.
>
> Monthly - This schedule will execute repeatedly at the time specified in
> RunTime, on or after the date specified in StartTime. A bool[12] value
> (Months) will specify which months to run in. A bool[31] value
> (MonthDays) will specify which days of the month to run on. Jobs set to
> run on non-existant days (such as 31/02) will be skipped.

Better is a bool[32], where the 32nd day is the last day of the month.
Silently skipping is not good, should at least be logged.

> Exception Schedules
> -------------------
>
> A negative schedule will be identical to a normal schedule, except that
> a bool value (Exception) will be set to true. When a job is due to run
> at a given schedule, if an exception schedule occurs at the same time,
> the job will not run. For example, if a daily schedule is defined to run
> a job every second day, a weekly exception schedule may be created with
> a matching run time on Fridays only. This would mean that the job would
> actually run on every 2nd day unless it ws a Friday.

Hm, negative schedule...
I doubt the user will understand this.
Exceptions are probably only needed for days, e.g. "I want to do backups
every weekday, but on Jan 1st nobody will change the tape so I don't
want to have it run then."

So I'd propose an additional exception table:

CREATE TABLE pgagent.pga_exception
(
jexscid int4 NOT NULL,
jexdate date NOT NULL,
jexdorun bool, -- run in addition to schedule if true
CONSTRAINT pga_exception_pkey PRIMARY KEY (jexscid, jexdate),
CONSTRAINT pga_exception_jexscid_fkey FOREIGN KEY (jexscid)
REFERENCES pgagent.pga_schedule (jscid)
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;

Regards,
Andreas