I have the following Hierarchical continuous aggregates:
CREATE MATERIALIZED VIEW panel_power_data_hourly
WITH (timescaledb.continuous) AS
select time_bucket('1 hour', time) as bucket_hourly, avg(power) as avg_power, sum(power) as total_power, panel_id
from timescale_panel_power_data
group by bucket_hourly, panel_id
WITH NO DATA;
CREATE MATERIALIZED VIEW panel_power_data_daily
WITH (timescaledb.continuous) AS
select time_bucket('1 day', bucket_hourly) as bucket_daily,
avg(avg_power) as avg_power,
sum(total_power) as total_power,
panel_id
from panel_power_data_hourly
group by bucket_daily, panel_id
WITH NO DATA;
CREATE MATERIALIZED VIEW panel_power_data_monthly
WITH (timescaledb.continuous) AS
select time_bucket('1 month', bucket_daily) as bucket_monthly,
avg(avg_power) as avg_power,
sum(total_power) as total_power,
panel_id
from panel_power_data_daily
group by bucket_monthly, panel_id
WITH NO DATA;
Materializing and refreshing the panel_power_data_hourly and panel_power_data_daily views works without problems. But when I try to add the following refresh policy to the monthly view:
SELECT add_continuous_aggregate_policy('panel_power_data_monthly',
start_offset => INTERVAL '3 months',
end_offset => INTERVAL '1 hour',
schedule_interval => INTERVAL '1 minute');
or try to refresh the monthly view:
call refresh_continuous_aggregate('panel_power_data_monthly', now() - interval '3 months',
now() - interval '1 hour');
nothing happens. The materialized view stays empty, I don’t get an error and when I check the water mark in the following way:
SELECT COALESCE(
_timescaledb_internal.to_timestamp(_timescaledb_internal.cagg_watermark(144)),
'-infinity'::timestamp with time zone
);
I get -infinity so watermark is null.
Any ideas what the problem could be?
