[Bug]: Duplicates in Continuous Aggregate + Overlapping Materialization Invalidation Log #9221
Description
What type of bug is this?
Unexpected error
What subsystems and features are affected?
Continuous aggregate
What happened?
Is it expected to have overlapping materialization invalidation logs? The reason for this issue is because I noticed that there are duplicates in my continuous aggregate even though the table it depends on doesn't have duplicates. A manual refresh on the continuous aggregate later on would remove the duplicates.
I saw recently Timescale enabled concurrent refresh, and under the hood it locks invalidation log rows. My speculation is since I have invalidation logs that are overlapping, that means I can have concurrent refreshes that can both go through even if they have overlapping time ranges, and this could lead to duplications. Here's a series of data from the Timescale tables.
continuous_aggs_materialization_invalidation_log:
data=> select to_timestamp(lowest_modified_value/1000000), to_timestamp(greatest_modified_value/1000000) from continuous_agg a join continuous_aggs_materialization_invalidation_log b on a.mat_hypertable_id = b.materialization_id where a.user_view_name = 'my_cagg' and lowest_modified_value > 0 order by 2,1;
to_timestamp | to_timestamp
------------------------+---------------------------------
2026-01-31 22:12:00+00 | 2026-01-31 22:12:59+00
2026-01-31 23:00:00+00 | 2026-02-01 06:00:59+00
2026-02-02 01:23:00+00 | 2026-02-02 01:23:59+00
2026-02-02 01:45:00+00 | 2026-02-02 01:55:59+00
2026-02-02 01:59:00+00 | 2026-02-02 01:59:59+00
2026-02-02 06:10:00+00 | 2026-02-02 06:10:59+00
2026-02-02 04:56:00+00 | 2026-02-03 00:59:59+00
2026-01-31 14:30:00+00 | 2026-02-03 05:59:59+00
2026-02-01 07:44:00+00 | 2026-02-03 05:59:59+00
2026-02-02 01:45:00+00 | 2026-02-03 05:59:59+00
2026-02-02 21:45:00+00 | 2026-02-03 05:59:59+00
2026-02-03 05:55:00+00 | 294247-01-10 04:00:53.999616+00
(12 rows)
continuous_aggs_materialization_ranges:
data=> select to_timestamp(lowest_modified_value/1000000), to_timestamp(greatest_modified_value/1000000) from continuous_agg a join continuous_aggs_materialization_ranges b on a.mat_hypertable_id = b.materialization_id where a.user_view_name = 'my_cagg' and lowest_modified_value > 0 order by 2,1;
to_timestamp | to_timestamp
------------------------+------------------------
2025-11-23 21:15:00+00 | 2025-11-23 21:20:00+00
2026-01-20 05:30:00+00 | 2026-01-21 04:30:00+00
2026-01-20 10:30:00+00 | 2026-01-21 09:30:00+00
2026-01-25 11:30:00+00 | 2026-01-26 10:30:00+00
2026-01-26 09:30:00+00 | 2026-01-27 08:30:00+00
2026-01-28 09:30:00+00 | 2026-01-29 08:30:00+00
2026-01-28 10:46:00+00 | 2026-01-29 08:45:00+00
(7 rows)
continuous_aggs_hypertable_invalidation_log of the hypertable that my cagg depends on.
data=> select to_timestamp(lowest_modified_value/1000000), to_timestamp(greatest_modified_value/1000000) from hypertable a join continuous_aggs_hypertable_invalidation_log b on a.id = b.hypertable_id where a.table_name = 'my_hypertable' and lowest_modified_value > 0 order by 2,1;
to_timestamp | to_timestamp
------------------------+------------------------
2026-01-31 22:13:00+00 | 2026-01-31 22:59:00+00
2026-01-31 23:56:00+00 | 2026-01-31 23:56:00+00
2026-01-31 23:00:00+00 | 2026-01-31 23:59:00+00
2026-01-31 23:57:00+00 | 2026-01-31 23:59:00+00
2026-02-01 00:00:00+00 | 2026-02-01 00:59:00+00
2026-02-01 00:00:00+00 | 2026-02-01 00:59:00+00
2026-02-01 01:00:00+00 | 2026-02-01 01:59:00+00
2026-02-01 01:00:00+00 | 2026-02-01 01:59:00+00
2026-02-01 02:00:00+00 | 2026-02-01 02:59:00+00
2026-02-01 02:00:00+00 | 2026-02-01 02:59:00+00
2026-02-01 03:00:00+00 | 2026-02-01 03:59:00+00
2026-02-01 03:00:00+00 | 2026-02-01 03:59:00+00
2026-02-01 04:00:00+00 | 2026-02-01 04:59:00+00
2026-02-01 04:00:00+00 | 2026-02-01 04:59:00+00
2026-02-01 05:00:00+00 | 2026-02-01 05:59:00+00
2026-02-01 05:00:00+00 | 2026-02-01 05:59:00+00
2026-02-01 06:00:00+00 | 2026-02-01 06:59:00+00
2026-02-01 06:00:00+00 | 2026-02-01 06:59:00+00
2026-02-01 07:00:00+00 | 2026-02-01 07:59:00+00
2026-02-01 07:00:00+00 | 2026-02-01 07:59:00+00
2026-02-01 08:00:00+00 | 2026-02-01 08:59:00+00
2026-02-01 08:00:00+00 | 2026-02-01 08:59:00+00
2026-02-01 09:00:00+00 | 2026-02-01 09:59:00+00
2026-02-01 09:00:00+00 | 2026-02-01 09:59:00+00
2026-02-01 10:00:00+00 | 2026-02-01 10:59:00+00
2026-02-01 10:00:00+00 | 2026-02-01 10:59:00+00
2026-02-01 11:00:00+00 | 2026-02-01 11:59:00+00
2026-02-01 11:00:00+00 | 2026-02-01 11:59:00+00
2026-02-01 12:00:00+00 | 2026-02-01 12:59:00+00
2026-02-01 12:00:00+00 | 2026-02-01 12:59:00+00
2026-02-01 13:00:00+00 | 2026-02-01 13:59:00+00
2026-02-01 13:00:00+00 | 2026-02-01 13:59:00+00
2026-02-01 14:00:00+00 | 2026-02-01 14:59:00+00
2026-02-01 14:00:00+00 | 2026-02-01 14:59:00+00
2026-02-01 15:00:00+00 | 2026-02-01 15:59:00+00
2026-02-01 15:00:00+00 | 2026-02-01 15:59:00+00
2026-02-01 16:00:00+00 | 2026-02-01 16:59:00+00
2026-02-01 16:00:00+00 | 2026-02-01 16:59:00+00
2026-02-01 17:00:00+00 | 2026-02-01 17:59:00+00
2026-02-01 17:00:00+00 | 2026-02-01 17:59:00+00
2026-02-01 18:00:00+00 | 2026-02-01 18:59:00+00
2026-02-01 18:00:00+00 | 2026-02-01 18:59:00+00
2026-02-01 19:00:00+00 | 2026-02-01 19:59:00+00
2026-02-01 19:00:00+00 | 2026-02-01 19:59:00+00
2026-02-01 20:00:00+00 | 2026-02-01 20:59:00+00
2026-02-01 20:00:00+00 | 2026-02-01 20:59:00+00
2026-02-01 21:00:00+00 | 2026-02-01 21:59:00+00
2026-02-01 21:00:00+00 | 2026-02-01 21:59:00+00
2026-02-01 22:00:00+00 | 2026-02-01 22:59:00+00
2026-02-01 22:00:00+00 | 2026-02-01 22:59:00+00
2026-02-01 23:00:00+00 | 2026-02-01 23:59:00+00
2026-02-01 23:00:00+00 | 2026-02-01 23:59:00+00
2026-02-02 00:00:00+00 | 2026-02-02 00:59:00+00
2026-02-02 00:00:00+00 | 2026-02-02 00:59:00+00
2026-02-02 01:00:00+00 | 2026-02-02 01:59:00+00
2026-02-02 01:00:00+00 | 2026-02-02 01:59:00+00
2026-02-02 01:24:00+00 | 2026-02-02 01:59:00+00
2026-02-02 02:00:00+00 | 2026-02-02 02:59:00+00
2026-02-02 02:00:00+00 | 2026-02-02 02:59:00+00
2026-02-02 02:00:00+00 | 2026-02-02 02:59:00+00
2026-02-02 03:00:00+00 | 2026-02-02 03:59:00+00
2026-02-02 03:00:00+00 | 2026-02-02 03:59:00+00
2026-02-02 03:00:00+00 | 2026-02-02 03:59:00+00
2026-02-02 04:00:00+00 | 2026-02-02 04:59:00+00
2026-02-02 04:00:00+00 | 2026-02-02 04:59:00+00
2026-02-02 04:00:00+00 | 2026-02-02 04:59:00+00
2026-02-02 05:00:00+00 | 2026-02-02 05:59:00+00
2026-02-02 05:00:00+00 | 2026-02-02 05:59:00+00
2026-02-02 05:00:00+00 | 2026-02-02 05:59:00+00
2026-02-02 06:00:00+00 | 2026-02-02 06:59:00+00
2026-02-02 06:00:00+00 | 2026-02-02 06:59:00+00
2026-02-02 06:00:00+00 | 2026-02-02 06:59:00+00
2026-02-02 07:00:00+00 | 2026-02-02 07:59:00+00
2026-02-02 07:00:00+00 | 2026-02-02 07:59:00+00
2026-02-02 07:00:00+00 | 2026-02-02 07:59:00+00
2026-02-02 08:00:00+00 | 2026-02-02 08:59:00+00
2026-02-02 08:00:00+00 | 2026-02-02 08:59:00+00
2026-02-02 08:00:00+00 | 2026-02-02 08:59:00+00
2026-02-02 09:00:00+00 | 2026-02-02 09:59:00+00
2026-02-02 09:00:00+00 | 2026-02-02 09:59:00+00
2026-02-02 09:00:00+00 | 2026-02-02 09:59:00+00
2026-02-02 10:00:00+00 | 2026-02-02 10:59:00+00
2026-02-02 10:00:00+00 | 2026-02-02 10:59:00+00
2026-02-02 10:00:00+00 | 2026-02-02 10:59:00+00
2026-02-02 11:00:00+00 | 2026-02-02 11:59:00+00
2026-02-02 11:00:00+00 | 2026-02-02 11:59:00+00
2026-02-02 11:00:00+00 | 2026-02-02 11:59:00+00
2026-02-02 12:00:00+00 | 2026-02-02 12:59:00+00
2026-02-02 12:00:00+00 | 2026-02-02 12:59:00+00
2026-02-02 12:00:00+00 | 2026-02-02 12:59:00+00
2026-02-02 13:00:00+00 | 2026-02-02 13:59:00+00
2026-02-02 13:00:00+00 | 2026-02-02 13:59:00+00
2026-02-02 13:00:00+00 | 2026-02-02 13:59:00+00
2026-02-02 14:40:00+00 | 2026-02-02 14:40:00+00
2026-02-02 14:00:00+00 | 2026-02-02 14:59:00+00
2026-02-02 14:00:00+00 | 2026-02-02 14:59:00+00
2026-02-02 14:00:00+00 | 2026-02-02 14:59:00+00
2026-02-02 14:41:00+00 | 2026-02-02 14:59:00+00
2026-02-02 15:00:00+00 | 2026-02-02 15:59:00+00
2026-02-02 15:00:00+00 | 2026-02-02 15:59:00+00
2026-02-02 15:00:00+00 | 2026-02-02 15:59:00+00
2026-02-02 15:00:00+00 | 2026-02-02 15:59:00+00
2026-02-02 16:00:00+00 | 2026-02-02 16:59:00+00
2026-02-02 16:00:00+00 | 2026-02-02 16:59:00+00
2026-02-02 16:00:00+00 | 2026-02-02 16:59:00+00
2026-02-02 16:00:00+00 | 2026-02-02 16:59:00+00
2026-02-02 17:00:00+00 | 2026-02-02 17:59:00+00
2026-02-02 17:00:00+00 | 2026-02-02 17:59:00+00
2026-02-02 17:00:00+00 | 2026-02-02 17:59:00+00
2026-02-02 17:00:00+00 | 2026-02-02 17:59:00+00
2026-02-02 18:00:00+00 | 2026-02-02 18:59:00+00
2026-02-02 18:00:00+00 | 2026-02-02 18:59:00+00
2026-02-02 18:00:00+00 | 2026-02-02 18:59:00+00
2026-02-02 18:00:00+00 | 2026-02-02 18:59:00+00
2026-02-02 19:00:00+00 | 2026-02-02 19:59:00+00
2026-02-02 19:00:00+00 | 2026-02-02 19:59:00+00
2026-02-02 19:00:00+00 | 2026-02-02 19:59:00+00
2026-02-02 19:00:00+00 | 2026-02-02 19:59:00+00
2026-02-02 20:00:00+00 | 2026-02-02 20:59:00+00
2026-02-02 20:00:00+00 | 2026-02-02 20:59:00+00
2026-02-02 20:00:00+00 | 2026-02-02 20:59:00+00
2026-02-02 20:00:00+00 | 2026-02-02 20:59:00+00
2026-02-02 21:00:00+00 | 2026-02-02 21:59:00+00
2026-02-02 21:00:00+00 | 2026-02-02 21:59:00+00
2026-02-02 21:00:00+00 | 2026-02-02 21:59:00+00
2026-02-02 21:00:00+00 | 2026-02-02 21:59:00+00
2026-02-02 22:00:00+00 | 2026-02-02 22:59:00+00
2026-02-02 22:00:00+00 | 2026-02-02 22:59:00+00
2026-02-02 22:00:00+00 | 2026-02-02 22:59:00+00
2026-02-02 22:00:00+00 | 2026-02-02 22:59:00+00
2026-02-02 23:20:00+00 | 2026-02-02 23:20:00+00
2026-02-02 23:49:00+00 | 2026-02-02 23:49:00+00
2026-02-02 23:00:00+00 | 2026-02-02 23:59:00+00
2026-02-02 23:00:00+00 | 2026-02-02 23:59:00+00
2026-02-02 23:00:00+00 | 2026-02-02 23:59:00+00
2026-02-02 23:00:00+00 | 2026-02-02 23:59:00+00
2026-02-02 23:21:00+00 | 2026-02-02 23:59:00+00
2026-02-03 00:03:00+00 | 2026-02-03 00:03:00+00
2026-02-03 00:03:00+00 | 2026-02-03 00:03:00+00
2026-02-03 00:04:00+00 | 2026-02-03 00:04:00+00
2026-02-03 00:23:00+00 | 2026-02-03 00:23:00+00
2026-02-03 00:00:00+00 | 2026-02-03 00:59:00+00
2026-02-03 00:00:00+00 | 2026-02-03 00:59:00+00
2026-02-03 00:00:00+00 | 2026-02-03 00:59:00+00
2026-02-03 00:00:00+00 | 2026-02-03 00:59:00+00
2026-02-03 00:00:00+00 | 2026-02-03 00:59:00+00
2026-02-03 00:04:00+00 | 2026-02-03 00:59:00+00
2026-02-03 00:05:00+00 | 2026-02-03 00:59:00+00
2026-02-03 00:24:00+00 | 2026-02-03 00:59:00+00
2026-02-03 01:00:00+00 | 2026-02-03 01:59:00+00
2026-02-03 01:00:00+00 | 2026-02-03 01:59:00+00
2026-02-03 01:00:00+00 | 2026-02-03 01:59:00+00
2026-02-03 01:00:00+00 | 2026-02-03 01:59:00+00
2026-02-03 01:00:00+00 | 2026-02-03 01:59:00+00
2026-02-03 01:00:00+00 | 2026-02-03 01:59:00+00
2026-02-03 01:00:00+00 | 2026-02-03 01:59:00+00
2026-02-03 01:00:00+00 | 2026-02-03 01:59:00+00
2026-02-03 02:00:00+00 | 2026-02-03 02:59:00+00
2026-02-03 02:00:00+00 | 2026-02-03 02:59:00+00
2026-02-03 02:00:00+00 | 2026-02-03 02:59:00+00
2026-02-03 02:00:00+00 | 2026-02-03 02:59:00+00
2026-02-03 02:00:00+00 | 2026-02-03 02:59:00+00
2026-02-03 02:00:00+00 | 2026-02-03 02:59:00+00
2026-02-03 02:00:00+00 | 2026-02-03 02:59:00+00
2026-02-03 02:00:00+00 | 2026-02-03 02:59:00+00
2026-02-03 03:00:00+00 | 2026-02-03 03:59:00+00
2026-02-03 03:00:00+00 | 2026-02-03 03:59:00+00
2026-02-03 03:00:00+00 | 2026-02-03 03:59:00+00
2026-02-03 03:00:00+00 | 2026-02-03 03:59:00+00
2026-02-03 03:00:00+00 | 2026-02-03 03:59:00+00
2026-02-03 03:00:00+00 | 2026-02-03 03:59:00+00
2026-02-03 03:00:00+00 | 2026-02-03 03:59:00+00
2026-02-03 03:00:00+00 | 2026-02-03 03:59:00+00
2026-02-03 03:40:00+00 | 2026-02-03 03:59:00+00
2026-02-03 04:47:00+00 | 2026-02-03 04:57:00+00
2026-02-03 04:00:00+00 | 2026-02-03 04:59:00+00
2026-02-03 04:00:00+00 | 2026-02-03 04:59:00+00
2026-02-03 04:00:00+00 | 2026-02-03 04:59:00+00
2026-02-03 04:00:00+00 | 2026-02-03 04:59:00+00
2026-02-03 04:00:00+00 | 2026-02-03 04:59:00+00
2026-02-03 04:00:00+00 | 2026-02-03 04:59:00+00
2026-02-03 04:00:00+00 | 2026-02-03 04:59:00+00
2026-02-03 04:00:00+00 | 2026-02-03 04:59:00+00
2026-02-03 04:00:00+00 | 2026-02-03 04:59:00+00
2026-02-03 04:38:00+00 | 2026-02-03 04:59:00+00
2026-02-03 04:43:00+00 | 2026-02-03 04:59:00+00
2026-02-03 05:00:00+00 | 2026-02-03 05:06:00+00
2026-02-03 05:47:00+00 | 2026-02-03 05:55:00+00
2026-02-03 05:00:00+00 | 2026-02-03 05:59:00+00
2026-02-03 05:00:00+00 | 2026-02-03 05:59:00+00
2026-02-03 05:00:00+00 | 2026-02-03 05:59:00+00
2026-02-03 05:00:00+00 | 2026-02-03 05:59:00+00
2026-02-03 05:00:00+00 | 2026-02-03 05:59:00+00
2026-02-03 05:00:00+00 | 2026-02-03 05:59:00+00
2026-02-03 05:00:00+00 | 2026-02-03 05:59:00+00
2026-02-03 05:00:00+00 | 2026-02-03 05:59:00+00
2026-02-03 05:00:00+00 | 2026-02-03 05:59:00+00
2026-02-03 05:00:00+00 | 2026-02-03 05:59:00+00
2026-02-03 05:04:00+00 | 2026-02-03 05:59:00+00
2026-02-03 05:44:00+00 | 2026-02-03 05:59:00+00
(200 rows)
continuous_aggs_invalidation_threshold:
data=> select b.* from hypertable a join continuous_aggs_invalidation_threshold b on a.id = b.hypertable_id where a.table_name = 'my_hypertable' order by 2,1;
hypertable_id | watermark
---------------+------------------
2368 | 1770098100000000
(1 row)
continuous_aggs_watermark:
data=> select b.* from continuous_agg a join continuous_aggs_watermark b on a.mat_hypertable_id = b.mat_hypertable_id where a.user_view_name = 'my_cagg' order by 2,1;
mat_hypertable_id | watermark
-------------------+------------------
2370 | 1770094800000000
(1 row)
TimescaleDB version affected
2.24.0
PostgreSQL version used
18.1
What operating system did you use?
Ubuntu
What installation method did you use?
Deb/Apt
What platform did you run on?
Amazon Web Services (AWS)
Relevant log output and stack trace
How can we reproduce the bug?
Im not quite sure how overlapping materialization invalidation logs would happen in the first place and if its expected. To reproduce the duplicates in cagg, here's a walkthrough of some investigations on the behaviour I've done.
I have such mat invalidation logs.
data=> select lowest_modified_value, to_timestamp(greatest_modified_value/1000000) from continuous_agg a join continuous_aggs_materialization_invalidation_log b on a.mat_hypertable_id = b.materialization_id where a.user_view_name = 'my_cagg' order by 1;
lowest_modified_value | to_timestamp
----------------------------------------------+---------------------------------
-9223372036854775808 | 2026-02-02 23:53:59+00
1769911380000000 (2026-02-01T02:03:00Z) | 2026-02-03 04:59:59+00 (A)
1770087180000000 (2026-02-03T02:53:00Z) | 2026-02-03 04:59:59+00 (B)
1770094140000000 | 294247-01-10 04:00:53.999616+00
(4 rows)
Then I fired 2 concurrent refreshes.
Refresh 1: in hopes to hit inval log A
data=> call refresh_continuous_aggregate('block_book_snapshot_agg_1m', '2026-02-03T01:00:00Z', '2026-02-03T05:00:00Z');
Refresh 2: in hopes to hit inval log B
data=> call refresh_continuous_aggregate('block_book_snapshot_agg_1m', '2026-02-03T02:53:00Z', '2026-02-03T05:00:00Z');
After these 2 are completed, I see duplicates from time range `2026-02-03 01:00:00` to `2026-02-03 04:48:00`.