Create and manage jobs

Tiger Cloud: Performance, Scale, Enterprise, Free

Self-hosted products

MST

Jobs in TimescaleDB are custom functions or procedures that run on a schedule that you define. This page explains how to create, test, alter, and delete a job.

Prerequisites

To follow the procedure on this page you need to:

Create a target Tiger Cloud service.
This procedure also works for self-hosted TimescaleDB.

Create a job

To create a job, create a function or procedure that you want your database to execute, then set it up to run on a schedule.

Define a function or procedure in the language of your choice

Wrap it in a CREATE statement:


CREATE FUNCTION <function_name> (job_id INT DEFAULT NULL, config JSONB DEFAULT NULL)
RETURNS VOID
DECLARE
    <declaration>;
BEGIN
    <function_body>;
END;
$<variable_name>$ LANGUAGE <language>;

For example, to create a function that reindexes a table within your database:


CREATE FUNCTION reindex_mytable(job_id INT DEFAULT NULL, config JSONB DEFAULT NULL)
RETURNS VOID
AS $$
BEGIN
   REINDEX TABLE mytable;
END;
$$ LANGUAGE plpgsql;

job_id and config are required arguments in the function signature. This returns CREATE FUNCTION to indicate that the function has successfully been created.

Call the function to validate
For example:
```
select reindex_mytable();
```
The result looks like this:
```
reindex_mytable
-----------------
 
(1 row)
```
Register your job with add_job
Pass the name of your job, the schedule you want it to run on, and the content of your config. For the config value, if you don't need any special configuration parameters, set to NULL. For example, to run the reindex_mytable function every hour:
```
SELECT add_job('reindex_mytable', '1h', config => NULL);
```
The call returns a job_id and stores it along with config in the TimescaleDB catalog.
The job runs on the schedule you set. You can also run it manually with run_job passing job_id. When the job runs, job_id and config are passed as arguments.

Validate the job

List all currently registered jobs with timescaledb_information.jobs:


SELECT * FROM timescaledb_information.jobs;

The result looks like this:


job_id |      application_name      | schedule_interval | max_runtime | max_retries | retry_period |      proc_schema      |    proc_name     |   owner   | scheduled |         config         |          next_start           | hypertable_schema | hypertable_name
--------+----------------------------+-------------------+-------------+-------------+--------------+-----------------------+------------------+-----------+-----------+------------------------+-------------------------------+-------------------+-----------------
1 | Telemetry Reporter [1]     | 24:00:00          | 00:01:40    |          -1 | 01:00:00     | _timescaledb_internal | policy_telemetry | postgres  | t         |                        | 2022-08-18 06:26:39.524065+00 |                   |
1000 | User-Defined Action [1000] | 01:00:00          | 00:00:00    |          -1 | 00:05:00     | public                | reindex_mytable  | tsdbadmin | t         |                        | 2022-08-17 07:17:24.831698+00 |                   |
(2 rows)

Test and debug a job

To debug a job, increase the log level and run the job manually with run_job in the foreground. Because run_job is a stored procedure and not a function, run it with CALL instead of SELECT.

Set the minimum log level to DEBUG1
```
SET client_min_messages TO DEBUG1;
```
Run the job
Replace 1000 with your job_id:
```
CALL run_job(1000);
```

Alter and delete a job

Alter an existing job with alter_job. You can change both the config and the schedule on which the job runs.

Change a job's config
To replace the entire JSON config for a job, call alter_job with a new config object. For example, replace the JSON config for a job with ID 1000:
```
SELECT alter_job(1000, config => '{"hypertable":"metrics"}');
```
Turn off job scheduling
To turn off automatic scheduling of a job, call alter_job and set scheduledto false. You can still run the job manually with run_job. For example, turn off the scheduling for a job with ID 1000:
```
SELECT alter_job(1000, scheduled => false);
```
Re-enable automatic scheduling of a job
To re-enable automatic scheduling of a job, call alter_job and set scheduled to true. For example, re-enable scheduling for a job with ID 1000:
```
SELECT alter_job(1000, scheduled => true);
```
Delete a job with delete_job
For example, to delete a job with ID 1000:
```
SELECT delete_job(1000);
```

Create and manage jobs

Prerequisites

Create a job

Test and debug a job

Alter and delete a job

Related Content