Re: RFC: pgAgent Scheduler Design

Dave Page wrote:
>
>
>
>>-----Original Message-----
>>From: pgadmin-hackers-owner(at)postgresql(dot)org
>>[mailto:pgadmin-hackers-owner(at)postgresql(dot)org] On Behalf Of Dave Page
>>Sent: 02 March 2005 20:25
>>To: pgadmin-hackers(at)postgresql(dot)org
>>Subject: [pgadmin-hackers] RFC: pgAgent Scheduler Design
>>
>
>
> <snip>
>
>>The basic design that I'm leaning towards is as follows, which each
>>schedule being represented in one row in a table.
>
>
> Y'know, having thought about all that for a few days, I'm simply not
> happy with it - it's all too messy and inconsistent :-(.
>
> So, thought #2 - follow a modified cron-style design:
>
>
> Control
> -------
>
> jscstart timestamptz -- date/time the schedule may
> start at.
> jscend timestamptz -- date/time the schedule will end at.
> jscenabled bool -- is the schedule active?
>
> Note the lack of run counting in this design. This is primarily because
> missed runs (caused by system downtime for example) would be extremely
> difficult to count, potentially leading to errors calculating the
> schedule end. In addition, an end date would almost certainly give most
> people the flexibility they require.
>
>
> Schedule
> --------
>
> jscminutes bool[60] -- 0,1,2,3...59
> jschours bool[24] -- 0,1,2,3...23
> jscweekdays bool[7] -- mon,tue,wed...sun
> jscmonthdays bool[32] -- 0,1,2,3...31,last
> jscmonths bool[12] -- jan,feb,mar...dec
>
> In this scheme, the elements of the arrays represent the possible values
> for each part of the schedule - for example, jscweekdays[] represents
> mon, tue, wed, thur, fri, sat, sun. If an array contains 'f' for all
> values, it is considered to be the cron * equivalent. jscmonthdays also
> includes an additional element to represent the last day of the month,
> regardless of it's actual number, per Andreas' suggestion.
>
> As per cron, a simple algorithm would determine if a schedule should
> fire:
>
> If ((jscminutes[datetime.minute] || jscminutes.IsAllFalse()) &&
> (jschours[datetime.hour] || jschours.IsAllFalse()) &&
> (jscweekdays[datetime.weekday] || jscweekdays.IsAllFalse()) &&
> (jscmonthdays[datetime.monthday] || jscmonthdays.IsAllFalse() ||
> (datetime.lastdayofmonth && jscmonthdays[32])) &&
> (jscmonths[datetime.month] || jscmonths.IsAllFalse()))
> {
> FireSchedule();
> }
>
> (I think that's about right - it's been a long day :-) )

Sorry, this won't work.

It is mandatory that a "next run" schedule is calculated after a job has
run, and FireSchedule will run when nextRun is >= current_timestamp.

Imagine two jobs scheduled for the very same minute, and only one
pgAgent running. It will run the first job, which will run for lets say
2 minutes. After that, the fire time for the second job is not due any
more, so it will run somewhat later, if ever.

That's what pga_next_schedule is good for. You'll have quite a hard time
to calulate it from your way of storing schedules, I'm afraid... It's
somewhat the difference between cron and anacron.

Regards,
Andreas