timescaledb_information.jobs

Shows information about all jobs registered with the automation framework.

Arguments

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. Defaults to 24 hours
max_runtimeINTERVALThe maximum amount of time the job is allowed to run by the background worker scheduler before it is stopped
max_retriesINTEGERThe number of times the job is retried if it fails
retry_periodINTERVALThe amount of time the scheduler waits 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
scheduledBOOLEANSet to true to run the job automatically
configJSONBConfiguration passed to the function specified by proc_name at execution time
next_startTIMESTAMP WITH TIME ZONENext start time for the job, if it is scheduled to run automatically
hypertable_schemaTEXTSchema name of the hypertable. Set to NULL for user-defined action
hypertable_nameTEXTTable name of the hypertable. Set to NULL for user-defined action
check_schemaTEXTSchema name of the optional configuration validation function, set when the job is created or updated
check_nameTEXTName of the optional configuration validation function, set when the job is created or updated

Sample use

Shows a job associated with the refresh policy for continuous aggregates:

  2. SELECT * FROM timescaledb_information.jobs;
  4. job_id            | 1001
  6. application_name  | Refresh Continuous Aggregate Policy [1001]
  8. schedule_interval | 01:00:00
  10. max_runtime       | 00:00:00
  12. max_retries       | -1
  14. retry_period      | 01:00:00
  16. proc_schema       | _timescaledb_internal
  18. proc_name         | policy_refresh_continuous_aggregate
  20. owner             | postgres
  22. scheduled         | t
  24. config            | {"start_offset": "20 days", "end_offset": "10
  26. days", "mat_hypertable_id": 2}
  28. next_start        | 2020-10-02 12:38:07.014042-04
  30. hypertable_schema | _timescaledb_internal
  32. hypertable_name   | _materialized_hypertable_2
  34. check_schema      | _timescaledb_internal
  36. check_name       | policy_refresh_continuous_aggregate_check

Find all jobs related to compression policies:

  2. SELECT * FROM timescaledb_information.jobs where application_name like 'Compression%';
  4. -[ RECORD 1 ]-----+--------------------------------------------------
  6. job_id            | 1002
  8. application_name  | Compression Policy [1002]
  10. schedule_interval | 15 days 12:00:00
  12. max_runtime       | 00:00:00
  14. max_retries       | -1
  16. retry_period      | 01:00:00
  18. proc_schema       | _timescaledb_internal
  20. proc_name         | policy_compression
  22. owner             | postgres
  24. scheduled         | t
  26. config            | {"hypertable_id": 3, "compress_after": "60 days"}
  28. next_start        | 2020-10-18 01:31:40.493764-04
  30. hypertable_schema | public
  32. hypertable_name   | conditions
  34. check_schema      | _timescaledb_internal
  36. check_name        | policy_compression_check

Find jobs that are run by user defined actions:

  2. SELECT * FROM timescaledb_information.jobs where application_name like 'User-Define%';
  4. -[ RECORD 1 ]-----+------------------------------
  6. job_id            | 1003
  8. application_name  | User-Defined Action [1003]
  10. schedule_interval | 01:00:00
  12. max_runtime       | 00:00:00
  14. max_retries       | -1
  16. retry_period      | 00:05:00
  18. proc_schema       | public
  20. proc_name         | custom_aggregation_func
  22. owner             | postgres
  24. scheduled         | t
  26. config            | {"type": "function"}
  28. next_start        | 2020-10-02 14:45:33.339885-04
  30. hypertable_schema |
  32. hypertable_name   |
  34. check_schema      | NULL
  36. check_name        | NULL
  38. -[ RECORD 2 ]-----+------------------------------
  40. job_id            | 1004
  42. application_name  | User-Defined Action [1004]
  44. schedule_interval | 01:00:00
  46. max_runtime       | 00:00:00
  48. max_retries       | -1
  50. retry_period      | 00:05:00
  52. proc_schema       | public
  54. proc_name         | custom_retention_func
  56. owner             | postgres
  58. scheduled         | t
  60. config            | {"type": "function"}
  62. next_start        | 2020-10-02 14:45:33.353733-04
  64. hypertable_schema |
  66. hypertable_name   |
  68. check_schema      | NULL
  70. check_name        | NULL