alter_job()

Alter a job that is scheduled to run automatically

Community Since 1.2.0

s scheduled using the automation framework run periodically in a background worker. You can change the schedule of these s with the alter_job function. To alter an existing , refer to it by job_id. The job_id runs a given , and its current schedule can be found in the timescaledb_information.jobs view, which lists information about every scheduled s, as well as in timescaledb_information.job_stats. The job_stats view also gives information about when each was last run and other useful statistics for deciding what the new schedule should be.

Calculate the next start on failure

When a run results in a runtime failure, the next start of the is calculated taking into account both its retry_period and schedule_interval. The next_start time is calculated using the following formula:

next_start = finish_time + consecutive_failures * retry_period ± jitter

where jitter (± 13%) is added to avoid the “thundering herds” effect.

To ensure that the next_start time is not put off indefinitely or produce timestamps so large they end up out of range, it is capped at 5*schedule_interval. Also, more than 20 consecutive failures are not considered, so if the number of consecutive failures is higher, then it multiplies by 20.

Additionally, for s with fixed schedules, the system ensures that if the next start ( calculated as specified), surpasses the next scheduled execution, the is executed again at the next scheduled slot and not after that. This ensures that the does not miss scheduled executions.

There is a distinction between runtime failures that do not cause the to crash and crashes. In the event of a crash, the next start calculation follows the same formula, but it is always at least 5 minutes after the ‘s last finish, to give an operator enough time to disable it before another crash.

Samples

• Reschedule ID 1000 so that it runs every two days:

  SELECT alter_job(1000, schedule_interval => INTERVAL '2 days');
  

• Disable scheduling of the compression policy on the conditions :

  SELECT alter_job(job_id, scheduled => false)
  FROM timescaledb_information.jobs
  WHERE proc_name = 'policy_compression' AND hypertable_name = 'conditions'
  

• Reschedule ID 1000 so that it next runs at 9:00:00 on 15 March, 2020:

  SELECT alter_job(1000, next_start => '2020-03-15 09:00:00.0+00');
  

• Alter a policy:

  You can pause and restart a policy, change how often the policy runs and the scheduling. To do this:

  -- Find the {JOB} ID for the {COLUMNSTORE} policy:
        SELECT job_id, hypertable_name, config
        FROM timescaledb_information.jobs
        WHERE proc_name = 'policy_compression';
  
  -- For example, to change the compression interval after 30 days instead of 7:
        SELECT alter_job(1000, config => '{"compress_after": "30 days"}');
  

  However, to change the after or created_before, the compression settings, or the the policy is acting on, you must remove the policy and add a new one.

Arguments

The syntax is:

SELECT alter_job(
    job_id = <job_id>,
    schedule_interval = <interval>,
    max_runtime = <interval>,
    max_retries = <integer>,
    retry_period = <interval>,
    scheduled = true | false,
    config = '<jsonb_config>',
    next_start = <timestamptz>,
    if_exists = true | false,
    check_config = '<procedure_name>',
    fixed_schedule = true | false,
    initial_start = <timestamptz>,
    timezone = '<timezone>'
);

Name	Type	Default	Required	Description
job_id	INTEGER	-	✔	The ID of the policy being modified.
schedule_interval	INTERVAL	24 hours	✖	The interval at which the job runs.
max_runtime	INTERVAL	-	✖	The maximum amount of time the job is allowed to run by the background worker scheduler before it is stopped.
max_retries	INTEGER	-	✖	The number of times the job is retried if it fails.
retry_period	INTERVAL	-	✖	The amount of time the scheduler waits between retries of the job on failure.
scheduled	BOOLEAN	true	✖	Set to false to exclude this job from being run as a background job.
config	JSONB	-	✖	-specific configuration, passed to the function when it runs. This includes: verbose_log: boolean, defaults to false. Enable verbose logging output when running the policy. maxchunks_to_compress: integer, defaults to 0 (no limit). The maximum number of s to add to the during a policy run. recompress: boolean, defaults to true. Recompress partially compressed s. compress_after: see add_columnstore_policy. compress_created_before: see add_columnstore_policy.
next_start	TIMESTAMPTZ	-	✖	The next time at which to run the job. The job can be paused by setting this value to infinity, and restarted with a value of now().
if_exists	BOOLEAN	false	✖	Set to true to issue a notice instead of an error if the job does not exist.
check_config	REGPROC	-	✖	A function that takes a single argument, the JSONB config structure. The function is expected to raise an error if the configuration is not valid, and return nothing otherwise. Can be used to validate the configuration when updating a job. Only functions, not procedures, are allowed as values for check_config.
fixed_schedule	BOOLEAN	false	✖	To enable fixed scheduled job runs, set to true.
initial_start	TIMESTAMPTZ	-	✖	Set the time when the fixed_schedule job run starts. For example, 19:10:25-07.
timezone	TEXT	UTC	✖	Address the 1-hour shift in start time when clocks change from Daylight Saving Time to Standard Time. For example, America/Sao_Paulo.
job_name	TEXT	NULL	✖	A name for the .

When a begins, the next_start parameter is set to infinity. This prevents the from attempting to be started again while it is running. When the completes, whether or not the job is successful, the parameter is automatically updated to the next computed start time.

Note that altering the next_start value is only effective for the next execution of the in case of fixed schedules. On the next execution, it will automatically return to the schedule.

Returns

Column	Type	Description
job_id	INTEGER	The ID of the being modified
schedule_interval	INTERVAL	The interval at which the runs. Defaults to 24 hours
max_runtime	INTERVAL	The maximum amount of time the is allowed to run by the background worker scheduler before it is stopped
max_retries	INTEGER	The number of times the is retried if it fails
retry_period	INTERVAL	The amount of time the scheduler waits between retries of the on failure
scheduled	BOOLEAN	Returns true if the is executed by the scheduler
config	JSONB	-specific configuration, passed to the function when it runs
next_start	TIMESTAMPTZ	The next time to run the
check_config	TEXT	The function used to validate updated configurations
fixed_schedule	BOOLEAN	Whether the uses a fixed schedule
initial_start	TIMESTAMPTZ	The time when the fixed schedule run starts
timezone	TEXT	The timezone for the schedule
application_name	NAME	The application name for the