CODEWITHSUNDEEP
postgresqltimescaledbhypertablecontinuous-aggregates
Aamir Dalal
Aamir Dalal

Reputation: 91

How should I automate refreshing of my continuous aggregates every time I insert historical data

I have created multiple continous aggregates on my hypertable using timescaledb 2.15.3 extension in postgreSQL 15.

When inserting historical data to this hypertable, I have to manually refresh all continous aggregates by calling refresh_continuous_aggregate procedure.

Is there any way that automates refreshing of all continous aggregates immediatly after insertion of historical data ?

I have tried to create an after insert foreach statement trigger on my hypertable inside which I was refreshing all continous aggregates.

  1. Created one stored procedure which refresh all continous aggregates:
CREATE OR REPLACE PROCEDURE sp_refresh_all_continuous_aggregates()
LANGUAGE plpgsql
AS $$
begin
    CALL refresh_continuous_aggregate('three_minute_candles'::regclass, null::timestamptz, null::timestamptz);
    CALL refresh_continuous_aggregate('five_minute_candles'::regclass, null::timestamptz, null::timestamptz);
    CALL refresh_continuous_aggregate('ten_minute_candles'::regclass, null::timestamptz, null::timestamptz);
    CALL refresh_continuous_aggregate('fifteen_minute_candles'::regclass, null::timestamptz, null::timestamptz);
    CALL refresh_continuous_aggregate('thirty_minute_candles', null::timestamptz, null::timestamptz);
    CALL refresh_continuous_aggregate('one_hour_candles'::regclass, null::timestamptz, null::timestamptz);
    CALL refresh_continuous_aggregate('one_day_candles'::regclass, null::timestamptz, null::timestamptz);
    CALL refresh_continuous_aggregate('one_month_candles'::regclass, null::timestamptz, null::timestamptz);
END;
$$;
  1. Created one trigger function which calls sp_refresh_all_continuous_aggregates:
CREATE OR REPLACE FUNCTION fn_call_refresh_all_continuous_aggregate()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    CALL sp_refresh_all_continuous_aggregates();
    return null;
END;
$$;
  1. Finally created a trigger on my hypertable.
CREATE TRIGGER refresh_all_continuous_aggregates
AFTER INSERT
ON candles
FOR EACH statement
execute function fn_call_refresh_all_continuous_aggregate();

But when inserting data into candles hypertable I get this error:

SQL Error [XX000]: ERROR: portal snapshots (0) did not account for all active snapshots (1)
  Where: SQL statement "CALL refresh_continuous_aggregate('three_minute_candles'::regclass, null::timestamptz, null::timestamptz)"
PL/pgSQL function sp_refresh_all_continuous_aggregates() line 3 at CALL
SQL statement "CALL sp_refresh_all_continuous_aggregates()"
PL/pgSQL function fn_call_refresh_all_continuous_aggregate() line 3 at CALL

Also, If I don't use this trigger and manually call sp_refresh_all_continuous_aggregates then I get this error:

SQL Error [42P01]: ERROR: relation "five_minute_candles" does not exist
  Where: PL/pgSQL function sp_refresh_all_continuous_aggregates() line 4 at CALL

I have made sure that there are no spelling mistakes, Also this query works normally if I execute it outside of the stored procedure.

Upvotes: 1

Views: 96

Answers (1)

jonatasdp
jonatasdp

Reputation: 1412

Just disable transaction DDLs. Every framework has its own way to disable DDL transactions. Example in Ruby:

https://apidock.com/rails/v5.2.3/ActiveRecord/Migration/disable_ddl_transaction%21/class

Upvotes: 0

Related Questions