cron job doesn't running as expected

[rudonx]

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
   

[rudonx]

Add more testing

we hope that runing cron job at fifty minutes past the hour, But you can see the execute interval is 50s

postgres=select * from cron.job;
 jobid |  schedule  |  command  | nodename | nodeport | database | username | active |   jobname   
-------+------------+-----------+----------+----------+----------+----------+--------+-------------
    16 | 50 * * * * | SELECT 1; | /tmp     |     5432 | postgres | rudonx   | t      | DAILYSELECT
(1 row)

postgres=> select * from cron.job_run_details limit 10;
 jobid | runid | job_pid | database | username |  command  |  status   | return_message |          start_time           |           end_time            
-------+-------+---------+----------+----------+-----------+-----------+----------------+-------------------------------+-------------------------------
    16 | 70470 | 1813893 | postgres | rudonx   | SELECT 1; | succeeded | 1 row          | 2024-05-09 00:47:38.055194+08 | 2024-05-09 00:47:38.055484+08
    16 | 70471 | 1814140 | postgres | rudonx   | SELECT 1; | succeeded | 1 row          | 2024-05-09 00:48:28.056153+08 | 2024-05-09 00:48:28.056454+08
    16 | 70472 | 1814442 | postgres | rudonx   | SELECT 1; | succeeded | 1 row          | 2024-05-09 00:49:18.056884+08 | 2024-05-09 00:49:18.057162+08