Multiple refresh policies for continuous aggregates

[mkindahl]

When creating a continuous aggregate it is possible to add a refresh policy to it to make sure that a particular range is updated, for example:

CREATE TABLE conditions(
    time TIMESTAMPTZ NOT NULL,
    device INTEGER,
    temperature FLOAT
);
SELECT * FROM create_hypertable('conditions', 'time',
       chunk_time_interval => INTERVAL '1 day');

INSERT INTO conditions
SELECT time, (random()*30)::int, random()*80 - 40
FROM generate_series(TIMESTAMPTZ '2020-01-01 00:00:00',
                 TIMESTAMPTZ '2020-03-01 00:00:00',
             INTERVAL '1 min') AS time;

CREATE MATERIALIZED VIEW conditions_summary_hourly
WITH (timescaledb.continuous) AS
SELECT device_id,
       time_bucket(INTERVAL '1 hour', "time") AS bucket,
       AVG(temperature),
       MAX(temperature),
       MIN(temperature)
FROM conditions
GROUP BY device_id, bucket;

CREATE MATERIALIZED VIEW conditions_summary_daily
WITH (timescaledb.continuous) AS
SELECT device_id,
       time_bucket(INTERVAL '1 day', "time") AS bucket,
       AVG(temperature),
       MAX(temperature),
       MIN(temperature)
FROM conditions
GROUP BY device_id, bucket;

SELECT add_continuous_aggregate_policy('conditions_summary_hourly',
    start_offset => INTERVAL '1 month',
    end_offset => INTERVAL '1 h',
    schedule_interval => INTERVAL '1 h');

Trying to add a second continuous aggregate policy results in an error.

postgres=# SELECT add_continuous_aggregate_policy('conditions_summary_hourly',
    start_offset => INTERVAL '12 months',
    end_offset => INTERVAL '1 month',
    schedule_interval => INTERVAL '1 day');
ERROR:  continuous aggregate policy already exists for "conditions_summary_hourly"
DETAIL:  Only one continuous aggregate policy can be created per continuous aggregate and a policy with job id 1005 already exists for "conditions_summary_hourly".

Instead of generating an error, support adding multiple policies for a continuous aggregate.

[glep207]

Workaround: configure a custom job.

[bendoerr]

Curious - I'm not familiar with how to configure a custom job? How might I do that?

We just ran into this issue as well. For some reason, our jobs end up hanging and never completing, if the threshold is too far back, this happens every once in a while. We were thinking of breaking up our refresh policies so that they only covers small range each.

[glep207]

@bendoerr see https://docs.timescale.com/api/latest/actions/ The body of the action would be a simple call to refresh_continuous_aggregate()