How to add job that will be ran at the same time every day

TimescaleDB and PostgreSQL
1

Hello!

Is there a possibility to assign a job for user-defined action one time for all day runs?
Assume I want to add_job (alter_job) for every day with the same time. May be config property have appropriate attribute?
Where can I find all the attributes for config property?

Thanks in advance

2

Hi @LuxCore,

TimescaleDB 2.9.0 will include two updates to job scheduling that together should cover your request:

  1. A new initial_start parameter on all policies (e.g. compression policy) and jobs for user-defined actions that sets the time the job is first run
  2. Fixed schedule semantics for jobs. Up to 2.8.1 our job scheduler had a drifting behavior, i.e. the next start of a job was determined as its last finish time plus the schedule interval. With the new semantics available in 2.9.0, if a fixed schedule is used, the next start of a job is set to begin schedule_interval after the last start. So it always starts at the same time if the schedule interval is a day multiple.

If I understand your request correctly, once TimescaleDB 2.9.0 is available and your instance is upgraded to it, you’ll be able to just add the job with an initial_start at the time you want your user-defined action to run and set the schedule_interval to 1 day.

Assuming that you want your job to run at 08:00 every morning:

add_job('your_user_defined_action', schedule_interval => interval '1 DAY', initial_start => '2022-12-20 08:00'::timestamptz);

Up to 2.8.1, there is a work around but with a catch:

  • You have to find the job that performs your user-defined action and note its job_id

  • Alter the job’s next start to start next morning at 08:00 - alter_job(job_id, next_start => '2022-12-20 08:00+00'::timestamptz)

  • But unfortunately, as I wrote above, due to drifting semantics, the job will start a little bit later every day: If one day it takes 3 minutes to run, the next day it will start at 08:03. If it then takes 4 minutes to run, it will then start at 08:07 the day after, etc.

You would have to add some automation to do the steps above every night and set the next day’s job start at 08:00. Or you can upgrade to 2.9.0 once it is released and let TimescaleDB take care of that :slight_smile:

1 Like
3

Thank you! That`s greate news.