Shows information about the jobs run by the automation framework. This includes jobs set up for user-defined actions, and jobs run by policies created to manage data retention, continuous aggregates, compression, and other automation policies. For more information about automation policies, see User-Defined Actions.
| Name | Type | Description |
|---|---|---|
id | INTEGER | The sequencial ID to identify the job execution |
job_id | INTEGER | The ID of the background job created to implement the policy |
succeeded | BOOLEAN | TRUE when the job ran successfully, FALSE for failed executions |
proc_schema | TEXT | The schema name of the function or procedure executed by the job |
proc_name | TEXT | The name of the function or procedure executed by the job |
pid | INTEGER | The process ID of the background worker executing the job. This is NULL in the case of a job crash |
start_time | TIMESTAMP WITH TIME ZONE | The time the job started |
finish_time | TIMESTAMP WITH TIME ZONE | The time when the error was reported |
config | JSONB | The job configuration at the moment of execution |
sqlerrcode | TEXT | The error code associated with this error, if any. See the official PostgreSQL documentation for a full list of error codes |
err_message | TEXT | The detailed error message |
To retrieve information about recent jobs:
SELECT job_id, pid, proc_schema, proc_name, succeeded, config, sqlerrcode, err_messageFROM timescaledb_information.job_historyORDER BY id, job_id;job_id | pid | proc_schema | proc_name | succeeded | config | sqlerrcode | err_message--------+---------+-------------+------------------+-----------+------------+------------+------------------1001 | 1779278 | public | custom_job_error | f | | 22012 | division by zero1000 | 1779407 | public | custom_job_ok | t | | |1001 | 1779408 | public | custom_job_error | f | | 22012 | division by zero1000 | 1779467 | public | custom_job_ok | t | {"foo": 1} | |1001 | 1779468 | public | custom_job_error | f | {"bar": 1} | 22012 | division by zero(5 rows)
The timescaledb_information.job_history informational view is defined on top
of the _timescaledb_internal.bgw_job_stat_history table in the internal schema. To
prevent this table from growing too large, the
Job History Log Retention Policy [3] system background job is enabled by default,
with this configuration:
job_id | 3application_name | Job History Log Retention Policy [3]schedule_interval | 1 monmax_runtime | 01:00:00max_retries | -1retry_period | 01:00:00proc_schema | _timescaledb_functionsproc_name | policy_job_stat_history_retentionowner | owner must be a user with WRITE privilege on the table `_timescaledb_internal.bgw_job_stat_history`scheduled | tfixed_schedule | tconfig | {"drop_after": "1 month"}next_start | 2024-06-01 01:00:00+00initial_start | 2000-01-01 00:00:00+00hypertable_schema |hypertable_name |check_schema | _timescaledb_functionscheck_name | policy_job_stat_history_retention_check
On Timescale and Managed Service for TimescaleDB, the owner of the job history
retention job is tsdbadmin. In an on-premise installation, the owner of the
job is the same as the extension owner.
The owner of the retention job can alter it and delete it.
For example, the owner can change the retention interval like this:
SELECT alter_job(id,config:=jsonb_set(config,'{drop_after}', '"2 weeks"')) FROM _timescaledb_config.bgw_job WHERE id = 3;
Keywords
Found an issue on this page?Report an issue or Edit this page in GitHub.