CODEWITHSUNDEEP
postgresqlmaterialized-viewstimescaledb
Antonwy
Antonwy

Reputation: 51

TimescaleDB Continuous Aggregation policy does not materialize view

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?

Upvotes: 1

Views: 570

Answers (2)

babbyldockr
babbyldockr

Reputation: 45

I know this reply is a little bit late but I realized that you are writing '3 months' instead of '3 month'. That will be a problem with the policy

Upvotes: 0

jonatasdp
jonatasdp

Reputation: 1412

This continuous aggregate troubleshooting page describe the watermark in the future which is a similar issue.

I think you can try to also access the errors and maybe run the first refresh of the continuous aggregates manually to see the internal error.

Think that refreshing in a minute base a aggregation of a month data will not be effective most of the time because the month is still in progress. Probably you can refresh it daily or weekly.

Upvotes: 0

Related Questions