citusdata / citusdata/pg_cron

cron job doesn't running as expected

Open
#327 1 comment 0 reactions 0 assignees View on GitHub
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
![image](https://github.com/citusdata/pg_cron/assets/73181232/f6a1698c-9a75-4054-b4b6-dfdbfc78eee7)

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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.