RFC: pgAgent Scheduler Design - Mailing list pgadmin-hackers

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.

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.

Any thoughts or comments?

Regards, Dave.