large crontab database design - Mailing list pgsql-general

I was thinking on how one would design an optimal (performance-wise)
database of large number of schedules with crontab-like semantic. There
will potentially be hundreds of thousands or even millions of "crontab"
entries, and there will be a query run once every minute to determine
which entries are to be run for that minute. The primary goal is, of
course, for the query to run as fast as possible.

My first stab is, straightforward enough, like below:

  create table schedule (
    id int not null primary key,
    command text
  );

  create table schedule_detail(
    schedule_id int not null references schedule(id),
    minute smallint not null,
    hour smallint not null,
    day smallint not null,
    month smallint not null,
    year smallint not null,
    dayofweek smallint not null
  );

  create index idx_schedule_detail_schedule_id on
    schedule_detail(schedule_id);
  create index idx_schedule_detail_minute on
    schedule_detail(minute);
  create index idx_schedule_detail_hour on
    schedule_detail(hour);
  create index idx_schedule_detail_day on
    schedule_detail(day);
  create index idx_schedule_detail_month on
    schedule_detail(month);
  create index idx_schedule_detail_year on
    schedule_detail(year);
  create index idx_schedule_detail_dayofweek on
    schedule_detail(dayofweek);

A "multiple items" syntax like '1,2,3' in a crontab time field will be
presented with several records in the schedule_detail table. An "every
N" syntax like '*/5' will be represented by a negative number '-5' in
the database field.

For example, this crontab entry:

  # every three hours except on weekends (sat/sun)
  0 */3 * * 1,2,3,4,5 CHECK-THE-COPIER-MACHINE

will translate to these records:

  insert into schedule values (1, 'CHECK-THE-COPIER-MACHINE');
  insert into schedule_detail values (1, 0, -3, -1, -1, -1, 1);
  insert into schedule_detail values (1, 0, -3, -1, -1, -1, 2);
  insert into schedule_detail values (1, 0, -3, -1, -1, -1, 3);
  insert into schedule_detail values (1, 0, -3, -1, -1, -1, 4);
  insert into schedule_detail values (1, 0, -3, -1, -1, -1, 5);

The query will then be:

  select distinct schedule_id,command from schedule_detail
  left join schedule on schedule_id=schedule.id
  where
  (year=extract(year from current_date) or
   (year<0 and cast(extract(year from current_date) as smallint)
               % year = 0)) and
  (month=extract(month from current_date) or
   (month<0 and cast(extract(month from current_date) as smallint)
               % month = 0)) and
  (day=extract(month from current_date) or
   (day<0 and cast(extract(day from current_date) as smallint)
               % day = 0)) and
  (hour=extract(hour from current_time) or
   (hour<0 and cast(extract(hour from current_time) as smallint)
               % hour = 0)) and
  (minute=extract(minute from current_time) or
   (minute<0 and cast(extract(minute from current_time) as smallint)
               % month = 0));

Several questions:

1. Any idea for a better design? The selectivity of each field is not so
great (only 60 different values for minute, 24 for day, etc).

2. What if we want to add support for things like "every 3 hours after
1:00AM" (1:00, 4:00, 7:00, and so on) or "beginning from 24 Jan 2004 and
every 12 days after that." We won't be able to use the negative number
and modulo trick, or even the crontab-like database fields for this.

3. I think I have a race condition in my query (because I call the
current_date & current_time function several times), but I don't know
how to fix this without resorting to procedure/function.

--
dave