Create and manage custom jobs

Jobs are custom functions or procedures that TimescaleDB runs on your schedule. The sections below show how to register one, test it, and change or remove it safely.

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 PostgreSQL function or procedure that you want your database to execute, then set it up to run on a schedule.

1. Define a function or procedure

   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.

2. Call the function to validate

   select reindex_mytable();

   The result looks like this:

    reindex_mytable
   -----------------
   
   (1 row)

3. 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.

4. 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.

1. Set the minimum log level to DEBUG1

   SET client_min_messages TO DEBUG1;

2. 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.

1. 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"}');

2. Turn off job scheduling

   To turn off automatic scheduling of a job, call alter_job and set scheduled to 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);

3. Re-enable automatic scheduling

   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);

4. Delete a job with delete_job

   For example, to delete a job with ID 1000:

   SELECT delete_job(1000);