Continuous Aggregate Refresh Policy not refreshing

TimescaleDB and PostgreSQL Installation, Configuration, and Errors
,
1

Hi,
a refresh policy (maybe also multiple) for a continuous aggregate has stopped working out of nowhere.

Postgres: PostgreSQL 14.6
Timescale: 2.9.3
Docker image: timescale/timescaledb:2.9.3-pg14

Database logs (it runs it but doesn’t refresh any data):

2023-08-10 12:20:54.887 UTC [1] LOG:  starting PostgreSQL 14.6 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r4) 12.2.1 20220924, 64-bit
2023-08-10 12:20:54.887 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-08-10 12:20:54.887 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2023-08-10 12:20:54.901 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-08-10 12:20:54.945 UTC [22] FATAL:  the database system is starting up
2023-08-10 12:20:54.946 UTC [21] LOG:  database system was shut down at 2023-08-10 12:19:49 UTC
2023-08-10 12:20:54.954 UTC [23] FATAL:  the database system is starting up
2023-08-10 12:20:54.964 UTC [24] FATAL:  the database system is starting up
2023-08-10 12:20:54.970 UTC [25] FATAL:  the database system is starting up
2023-08-10 12:20:54.978 UTC [26] FATAL:  the database system is starting up
2023-08-10 12:20:54.982 UTC [1] LOG:  database system is ready to accept connections
2023-08-10 12:20:54.984 UTC [32] LOG:  TimescaleDB background worker launcher connected to shared catalogs
2023-08-10 12:23:06.128 UTC [1406] LOG:  refreshing continuous aggregate "measurements_5m" in window [ 2023-08-10 12:10:00+00, 2023-08-10 12:15:00+00 ]
2023-08-10 12:28:06.189 UTC [3294] LOG:  refreshing continuous aggregate "measurements_5m" in window [ 2023-08-10 12:15:00+00, 2023-08-10 12:20:00+00 ]
2023-08-10 12:33:08.769 UTC [5531] LOG:  refreshing continuous aggregate "measurements_5m" in window [ 2023-08-10 12:20:00+00, 2023-08-10 12:25:00+00 ]

Continuous aggregate:

-[ RECORD 4 ]---------------------+--------------------------------------------------------------------------------------------
hypertable_schema                 | public
hypertable_name                   | measurements
view_schema                       | public
view_name                         | measurements_5m
view_owner                        | db
materialized_only                 | t
compression_enabled               | t
materialization_hypertable_schema | _timescaledb_internal
materialization_hypertable_name   | _materialized_hypertable_990
view_definition                   |  SELECT measurements.variable,                                                             +
                                  |     time_bucket('00:05:00'::interval, measurements."time") AS bucket,                      +
                                  |     avg(measurements.value) AS avg,                                                        +
                                  |     min(measurements.value) AS min,                                                        +
                                  |     max(measurements.value) AS max,                                                        +
                                  |     first(measurements.value, measurements."time") AS first,                               +
                                  |     last(measurements.value, measurements."time") AS last,                                 +
                                  |     count(measurements.value) AS count                                                     +
                                  |    FROM measurements                                                                       +
                                  |   GROUP BY measurements.variable, (time_bucket('00:05:00'::interval, measurements."time"));
finalized                         | t

Job

select * from timescaledb_information.job_stats where job_id = 1011;
-[ RECORD 1 ]----------+------------------------------
hypertable_schema      | _timescaledb_internal
hypertable_name        | _materialized_hypertable_990
job_id                 | 1011
last_run_started_at    | 2023-08-10 12:38:08.783927+00
last_successful_finish | 2023-08-10 12:38:09.278464+00
last_run_status        | Success
job_status             | Scheduled
last_run_duration      | 00:00:00.494537
next_start             | 2023-08-10 12:43:09.278464+00
total_runs             | 12
total_successes        | 12
total_failures         | 0

I tried:

  • Restarting postgres (no success)
  • Running SELECT _timescaledb_internal.start_background_workers(); (no success).

When I refresh manually, e.g.: CALL refresh_continuous_aggregate('measurements_5m', '2023-08-10 11:45:19', '2023-08-10 12:41:27'); then the continuous aggregate gets “correctly filled up”/refreshed.

thank you

2

Here is an example. The continuous aggregate is outdated, but the jobs has a last run that should have obviously worked, but it didn’t. After that I refresh it manually and then it’s up to date again. I get no logs, no warnings, no nothing. Very strange.

XXX=# select * from measurements_5m where bucket >= now () - interval '30 minute' order by bucket desc limit 1;
-[ RECORD 1 ]--------------------
variable | 88
bucket   | 2023-08-10 12:40:00+00
avg      | 0.6899999976158142
min      | 0.69
max      | 0.69
first    | 0.69
last     | 0.69
count    | 5

XXX=# select * from timescaledb_information.job_stats where job_id = 1011;
-[ RECORD 1 ]----------+------------------------------
hypertable_schema      | _timescaledb_internal
hypertable_name        | _materialized_hypertable_990
job_id                 | 1011
last_run_started_at    | 2023-08-10 12:53:09.3561+00
last_successful_finish | 2023-08-10 12:53:09.377891+00
last_run_status        | Success
job_status             | Scheduled
last_run_duration      | 00:00:00.021791
next_start             | 2023-08-10 12:58:09.377891+00
total_runs             | 15
total_successes        | 15
total_failures         | 0

XXX=# CALL refresh_continuous_aggregate('measurements_5m', '2023-08-10 11:45:19', '2023-08-10 12:55:27');
CALL
XXX=# select * from measurements_5m where bucket >= now () - interval '30 minute' order by bucket desc limit 1;
-[ RECORD 1 ]--------------------
variable | 88
bucket   | 2023-08-10 12:50:00+00
avg      | 0.6899999976158142
min      | 0.69
max      | 0.69
first    | 0.69
last     | 0.69
count    | 5
3

Upgraded to the newest version (timescale/timescaledb/2.11.1-pg14), but still the same problem, only manually refreshing works.

4

Hi @vnts, thanks for reporting. It seems a real issue. Please, create an issue in the official github and share a minimal POC proving it.