Hi everyone!
I started to be using the marvelous continuous aggregates recently and have trouble understanding how the retention policy of continuous aggregates works. I thought someone here could give me a hint 
For instance, I have a continuous aggregate with a retention policy which runs everyday (and outputs a “success” status), but I can still see aggregated data older than the policy.
Here is how the aggregate was initially created :
CREATE MATERIALIZED VIEW interfaces_to_load
WITH (timescaledb.continuous, timescaledb.materialized_only=true) AS
SELECT time_bucket(INTERVAL '1 day', created_at) as time,
d.product_id as product_id,
d.version as version
FROM document d WHERE d.for_training is not TRUE AND d.api_type = 'constructed'
GROUP BY time, d.product_id, d.version
WITH NO DATA;
The refresh and retention job informations:
| hypertabel_name | _materialized_hypertable_4 | _materialized_hypertable_4 |
|---|---|---|
| job_id | 1007 | 1000 |
| config | {“end_offset”: “00:00:00”, “start_offset”: “00:30:00”, “mat_hypertable_id”: 4} | {“drop_after”: “7 days”, “hypertable_id”: 4} |
| schedule_interval | 0 years 0 mons 0 days 0 hours 15 mins 0.0 secs | 0 years 0 mons 1 days 0 hours 0 mins 0.0 secs |
| job_status | Scheduled | Scheduled |
| last_run_status | Success | Success |
| last_run_started_at | 2022-06-07 14:37:21.070739 +00:00 | 2022-06-06 15:29:09.892205 +00:00 |
| next_start | 2022-06-07 14:52:21.143838 +00:00 | 2022-06-07 15:29:09.899479 +00:00 |
| total_runs | 1463 | 34 |
| total_successes | 1463 | 34 |
| total_failures | 0 | 0 |
An sample of my db, where old data is still present in the aggregate:
| time | product_id |
|---|---|
| 2022-04-28 00:00:00 | 10b8748b-5aa2-4c7c-807d-ad216ef8bd |
| 2022-04-28 00:00:00 | a5325ab4-bcac-4e4b-8296-bb1b627380 |
| 2022-04-28 00:00:00 | a8a5865f-7522-486d-823c-976188891a |
| 2022-04-28 00:00:00 | 6d607ca0-3287-4d7b-932a-2f5e28ea9b |
Does someone know why the aggregated data older than 7 days is not dropped?
Thanks a lot!