cron job doesn't running as expected
- Dominant language
- C
- Stars
- 3.9k
- Forks
- 260
- Avg merge
- 5d 5h
- Merged PRs (30d)
- 1
Description
PostgreSQL: 12.9
pg_cron:1.5
### how to repo
```
create table image_test(
id varchar(100),
create_time TIMESTAMP DEFAULT (TO_CHAR((NOW() AT TIME ZONE 'Asia/Shanghai'), 'YYYY-MM-DD HH24:MI:SS')::timestamp))
partition by range (create_time);`
```
create function:
```
CREATE OR REPLACE FUNCTION create_next_day_partition() RETURNS integer
LANGUAGE PLPGSQL
AS $$
DECLARE
dateStr varchar;
BEGIN
SELECT to_char(DATE 'tomorrow', 'YYYYMMDD') INTO dateStr;
EXECUTE
format('CREATE TABLE image_test_%s PARTITION OF image_test
FOR VALUES FROM (current_date::timestamp + interval ''1 day'') TO (current_date::timestamp + interval ''1 day'' + interval ''1 day'')', dateStr);
RETURN 1;
END;
$$;
```
query the cron job:
```
postgres=# select * from cron.job;
jobid | schedule | command | nodename | nodeport | database | username | active | jobname
-------+-------------+------------------------------------+-----------+----------+----------+----------+--------+--------------
11 | 50 13 * * * | SELECT create_next_day_partition() | localhost | 5432 | demo3 | postgres | t | create_table
(1 row)
```
it will have two issues:
1. the first run of this cron job is not 13:50(GMT),it created the table before this time
```
11 | 662 | 12626 | demo3 | postgres | SELECT create_next_day_partition() | succeeded | 1 row | 2024-05-08 21:32:27.237667+08 | 2024-05-08 21:32:27.242122+08
```
2. the cron job is executed repeatedly , failed because the target table exists

Contributor guide
No contributing guide indexed for this repository
Research direction
Start with the PostgreSQL and pg_cron versions, the cron.job entry, and the create_next_day_partition() reproduction. Compare the configured 13:50 schedule with the recorded execution times and inspect why the function is run again after creating the partition. Done means the documented reproduction explains the timing and prevents repeated failures when the target table already exists.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 30/100