Re: PG_CRON

On 2024-03-25 16:48 +0100, Geri Wright wrote:
> I have pg_cron installed. It's working fine for simple schedules but having
> difficulty scheduling a job to run only on the 4th Sunday of the month at 3
> am. It seams to ignore the combination of "day of month" and "day of week".
> It runs everyday from the 22nd through the 28th. I have tried with the
> following schedules.
>
> 0 3 22-28 * 0
> 0 3 22-28 * 7
> 0 3 22,23,24,25,26,27,28 * 0
> 0 3 22,23,24,25,26,27,28 * 7

That's a question for the pg_cron maintainers but I'd say its simply
POSIX-compliant: POSIX specifies that day-of-month or day-of-week should
match if both are specified (i.e. not *):

"if either the month or day of month is specified as an element or list,
and the day of week is also specified as an element or list, then any
day matching either the month and day of month, or the day of week,
shall be matched" [1]

Example 3 in [1] also illustrates this.

And pg_cron implements[2] it like that if I'm not mistaken:

(schedule->flags & (DOM_STAR|DOW_STAR)) != 0
? (thisdom && thisdow) : (thisdom) || thisdow

This condition is false if day-of-month and day-of-week are specified:

(schedule->flags & (DOM_STAR|DOW_STAR)) != 0

so we end up with:

(thisdom) || thisdow

[1] https://pubs.opengroup.org/onlinepubs/9699919799/utilities/crontab.html
[2] https://github.com/citusdata/pg_cron/blob/c9dedd3947d80566b6418c0fb2e30f10191d3dca/src/pg_cron.c#L989,L990

--
Erik