timescaledb_information.jobs

Shows information about all jobs registered with the automation framework.

Available Columns

NameDescription
job_id(INTEGER) The id of the background job
application_name(NAME) Name of the policy or user defined action
schedule_interval(INTERVAL) The interval at which the job runs
max_runtime(INTERVAL) The maximum amount of time the job will be allowed to run by the background worker scheduler before it is stopped
max_retries(INTEGER) The number of times the job will be retried should it fail
retry_period(INTERVAL) The amount of time the scheduler will wait between retries of the job on failure
proc_schema(NAME) Schema name of the function or procedure executed by the job
proc_name(NAME) Name of the function or procedure executed by the job
owner(NAME) Owner of the job
scheduled(BOOLEAN)
config(JSONB)
next_start(TIMESTAMP WITH TIME ZONE)
hypertable_schema(NAME) Schema name of the hypertable. NULL, if this is a user defined action.
hypertable_name(NAME) Table name of the hypertable. NULL, if this is a user defined action.

Sample Usage

Get information about jobs.

  2. SELECT * FROM timescaledb_information.jobs;
  3. --This shows a job associated with the refresh policy for continuous aggregates
  4. job_id            | 1001
  5. application_name  | Refresh Continuous Aggregate Policy [1001]
  6. schedule_interval | 01:00:00
  7. max_runtime       | 00:00:00
  8. max_retries       | -1
  9. retry_period      | 01:00:00
  10. proc_schema       | _timescaledb_internal
  11. proc_name         | policy_refresh_continuous_aggregate
  12. owner             | postgres
  13. scheduled         | t
  14. config            | {"start_offset": "20 days", "end_offset": "10 
  15. days", "mat_hypertable_id": 2}
  16. next_start        | 2020-10-02 12:38:07.014042-04
  17. hypertable_schema | _timescaledb_internal
  18. hypertable_name   | _materialized_hypertable_2

Find all jobs related to compression policies.

  1. SELECT * FROM timescaledb_information.jobs where application_name like 'Compression%';
  3. -[ RECORD 1 ]-----+--------------------------------------------------
  4. job_id            | 1002
  5. application_name  | Compression Policy [1002]
  6. schedule_interval | 15 days 12:00:00
  7. max_runtime       | 00:00:00
  8. max_retries       | -1
  9. retry_period      | 01:00:00
  10. proc_schema       | _timescaledb_internal
  11. proc_name         | policy_compression
  12. owner             | postgres
  13. scheduled         | t
  14. config            | {"hypertable_id": 3, "compress_after": "60 days"}
  15. next_start        | 2020-10-18 01:31:40.493764-04
  16. hypertable_schema | public
  17. hypertable_name   | conditions

Find jobs that are executed by user defined actions.

  1. SELECT * FROM timescaledb_information.jobs where application_name like 'User-Define%';
  3. -[ RECORD 1 ]-----+------------------------------
  4. job_id            | 1003
  5. application_name  | User-Defined Action [1003]
  6. schedule_interval | 01:00:00
  7. max_runtime       | 00:00:00
  8. max_retries       | -1
  9. retry_period      | 00:05:00
  10. proc_schema       | public
  11. proc_name         | custom_aggregation_func
  12. owner             | postgres
  13. scheduled         | t
  14. config            | {"type": "function"}
  15. next_start        | 2020-10-02 14:45:33.339885-04
  16. hypertable_schema | 
  17. hypertable_name   | 
  18. -[ RECORD 2 ]-----+------------------------------
  19. job_id            | 1004
  20. application_name  | User-Defined Action [1004]
  21. schedule_interval | 01:00:00
  22. max_runtime       | 00:00:00
  23. max_retries       | -1
  24. retry_period      | 00:05:00
  25. proc_schema       | public
  26. proc_name         | custom_retention_func
  27. owner             | postgres
  28. scheduled         | t
  29. config            | {"type": "function"}
  30. next_start        | 2020-10-02 14:45:33.353733-04
  31. hypertable_schema | 
  32. hypertable_name   |