pg_cron
pg_cron copied to clipboard
citusdata
Leap year scheduling problem
For my project we use Postgres 12 and the pg_cron extension at version 1.6. During the testing phase we realized that the scheduling for the days of the week does not work correctly and the cause is linked to the current year which is a leap year. Scheduling these jobs:
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-- | -- | -- | -- | -- | -- | -- | -- | --
22 | 40 07 * * 4 | SELECT 1 | localhost | 5,432 | dbname | username | true | WandaVisionToday
21 | 40 07 * * 5 | SELECT 1 | localhost | 5,432 | dbname | username | true | WandaVision
job 22 refers to the schedule with day of the week 5 (Friday) while the job with id 21 refers to a schedule with day of the week 4 (Thursday) or (Today 16/05/2024).
We noticed that job 21 was not executed while job 22 was executed. We expected the opposite. This made me think that one cause could be pg_cron's handling of leap years. I think it's all there.
Below is the extraction of the cron.job_run_details:
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
-- | -- | -- | -- | -- | -- | -- | -- | -- | --
21 | 7 | 43,042 | dbname| username| SELECT 1 | succeeded | SELECT 1 | 2024-05-16 09:40:00.034 +0200 | 2024-05-16 09:40:01.050 +0200
I hope it can help solve the problem.
Best regards Marco
That's interesting, so far no luck in reproducing it.
I tried scheduling something at 8:38am on 17/5 (today) with 38 8 17 5 *, which ran without issue.
I also tried scheduling something at 8:41am on Friday (today) with 41 8 * * 5, which ran without issue.
What does SELECT now() return?
SELECT now() return 2024-05-17 14:20:49.324 +0200
I set the timestamp to Europe/Rome.
I am having the same issue. I have a bunch of jobs that I scheduled on Mondays, but they are running on Sundays since March 2024 (which supports the assumption that this is caused by the leap year).
This is what I get from job_run_details around the start of March
jobid|start_time|schedule
28|2024-03-03 02:05:00.004781 +00:00|25 4 * * MON
18|2024-03-03 02:00:00.460541 +00:00|0 2 * * MON
28|2024-02-26 02:05:00.004350 +00:00|25 4 * * MON
18|2024-02-26 02:00:00.723335 +00:00|0 2 * * MON
28|2024-02-19 02:05:00.005878 +00:00|25 4 * * MON
18|2024-02-19 02:00:00.534577 +00:00|0 2 * * MON
18|2024-02-12 02:00:00.770294 +00:00|0 2 * * MON
18|2024-02-05 02:00:00.084901 +00:00|0 2 * * MON
You can see here that the Monday jobs ran fine in February (5th, 12th , 19th and 26th are all Mondays) and they started running on Sunday since March 3rd.
I am running postgres on Google Cloud using Cloud SQL, with the maintenance version POSTGRES_15_5.R20240130.00_09, postgres version 15.5, pg_cron version 1.6.
SELECT NOW();
gives me the correct time: 2024-05-20 09:00:13.348312 +00:00.
Moreover you can see in the start_time, that pg_cron itself stores the wrong date in the column (March 3rd which is a Sunday), so it does not seem confused about the current time, but rather about which day of the week it represents.
I am having the same issue, and I found that pg_cron doesn't recognize leap years, and the bug inited after February 28. If I want to schedule something for today (May 31), I have to put tomorrow's date (June 01).
The same happens with weekdays, and for today (Friday), I have to schedule it for tomorrow (Saturday).
Initially, the problem occurred on the PG12 cluster, but further tests shown the same error on AWS RDS pg15.
I am also facing the same issue with the @monthly job that was running on the last day of the previous month in 2024 year. schedule @"@monthly"
| table_name | cron.job_run_details |
|---|---|
| start_time | end_time |
| 2024-11-30 00:00:00.338611+00 | 2024-11-30 00:00:03.58219+00 |
| 2024-10-31 00:00:00.150215+00 | 2024-10-31 00:00:02.42534+00 |
| 2024-09-30 00:00:00.0755+00 | 2024-09-30 00:00:01.669262+00 |
| 2024-08-31 00:00:00.061474+00 | 2024-08-31 00:00:01.760078+00 |
| 2024-07-31 00:00:00.063722+00 | 2024-07-31 00:00:01.775881+00 |
| 2024-06-30 00:00:00.059915+00 | 2024-06-30 00:00:01.647093+00 |
| 2024-05-31 00:00:00.070418+00 | 2024-05-31 00:00:01.171584+00 |
| 2024-04-30 00:00:00.071351+00 | 2024-04-30 00:00:00.822347+00 |
| 2024-03-31 00:00:00.067627+00 | 2024-03-31 00:00:00.756022+00 |
| 2024-02-29 00:00:00.048748+00 | 2024-02-29 00:00:00.312714+00 |
| 2024-01-31 00:00:00.06602+00 | 2024-01-31 00:00:00.690779+00 |
| 2024-01-01 00:00:00.072269+00 | 2024-01-01 00:00:00.597968+00 |
| 2023-12-01 00:00:00.067171+00 | 2023-12-01 00:00:00.828748+00 |
| 2023-11-01 00:00:00.070223+00 | 2023-11-01 00:00:00.576374+00 |
| 2023-10-01 00:00:00.83783+00 | 2023-10-01 00:00:00.915509+00 |
| 2023-09-01 00:00:00.037839+00 | 2023-09-01 00:00:00.407945+00 |
| 2023-08-01 00:00:01.050022+00 | 2023-08-01 03:14:04.656243+00 |
| 2023-07-01 00:00:00.063332+00 | 2023-07-01 03:17:53.775742+00 |
| 2023-06-01 00:00:00.027724+00 | 2023-06-01 00:00:00.440762+00 |
| 2023-02-01 00:00:00.008174+00 | 2023-02-01 00:00:00.563614+00 |
| 2023-01-01 00:00:00.016212+00 | 2023-01-01 00:00:00.489909+00 |
| 2022-12-01 00:00:00.008568+00 | 2022-12-01 00:00:00.277078+00 |
| 2022-11-01 00:00:00.025814+00 | 2022-11-01 00:00:00.310767+00 |
| 2022-10-01 00:00:00.020995+00 | 2022-10-01 00:00:00.343351+00 |
Could it be related to https://github.com/citusdata/pg_cron/commit/35d1475099449415e63a28695d1cd98f3fd8228d? This is the first leap year since then.
Could it be related to 35d1475? This is the first leap year since then.
Hello, I submitted the time zone guc parameter (but it's been a long time since today). Do you have a way to reproduce this leap year error?
Looking forward to your reply, thank you
I have faced this issue in AWS Aurora PostgreSQL and used UTC for the database timestamp and cron as below Show cron.timezone as GMT
Hello, I don't know how to reproduce this error? I tried several times but no luck. Thank you.
Hi TsinghuaLucky912 - There are no issues with the daily or hourly scheduler jobs, only with the monthly job(i.e., First of the month).You can find more details in my first post. If you schedule any jobs monthly, the issue can be reproduced.
I scheduled a job to run at @monthly, and there were no issues in 2023. The job started and ended as expected. However, in 2024, the issue began in February. The job unexpectedly started on January 31, 2024, instead of February 1st.
Here are the old job history information for your reference.
| table_name | cron.job_run_details |
|---|---|
| start_time | end_time |
| 2024-11-30 00:00:00.338611+00 | 2024-11-30 00:00:03.58219+00 |
| 2024-10-31 00:00:00.150215+00 | 2024-10-31 00:00:02.42534+00 |
| 2024-09-30 00:00:00.0755+00 | 2024-09-30 00:00:01.669262+00 |
| 2024-08-31 00:00:00.061474+00 | 2024-08-31 00:00:01.760078+00 |
| 2024-07-31 00:00:00.063722+00 | 2024-07-31 00:00:01.775881+00 |
| 2024-06-30 00:00:00.059915+00 | 2024-06-30 00:00:01.647093+00 |
| 2024-05-31 00:00:00.070418+00 | 2024-05-31 00:00:01.171584+00 |
| 2024-04-30 00:00:00.071351+00 | 2024-04-30 00:00:00.822347+00 |
| 2024-03-31 00:00:00.067627+00 | 2024-03-31 00:00:00.756022+00 |
| 2024-02-29 00:00:00.048748+00 | 2024-02-29 00:00:00.312714+00 |
| 2024-01-31 00:00:00.06602+00 | 2024-01-31 00:00:00.690779+00 |
| 2024-01-01 00:00:00.072269+00 | 2024-01-01 00:00:00.597968+00 |
| 2023-12-01 00:00:00.067171+00 | 2023-12-01 00:00:00.828748+00 |
| 2023-11-01 00:00:00.070223+00 | 2023-11-01 00:00:00.576374+00 |
| 2023-10-01 00:00:00.83783+00 | 2023-10-01 00:00:00.915509+00 |
| 2023-09-01 00:00:00.037839+00 | 2023-09-01 00:00:00.407945+00 |
| 2023-08-01 00:00:01.050022+00 | 2023-08-01 03:14:04.656243+00 |
| 2023-07-01 00:00:00.063332+00 | 2023-07-01 03:17:53.775742+00 |
| 2023-06-01 00:00:00.027724+00 | 2023-06-01 00:00:00.440762+00 |
| 2023-02-01 00:00:00.008174+00 | 2023-02-01 00:00:00.563614+00 |
| 2023-01-01 00:00:00.016212+00 | 2023-01-01 00:00:00.489909+00 |
| 2022-12-01 00:00:00.008568+00 | 2022-12-01 00:00:00.277078+00 |
| 2022-11-01 00:00:00.025814+00 | 2022-11-01 00:00:00.310767+00 |
| 2022-10-01 00:00:00.020995+00 | 2022-10-01 00:00:00.343351+00 |
table_name cron.job_run_details start_time start_time 2024-11-30 00:00:00.338611+00 2024-11-30 00:00:03.58219+00 2024-10-31 00:00:00.150215+00 2024-10-31 00:00:02.42534+00 2024-09-30 00:00:00.0755+00 2024-09-30 00:00:01.669262+00 2024-08-31 00:00:00.061474+00 2024-08-31 00:00:01.760078+00 2024-07-31 00:00:00.063722+00 2024-07-31 00:00:01.775881+00 2024-06-30 00:00:00.059915+00 2024-06-30 00:00:01.647093+00 2024-05-31 00:00:00.070418+00 2024-05-31 00:00:01.171584+00 2024-04-30 00:00:00.071351+00 2024-04-30 00:00:00.822347+00 2024-03-31 00:00:00.067627+00 2024-03-31 00:00:00.756022+00 2024-02-29 00:00:00.048748+00 2024-02-29 00:00:00.312714+00 2024-01-31 00:00:00.06602+00 2024-01-31 00:00:00.690779+00 2024-01-01 00:00:00.072269+00 2024-01-01 00:00:00.597968+00 2023-12-01 00:00:00.067171+00 2023-12-01 00:00:00.828748+00 2023-11-01 00:00:00.070223+00 2023-11-01 00:00:00.576374+00 2023-10-01 00:00:00.83783+00 2023-10-01 00:00:00.915509+00 2023-09-01 00:00:00.037839+00 2023-09-01 00:00:00.407945+00 2023-08-01 00:00:01.050022+00 2023-08-01 03:14:04.656243+00 2023-07-01 00:00:00.063332+00 2023-07-01 03:17:53.775742+00 2023-06-01 00:00:00.027724+00 2023-06-01 00:00:00.440762+00 2023-02-01 00:00:00.008174+00 2023-02-01 00:00:00.563614+00 2023-01-01 00:00:00.016212+00 2023-01-01 00:00:00.489909+00 2022-12-01 00:00:00.008568+00 2022-12-01 00:00:00.277078+00 2022-11-01 00:00:00.025814+00 2022-11-01 00:00:00.310767+00 2022-10-01 00:00:00.020995+00 2022-10-01 00:00:00.343351+00
Thank you very much for your supplement. Your plan starts on the first day of each month, but starting from the second execution in 2024, the time is wrong. The correct time should be 2024-02-01. I want to know what your timezone and cron.timezone configurations are? And your cron configuration (time) rules?
ps: It has been a long time since I submitted cron.timezone, and I am trying to understand the modifications made by others later😄
Yes, correct time should be beginning of the months similar to 2023 plan year. My DB timezone setup is UTC and cron.timezone is GMT(I mean default).
Yes, correct time should be beginning of the months similar to 2023 plan year. My DB timezone setup is UTC and cron.timezone is GMT(I mean default).
What are your cron configuration rules?
Hi everyone, I just had a chat with my friend (zhaojunwang): When the cron configuration is @monthly, we guess that the reason why the wrong time 2024-01-31 can be executed is:
// ShouldRunTask
...
bool lastdom = (schedule->flags & DOM_LAST) != 0 && tomorrow_tm->tm_mday == 1; // false
bool thisdom = lastdom || bit_test(schedule->dom, dayOfMonth) != 0; // false
bool thisdow = bit_test(schedule->dow, dayOfWeek); // true
if (bit_test(schedule->minute, minute) &&
bit_test(schedule->hour, hour) &&
bit_test(schedule->month, month) &&
( (schedule->flags & (DOM_STAR|DOW_STAR)) != 0 // true
? (thisdom && thisdow) : (thisdom) || thisdow)) { // here
if ((doNonWild && !(schedule->flags & (MIN_STAR|HR_STAR)))
|| (doWild && (schedule->flags & (MIN_STAR|HR_STAR))))
{
return true;
}
}
...
This line of code has a parenthesis problem (scope), which causes || thisdow to be re-applied after the ternary operator. This is not the real purpose of the patch introduced before. He resubmitted a patch, and these are my current analysis. Thank you!
I need help with verifying if the issue is resolved by #365 that is released in v1.6.5.
It will take some time before the OS packages are ready though.
