citusdata / citusdata/pg_cron

cron.timezone Does Nothing

Open
#238 3 comments 0 reactions 0 assignees View on GitHub
Dominant language
C
Stars
3.9k
Forks
260
Avg merge
5d 5h
Merged PRs (30d)
1

Description

**Versions used:**
PostgreSQL: 15.1
pg_cron: `pg_cron_15-1.4.2-1.rhel7.x86_64`

when setting `cron.timezone` in our `postgresql.conf` file to any of the following:
```
US/Central
CDT
CST
```
... a test job does not run given the following:
```
postgres=# show timezone;
TimeZone
------------
US/Central
(1 row)
```

Example:

```
postgres=# show timezone;
TimeZone
------------
US/Central
(1 row)

postgres=# show cron.timezone;
cron.timezone
---------------
US/Central
(1 row)

postgres=# select now();
now
-------------------------------
2023-02-09 07:51:12.289057-06
(1 row)

postgres=# \d a
Table "dbaadmin.a"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+---------
b | bigint | | |

postgres=# SELECT cron.schedule('testing', '54 07 * * *',
postgres(# $$vacuum a$$
postgres(# );
schedule
----------
12
(1 row)

postgres=# update cron.job set nodename='' where jobid=12;
UPDATE 1
postgres=# select now();
now
-------------------------------
2023-02-09 07:51:35.595301-06
(1 row)

postgres=# select * from cron.job_run_details where jobid=12;
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
-------+-------+---------+----------+----------+---------+--------+----------------+------------+----------
(0 rows)

...
...
...

postgres=# select now();
now
-------------------------------
2023-02-09 07:53:20.933201-06
(1 row)

postgres=# select * from cron.job_run_details where jobid=12;
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
-------+-------+---------+----------+----------+---------+--------+----------------+------------+----------
(0 rows)
```

if i modify the schedule of the job for every minute, it shows it working in the local timezone:
```
postgres=# select now();
now
------------------------------
2023-02-09 08:01:03.15321-06
(1 row)

postgres=# SELECT cron.schedule('testing', '* * * * *',
postgres(# $$vacuum a$$
postgres(# );
schedule
----------
12
(1 row)

postgres=# update cron.job set nodename='' where jobid=12;
UPDATE 1
postgres=# select now();
now
-------------------------------
2023-02-09 08:01:17.372257-06
(1 row)

postgres=# select * from cron.job;
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-------+-----------+----------+----------+----------+----------+----------+--------+---------
12 | * * * * * | vacuum a | | 45221 | postgres | postgres | t | testing
(1 row)

...
...
...

postgres=# select now();
now
----------------------------
2023-02-09 08:02:00.517-06
(1 row)

postgres=# select * from cron.job_run_details where jobid=12;
jobid | runid | job_pid | database | username | command | status | return_message | start_time | end_time
-------+-------+---------+----------+----------+----------+-----------+----------------+-------------------------------+-------------------------------
12 | 81 | 26949 | postgres | postgres | vacuum a | succeeded | VACUUM | 2023-02-09 08:02:00.010479-06 | 2023-02-09 08:02:00.011612-06
(1 row)
```

Contributor guide

No contributing guide indexed for this repository

Research direction

Reproduce the reported scheduling behavior with cron.timezone set to US/Central and compare fixed-time and every-minute jobs. Trace the scheduler's timezone handling and verify that fixed-time jobs run at the configured local time, with cron.job_run_details recording the execution.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.