[SOLVED] Revert dropping chunks on a continuous aggregate

Revert dropping chunks on a continuous aggregate

I have a hypertable: measurements, and materialized view: measurements_hourly. I accidentally dropped chunks from meaurement_hourly instead measurements by running:

SELECT drop_chunks('measurements_hourly', INTERVAL '1 month');

So instead dropping chunks from a row table, I dropped it from aggregation table. Now, I want to fix my mistake and recalculate measurements_hourly aggregation table. To do this I ran:

CALL refresh_continuous_aggregate('meaurement_hourly', NULL, NULL);

but in response I got: continuous aggregate "measurements_hourly" is already up-to-date. This is not true, because there are rows in measurements table from 1 year ago, and measurements_hourly have only records from last 1 month. Besides, how can it be up-to-date, if I just dropped some chunks?

I found out, that If I change existing row from measurements table, or add a new one with an old date, it is successfully recalculated by manually calling refresh_continuous_aggregate. So how can I do a refresh WITHOUT modyfing existing data?

FYI: I don't using any other retention policies, and my timescaledb version is: 2.14.2.

Solution

The simplest way is touching the data to generate a new "invalidation log" about it:

update hypertable 
set time=time 
where time 
between <your-boundaries-that-needs-update>

You can learn more about the invalidation logs here: https://youtube.com/clip/Ugkx6RXs8NTHZOgR22L4DH5LBdM3i95w4Tty?si=yZYM9oC27d1Kyl8M