[Bug]: there is only policy_telemetry in timescaledb_information.job_history

[pgloader]

What type of bug is this?

Other

What subsystems and features are affected?

Other

What happened?

There is only policy_telemetry in timescaledb_information.job_history. refresh_continuous_aggregate jobs didn't appear in the history view

TimescaleDB version affected

2.5.12

PostgreSQL version used

14.5

What operating system did you use?

Red Hat Enterprise Linux release 8.2 (Ootpa)

What installation method did you use?

Source

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

# select * from timescaledb_information.job_history;
 id | job_id | succeeded |      proc_schema       |    proc_name     |   pid   |          start_time           |          finish_time          | config | sqlerrcode |             err_message
----+--------+-----------+------------------------+------------------+---------+-------------------------------+-------------------------------+--------+------------+-------------------------------------
  1 |      1 | f         | _timescaledb_functions | policy_telemetry | 1010670 | 2024-06-11 12:12:39.097899-04 | 2024-06-11 12:12:39.159925-04 |        |            | job crash detected, see server logs
  2 |      1 | f         | _timescaledb_functions | policy_telemetry | 1044003 | 2024-06-11 13:12:39.100308-04 | 2024-06-11 13:12:42.168173-04 |        |            | job crash detected, see server logs
  3 |      1 | f         | _timescaledb_functions | policy_telemetry | 1077300 | 2024-06-11 14:12:39.104991-04 | 2024-06-11 14:12:42.232221-04 |        |            | job crash detected, see server logs
  4 |      1 | f         | _timescaledb_functions | policy_telemetry | 1113751 | 2024-06-11 15:12:39.105129-04 | 2024-06-11 15:12:39.114045-04 |        |            | job crash detected, see server logs
(4 rows)

# select * from timescaledb_information.job_stats;
   hypertable_schema   |       hypertable_name       | job_id |      last_run_started_at      |    last_successful_finish     | last_run_status | job_status | last_run_duration |          next_start
     | total_runs | total_successes | total_failures
-----------------------+-----------------------------+--------+-------------------------------+-------------------------------+-----------------+------------+-------------------+--------------------------
-----+------------+-----------------+----------------
 _timescaledb_internal | _materialized_hypertable_2  |   1000 | 2024-06-13 16:03:16.19217-04  | 2024-06-13 16:03:16.207449-04 | Success         | Scheduled  | 00:00:00.015279   | 2024-06-13 16:33:16.20744
9-04 |         78 |              78 |              0
 _timescaledb_internal | _materialized_hypertable_3  |   1001 | 2024-06-13 12:21:39.517465-04 | 2024-06-13 12:24:57.720115-04 | Success         | Scheduled  | 00:03:18.20265    | 2024-06-14 12:24:57.72011
5-04 |          3 |               3 |              0
 _timescaledb_internal | _materialized_hypertable_38 |   1002 | 2024-06-13 16:14:46.03692-04  | 2024-06-13 16:14:46.055608-04 | Success         | Scheduled  | 00:00:00.018688   | 2024-06-13 16:44:46.05560
8-04 |         98 |              98 |              0
 _timescaledb_internal | _materialized_hypertable_39 |   1003 | 2024-06-13 15:37:58.223756-04 | 2024-06-13 15:37:58.648071-04 | Success         | Scheduled  | 00:00:00.424315   | 2024-06-14 15:37:58.64807
1-04 |          3 |               3 |              0
 _timescaledb_internal | _materialized_hypertable_40 |   1004 | 2024-06-13 16:15:22.628147-04 | 2024-06-13 16:15:22.64759-04  | Success         | Scheduled  | 00:00:00.019443   | 2024-06-13 16:45:22.64759
-04  |         81 |              81 |              0
 _timescaledb_internal | _materialized_hypertable_41 |   1005 | 2024-06-13 15:38:23.290275-04 | 2024-06-13 15:41:23.101159-04 | Success         | Scheduled  | 00:02:59.810884   | 2024-06-14 15:41:23.10115
9-04 |          3 |               3 |              0
                       |                             |      1 | 2024-06-11 15:12:39.105046-04 | -infinity                     | Failed          | Scheduled  | 00:00:00.008924   | 2024-06-11 16:12:39.10504
6-04 |          4 |               0 |              4
                       |                             |      3 | 2024-06-11 12:12:39.098481-04 | 2024-06-11 12:12:39.108912-04 | Success         | Scheduled  | 00:00:00.010431   | 2024-06-30 19:00:00-04
     |          1 |               1 |              0
(8 rows)

How can we reproduce the bug?

select * from timescaledb_information.job_history;

[fabriziomello]

By default only failed execution are tracked. You need to turn on the GUC timescaledb.enable_job_execution_logging to record all executions

[pgloader]

Thanks. @fabriziomello

I see the rest of jobs in job_history. Is there a way to setup retention for job_history ?

[nikkhils]

@pgloader job_history is a view on top of _timescaledb_internal.bgw_job_stat_history which is a regular PG table. To prevent this table from growing too large, the Job History Log Retention Policy is enabled by default.

https://docs.timescale.com/api/latest/informational-views/job_history/#error-retention-policy

[nikkhils]

@pgloader I am closing this for now. Please re-open if you feel that anything else needs to be done here.