Clear invalidation log when there is nothing to refresh

[mkindahl]

If a continuous aggregate is created an invalidation entry will be added to the invalidation log to ensure that the continuous aggregate is refreshed. However, when refreshing the full range of the continuous aggregate, there are still entries remaining in the invalidation log that cannot be removed in any way except explicitly operating on the table.

The behavior I expected where that the invalidation log is empty if and only if there are no outstanding invalidation to process.

postgres=# CREATE TABLE conditions (                                                                
      timec        TIMESTAMPTZ       NOT NULL,
      location    TEXT              NOT NULL,
      temperature integer  NULL,
      humidity    DOUBLE PRECISION  NULL,
      timemeasure TIMESTAMPTZ,
      timeinterval INTERVAL
);
CREATE TABLE
postgres=# SELECT create_hypertable('conditions', 'timec', chunk_time_interval => INTERVAL '1 day');
    create_hypertable     
--------------------------
 (15,public,conditions,t)
(1 row)

postgres=# CREATE MATERIALIZED VIEW conditions_view                                                 
  WITH ( timescaledb.continuous, timescaledb.materialized_only=true)
AS SELECT time_bucket('1week', timec), AVG(temperature)
    FROM conditions GROUP BY 1;
CREATE MATERIALIZED VIEW
postgres=# select * from _timescaledb_catalog.continuous_aggs_materialization_invalidation_log ;    
 materialization_id | modification_time | lowest_modified_value | greatest_modified_value 
--------------------+-------------------+-----------------------+-------------------------
                  2 |   653033325780822 |  -9223372036854775808 |     9223372036854775807
(1 row)
postgres=# CALL refresh_continuous_aggregate('conditions_view', NULL, NULL);                                  
CALL
postgres=# select * from _timescaledb_catalog.continuous_aggs_materialization_invalidation_log ;
 materialization_id | modification_time | lowest_modified_value | greatest_modified_value 
--------------------+-------------------+-----------------------+-------------------------
                  2 |   653033325780822 |  -9223372036854775808 |     -210866803200000001
                  2 |   653033325780822 |   9223371331200000000 |     9223372036854775807
(2 rows)

[NunoFilipeSantos]

@mkindahl is this still relevant?

[mkindahl]

@mkindahl is this still relevant?

It is still relevant, but it can be solved in several different ways.

The key is that there should be a view or log to investigate that will give a clear indication for what parts of the continuous aggregate that has outstanding invalidation, regardless of whether they are in the refresh window or not.