Creating continuous aggregate before table exists

TimescaleDB and PostgreSQL
1

We have the requirement that the whole monitoring infrastructure has to be bootstrap-able in remote environments without internet connectivity. We want to automatically create the continuous aggregates after the timescaledb server has been set up using an plpgsql script:

set role timescale;
CREATE MATERIALIZED VIEW cpu_hourly
WITH (timescaledb.continuous) AS
SELECT
  time_bucket(INTERVAL '1 hour', time) as bucket,
  *
from cpu 
group by bucket, host
WITH NO DATA;

select add_continuous_aggregate_policy('cpu_hourly', start_offset => INTERVAL '1 day', end_offset => INTERVAL '61 minutes', schedule_interval => '1 hour');

grant select on cpu_hourly to grafana;

reset role;

The problem is, that at the time that this script is executed, no metrics have yet been reported to the server by telegraf and therefore none of the tables required for the select exist. Some metrics might take multiple hours to be sent to the server.

Are any solutions to this problem?

2

Welcome @Earthwalker31, I think you raised a fair point here, and probably that will need some extra mechanism to make it create after the data is created.

I’d start with a function that can periodically check what are the actual hypertables, and then it creates the caggs for the missing hypertables.

Another idea would be for you to also bring a suggestion for the development team to introduce hooks. We could hook something like “on hypertable creation,” which would allow us to generate the caggs right after the hypertable is generated.

In the same way, you can probably be replacing the create_hypertable function to inject your desired behavior. I never tried it but I think it would be a valid path.