How to determine whether a continuous aggregation has completed computation and storage?

TimescaleDB and PostgreSQL
1

How to determine whether a continuous aggregation has completed computation and storage?

Create a continuous aggregation:

CREATE MATERIALIZED VIEW WATER_DOWNSAMPLING_HOUR
WITH (TIMESCALEDB. CONTINUOUS,
TIMESCALEDB.MATERIALIZED_ONLY = FALSE)
AS
SELECT TIME_BUCKET('1 hour'::INTERVAL, "time") AS RECODE_TIME,
METER_CODE,
MAX(DQLJLCC)                            AS MAX_READING
FROM WATER_METER1
GROUP BY TIME_BUCKET('1 hour'::INTERVAL, "time"), METER_CODE
WITH NO DATA;

“With NO DATA” is used here.
This view can query results, but it is very time-consuming.
Later, I realized that it might not have completed the calculation and storage, so I performed a manual refresh:

call refresh_continuous_aggregate('water_downsampling_hour', '2023-01-01', '2024-01-01');

Its execution time has become very short.
So how to determine whether a continuous aggregation has completed computation and implementation?

2

Hi @NieYuan , if you create a refresh policy, you can use the timescaledb_information.job_stats to check the jobs running and you can see when it’s completed. You can also check the timescaledb_information.invalidation_log to check pending fragments that needs to be reprocessed.