Jsonb_build_object removed continuous aggregate since timescale db 2.7?

TimescaleDB and PostgreSQL
, ,
1

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;
2

@george,

Welcome to the forum! :tada:

To be honest, I’m a bit surprised it would work with the older partialized form of continuous aggregates and not the other way around. I haven’t tried jsonb_build_object() in a CAGG before, so I don’t have prior experience.

This seems like a regression in some way with the new finalized form, so I’d highly encourage you to submit an issue through our repo and I’ll alert a few folks here.

3

Ok. I’ve posted it on Github Bug report