Is it possible to set several refreshing continuous aggregate policy for one view in Timescale?

user_14319954 0
Viewed 417

When I'm trying to add second automatically refreshes policy for the view, I'm getting error:

ERROR:  continuous aggregate policy already exists for "my_table_hourly"
SQL state: 42710

It looks like Timescale allow just one refreshing policy per view. Is it any way to add another one?

timescaledb postgresql
1 Answers

Current version of TimescaleDB allows to create only one continuous aggregate policy per continuous aggregate materialized view. The policy performs refresh of the view inside the given refresh window.

Despite a policy exists, it is possible to call a refresh function manually on the same continuous aggregate view. So you have opportunity to automate calls to the refresh function, e.g., with help of user-defined actions.

For example, something like following can be implemented:

CREATE OR REPLACE PROCEDURE another_refresh(job_id int, config jsonb)
LANGUAGE PLPGSQL
AS $$
DECLARE
  cagg REGCLASS;
  start_offset interval;
  end_offset interval;
BEGIN
  SELECT jsonb_object_field_text (config, 'continuous_aggregate')::regclass INTO STRICT cagg;
  SELECT jsonb_object_field_text (config, 'start_offset')::interval INTO STRICT start_offset;
  SELECT jsonb_object_field_text (config, 'end_offset')::interval INTO STRICT end_offset;

  CALL refresh_continuous_aggregate(cagg, now()-start_offset, now()-end_offset);
END
$$;

SELECT add_job('another_refresh','1w', 
  config => '{"continuous_aggregate":"my_table_hourly","start_offset":"2w","end_offset":"1w"}');

It is good to take care about refresh windows between existing continuous aggregate policy and custom jobs calling the refresh functions, so the windows don't overlap and the policy and the custom jobs will not refresh the same buckets simultaneously, since it might affect performance significantly.

user_8403400
Related