--- title: alter_job() | Tiger Data Docs description: Alter a job that is scheduled to run automatically --- Community Since [1.2.0](https://github.com/timescale/timescaledb/releases/tag/1.2.0) Jobs scheduled using the TimescaleDB automation framework run periodically in a background worker. You can change the schedule of these jobs with the `alter_job` function. To alter an existing job, refer to it by `job_id`. The `job_id` runs a given job, and its current schedule can be found in the `timescaledb_information.jobs` view, which lists information about every scheduled jobs, as well as in `timescaledb_information.job_stats`. The `job_stats` view also gives information about when each job was last run and other useful statistics for deciding what the new schedule should be. ### Calculate the next start on failure When a job run results in a runtime failure, the next start of the job 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 jobs with fixed schedules, the system ensures that if the next start ( calculated as specified), surpasses the next scheduled execution, the job is executed again at the next scheduled slot and not after that. This ensures that the job does not miss scheduled executions. There is a distinction between runtime failures that do not cause the job to crash and job crashes. In the event of a job crash, the next start calculation follows the same formula, but it is always at least 5 minutes after the job‘s last finish, to give an operator enough time to disable it before another crash. ## Samples - **Reschedule job 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` hypertable**: ``` SELECT alter_job(job_id, scheduled => false) FROM timescaledb_information.jobs WHERE proc_name = 'policy_compression' AND hypertable_name = 'conditions' ``` - **Reschedule continuous aggregate job 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 columnstore policy**: You can pause and restart a columnstore policy, change how often the policy runs and the job 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 hypertable the policy is acting on, you must [remove the columnstore policy](/docs/reference/timescaledb/hypercore/remove_columnstore_policy/index.md) and [add a new one](/docs/reference/timescaledb/hypercore/add_columnstore_policy/index.md). ## Arguments The syntax is: ``` SELECT alter_job( job_id = , schedule_interval = , max_runtime = , max_retries = , retry_period = , scheduled = true | false, config = '', next_start = , if_exists = true | false, check_config = '', fixed_schedule = true | false, initial_start = , timezone = '' ); ``` | Name | Type | Default | Required | Description | | ------------------- | ----------- | -------- | -------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `job_id` | INTEGER | - | ✔ | The ID of the policy job 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 | - | ✖ | Job-specific configuration, passed to the function when it runs. This includes:* `verbose_log`: boolean, defaults to `false`. Enable verbose logging output when running the columnstore policy. * `maxchunks_to_compress`: integer, defaults to `0` (no limit). The maximum number of chunks to add to the columnstore during a policy run. * `recompress`: boolean, defaults to `true`. Recompress partially compressed chunks. * `compress_after`: see [`add_columnstore_policy`](/docs/reference/timescaledb/hypercore/add_columnstore_policy#arguments/index.md). * `compress_created_before`: see [`add_columnstore_policy`](/docs/reference/timescaledb/hypercore/add_columnstore_policy#arguments/index.md). | | `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](https://en.wikipedia.org/wiki/Daylight_saving_time). For example, `America/Sao_Paulo`. | | `job_name` | TEXT | `NULL` | ✖ | A name for the job. | When a job begins, the `next_start` parameter is set to `infinity`. This prevents the job from attempting to be started again while it is running. When the job 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 job 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 job being modified | | `schedule_interval` | INTERVAL | The interval at which the job runs. Defaults to 24 hours | | `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 | Returns `true` if the job is executed by the TimescaleDB scheduler | | `config` | JSONB | Job-specific configuration, passed to the function when it runs | | `next_start` | TIMESTAMPTZ | The next time to run the job | | `check_config` | TEXT | The function used to validate updated job configurations | | `fixed_schedule` | BOOLEAN | Whether the job uses a fixed schedule | | `initial_start` | TIMESTAMPTZ | The time when the fixed schedule job run starts | | `timezone` | TEXT | The timezone for the job schedule | | `application_name` | NAME | The application name for the job |