Schedule jobs that start based on the start-time of previous instance.

[jayadevanm]

What type of enhancement is this?

Configuration

What subsystems and features will be improved?

User-Defined Action (UDA)

What does the enhancement do?

I would like to schedule a job that runs at a pre-defined interval - for example, at 1 hour interval. As of now, it is possible to schedule a job like that, but there seems to be a 'drift' depending on the time the job takes to finish. For example. if a job is schedule to run every 2 minutes, and it takes 1 minute to complete, the next instance actually starts after 3 minutes from the start of the first instance. I am not sure if it is a bug or was intended to work like that. Here's how to reproduce.

BEGIN;
CREATE TABLE  test_sleep_ts_scheduler( id SERIAL PRIMARY KEY, val INT ,created_at TIMESTAMPTZ DEFAULT now());
CREATE OR REPLACE PROCEDURE public.test_sleep_ts_scheduler(job_id integer, config jsonb)
LANGUAGE plpgsql                                                           
AS $procedure$
BEGIN                    
INSERT INTO  test_sleep_ts_scheduler (val) VALUES(1);
perform pg_sleep(60);
END
$procedure$
;
SELECT add_job('test_sleep_ts_scheduler','2m');
COMMIT;

SELECT  created_at - LAG(created_at,1) OVER (ORDER BY id ) FROM test_sleep_ts_scheduler ;
 00:03:00.042483
 00:03:00.065567
 00:03:00.079623
 00:03:00.073977
 00:03:00.042072
 00:03:00.051082
 00:03:00.077354
 00:03:00.062178
 00:03:00.074481

The gap between successive values of created_at is 3 minutes. Since I set the job interval as 2 minutes. I'd expect it to be 2 minutes.

Implementation challenges

No response

[john9x]

+1

Due to this behavior we need to use cron instead of UDA.

[jfjoly]

TimescaleDB 2.9 introduces the fixed_schedule option that enables setting the job at a precise time. You can see the details in the documentation here.