timescaledb_information.jobs

Shows information about all jobs registered with the automation framework.

Available Columns

NameTypeDescription
job_idINTEGERThe id of the background job
application_nameTEXTName of the policy or user defined action
schedule_intervalINTERVALThe interval at which the job runs
max_runtimeINTERVALThe maximum amount of time the job will be allowed to run by the background worker scheduler before it is stopped
max_retriesINTEGERThe number of times the job will be retried should it fail
retry_periodINTERVALThe amount of time the scheduler will wait between retries of the job on failure
proc_schemaTEXTSchema name of the function or procedure executed by the job
proc_nameTEXTName of the function or procedure executed by the job
ownerTEXTOwner of the job
scheduledBOOLEAN
configJSONB
next_startTIMESTAMP WITH TIME ZONE
hypertable_schemaTEXTSchema name of the hypertable. NULL, if this is a user defined action.
hypertable_nameTEXTTable name of the hypertable. NULL, if this is a user defined action.

Sample Usage

Get information about jobs.

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

Find all jobs related to compression policies.

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

Find jobs that are executed by user defined actions.

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