timescaledb_information.jobs

Shows information about all jobs registered with the automation framework.

Arguments

Name Type Description
job_id INTEGER The ID of the background job
application_name TEXT Name of the policy or user defined action
schedule_interval INTERVAL The interval at which the job runs. Defaults to 24 hours
max_runtime INTERVAL The maximum amount of time the job is allowed to run by the background worker scheduler before it is stopped
max_retries INTEGER The number of times the job is retried if it fails
retry_period INTERVAL The amount of time the scheduler waits between retries of the job on failure
proc_schema TEXT Schema name of the function or procedure executed by the job
proc_name TEXT Name of the function or procedure executed by the job
owner TEXT Owner of the job
scheduled BOOLEAN Set to true to run the job automatically
config JSONB Configuration passed to the function specified by proc_name at execution time
next_start TIMESTAMP WITH TIME ZONE Next start time for the job, if it is scheduled to run automatically
hypertable_schema TEXT Schema name of the hypertable. Set to NULL for user-defined action
hypertable_name TEXT Table name of the hypertable. Set to NULL for user-defined action
check_schema TEXT Schema name of the optional configuration validation function, set when the job is created or updated
check_name TEXT Name 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