I am trying to create a continuous aggregate with the heartbeat of all hosts reporting to the timescaledb with the following statement:
CREATE MATERIALIZED VIEW weekly_heartbeat
WITH (timescaledb.continuous) AS
SELECT
time_bucket('1 week', time) as week,
host,
heartbeat_agg(time :: timestamptz, time_bucket('1w', time), '1w', '2m')
FROM telegraf_internal_agent
GROUP BY 1,2;
This returns the error: ERROR: only immutable functions supported in continuous aggregate view
I checked all hyperfunctions i used for their immutability and none of them should be an issue:
> select proname, pronamespace::regnamespace, provolatile
from pg_proc
where proname IN ('heartbeat_agg', 'time_bucket');
-[ RECORD 1 ]+------------
proname | time_bucket
pronamespace | public
provolatile | i
...
-[ RECORD 17 ]--------------
proname | heartbeat_agg
pronamespace | telegraf
provolatile | i
Do I need to cast the timestamps/intervals in any special way to make this work?
TimescaleDB version: 2.10.3
PostgreSQL version: 14.8