I’ve got a problem with a jsonb_build_object in a continuous aggregate function. It worked fine, but on a new docker image is stopped working (timescaledb pg13-latest).
I’ve got the error message that only immutable functions are allowed in a aggregate view.
I can fix it with adding ‘timescaledb.finalized=false’ as an option to the CREATE MATERIALIZED VIEW… query but I’m wondering what I missed. I can’t find any resource or something that jsonb_build_object is disabled or has changed.
Should I stop using jsonb in aggregates?
Here is the query that gives the “only immatable functions are allowed…” - error
CREATE MATERIALIZED VIEW a_5min_aggre(measurement_id, time, schema) WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT measurement_id, time_bucket('5 minutes', md.time) as bucket, jsonb_build_object( 'count', count(value)::integer, 'sum', sum(value)::double precision ) FROM measurements_count_raw md WHERE md.value_type = 'SOME_VALUE' GROUP BY measurement_id, bucket WITH NO DATA;