Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pazqS-0003tJ-SJ for pgsql-general@arkaria.postgresql.org; Sat, 11 Mar 2023 14:05:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pazqQ-0000yB-SP for pgsql-general@arkaria.postgresql.org; Sat, 11 Mar 2023 14:05:10 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pazqQ-0000xu-1M for pgsql-general@lists.postgresql.org; Sat, 11 Mar 2023 14:05:10 +0000 Received: from mail-40133.protonmail.ch ([185.70.40.133]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pazqJ-0005qK-Un for pgsql-general@postgresql.org; Sat, 11 Mar 2023 14:05:07 +0000 Date: Sat, 11 Mar 2023 14:04:54 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=protonmail.com; s=protonmail3; t=1678543500; x=1678802700; bh=GPT1g6K0OwpmY4x8h2F1bjaOL4ktmt5MZgEcwtVrWzM=; h=Date:To:From:Subject:Message-ID:Feedback-ID:From:To:Cc:Date: Subject:Reply-To:Feedback-ID:Message-ID:BIMI-Selector; b=tf/ZKuzGxo8u0tKCjPcQAgoSktWqrYW4KzUXgJIjiJ/9nARHdLgNnu7tJFrqasmFi eQErkxS70RO8a+uxsGD/jS1QdmYSglj44mxH6JsdVecvMvVC5c2z1Mckp0RmKIwGpm FrXM/+0Ox+6e6lTiRzQjZlqx9K1YA2AmN2Huxr1r4zG1TWCA3PpCgo3Y9jTSFBwM+C M97Ry7/3nG+enKeXbKY+SXrViR9wzzevq1e20Th7zUd/j4MkmrgUZyrf9lgIJXyxeX fU0EWvls71gyO0LvDgINMRkDUjbo73Ivlt7W80KHbmPfxghwo5GlPgjY4B+Mc4KR8a lblTFd0CA9GJQ== To: pgsql-general@postgresql.org From: Martijn de Munnik Subject: ERROR: only immutable functions supported in continuous aggregate view Message-ID: <0b21034b-3fa8-16b3-c65b-1ef6b67c4da2@protonmail.com> Feedback-ID: 4917635:user:proton MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi List, I'm trying to create a TimescaleDB continuous aggregate using the following statement: CREATE MATERIALIZED VIEW "navigation_data_5min_hidden" WITH (timescaledb.continuous) AS SELECT =C2=A0 public.time_bucket(INTERVAL '5 min', "time") AS "time", =C2=A0 "context", =C2=A0 jsonb_build_object( =C2=A0=C2=A0=C2=A0 'max', max("value"::double precision) FILTER (WHERE "pa= th" =3D 'navigation.courseOverGroundTrue'), =C2=A0=C2=A0=C2=A0 'min', min("value"::double precision) FILTER (WHERE "pa= th" =3D 'navigation.courseOverGroundTrue'), =C2=A0=C2=A0=C2=A0 'mode', mode() WITHIN GROUP (ORDER BY "value") FILTER (= WHERE "path" =3D 'navigation.courseOverGroundTrue' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'cardinality', COUNT(DISTINCT "value") FILTER (WHERE "p= ath" =3D 'navigation.courseOverGroundTrue' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'average', public.average(public.time_weight('Linear', = "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.courseOverGroundTrue')), =C2=A0=C2=A0=C2=A0 'integral', public.average(public.time_weight('Linear',= "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.courseOverGroundTrue')) * EXTRACT(EPOCH FROM INTERVAL '5 min'), =C2=A0=C2=A0=C2=A0 'stddev', public.stddev(public.stats_agg("value"::doubl= e precision) FILTER (WHERE "path" =3D 'navigation.courseOverGroundTrue')), =C2=A0=C2=A0=C2=A0 'skewness', public.skewness(public.stats_agg("value"::d= ouble precision) FILTER (WHERE "path" =3D 'navigation.courseOverGroundTrue')), =C2=A0=C2=A0=C2=A0 'first_time', public.first_time(public.time_weight('Lin= ear', "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.courseOverGroundTrue')), =C2=A0=C2=A0=C2=A0 'first_val', public.first_val(public.time_weight('Linea= r', "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.courseOverGroundTrue')), =C2=A0=C2=A0=C2=A0 'last_time', public.last_time(public.time_weight('Linea= r', "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.courseOverGroundTrue')), =C2=A0=C2=A0=C2=A0 'last_val', public.last_val(public.time_weight('Linear'= , "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.courseOverGroundTrue')) =C2=A0 ) AS "navigation.courseOverGroundTrue", =C2=A0 jsonb_build_object( =C2=A0=C2=A0=C2=A0 'mode', mode() WITHIN GROUP (ORDER BY "value") FILTER (= WHERE "path" =3D 'navigation.datetime' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'cardinality', COUNT(DISTINCT "value") FILTER (WHERE "p= ath" =3D 'navigation.datetime' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'first_time', public.first("time", "time") FILTER (WHER= E "path" =3D 'navigation.datetime' AND "time" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'first_val', public.first("value"#>>'{}', "time") FILTE= R (WHERE "path" =3D 'navigation.datetime' AND "time" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'last_time', public.last("time", "time") FILTER (WHERE = "path" =3D 'navigation.datetime' AND "time" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'last_val', public.last("value"#>>'{}', "time") FILTER = (WHERE "path" =3D 'navigation.datetime' AND "time" IS NOT NULL) =C2=A0 ) AS "navigation.datetime", =C2=A0 jsonb_build_object( =C2=A0=C2=A0=C2=A0 'mode', mode() WITHIN GROUP (ORDER BY "value") FILTER (= WHERE "path" =3D 'navigation.gnss.methodQuality' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'cardinality', COUNT(DISTINCT "value") FILTER (WHERE "p= ath" =3D 'navigation.gnss.methodQuality' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'first_time', public.first("time", "time") FILTER (WHER= E "path" =3D 'navigation.gnss.methodQuality' AND "time" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'first_val', public.first("value"#>>'{}', "time") FILTE= R (WHERE "path" =3D 'navigation.gnss.methodQuality' AND "time" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'last_time', public.last("time", "time") FILTER (WHERE = "path" =3D 'navigation.gnss.methodQuality' AND "time" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'last_val', public.last("value"#>>'{}', "time") FILTER = (WHERE "path" =3D 'navigation.gnss.methodQuality' AND "time" IS NOT NULL) =C2=A0 ) AS "navigation.gnss.methodQuality", =C2=A0 jsonb_build_object( =C2=A0=C2=A0=C2=A0 'max', max("value"::double precision) FILTER (WHERE "pa= th" =3D 'navigation.gnss.satellites'), =C2=A0=C2=A0=C2=A0 'min', min("value"::double precision) FILTER (WHERE "pa= th" =3D 'navigation.gnss.satellites'), =C2=A0=C2=A0=C2=A0 'mode', mode() WITHIN GROUP (ORDER BY "value") FILTER (= WHERE "path" =3D 'navigation.gnss.satellites' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'cardinality', COUNT(DISTINCT "value") FILTER (WHERE "p= ath" =3D 'navigation.gnss.satellites' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'average', public.average(public.time_weight('Linear', = "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.gnss.satellites')), =C2=A0=C2=A0=C2=A0 'integral', public.average(public.time_weight('Linear',= "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.gnss.satellites')) * EXTRACT(EPOCH FROM INTERVAL '5 min'), =C2=A0=C2=A0=C2=A0 'stddev', public.stddev(public.stats_agg("value"::doubl= e precision) FILTER (WHERE "path" =3D 'navigation.gnss.satellites')), =C2=A0=C2=A0=C2=A0 'skewness', public.skewness(public.stats_agg("value"::d= ouble precision) FILTER (WHERE "path" =3D 'navigation.gnss.satellites')), =C2=A0=C2=A0=C2=A0 'first_time', public.first_time(public.time_weight('Lin= ear', "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.gnss.satellites')), =C2=A0=C2=A0=C2=A0 'first_val', public.first_val(public.time_weight('Linea= r', "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.gnss.satellites')), =C2=A0=C2=A0=C2=A0 'last_time', public.last_time(public.time_weight('Linea= r', "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.gnss.satellites')), =C2=A0=C2=A0=C2=A0 'last_val', public.last_val(public.time_weight('Linear'= , "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.gnss.satellites')) =C2=A0 ) AS "navigation.gnss.satellites", =C2=A0 jsonb_build_object( =C2=A0=C2=A0=C2=A0 'mode', mode() WITHIN GROUP (ORDER BY "value") FILTER (= WHERE "path" =3D 'navigation.gnss.type' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'cardinality', COUNT(DISTINCT "value") FILTER (WHERE "p= ath" =3D 'navigation.gnss.type' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'first_time', public.first("time", "time") FILTER (WHER= E "path" =3D 'navigation.gnss.type' AND "time" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'first_val', public.first("value"#>>'{}', "time") FILTE= R (WHERE "path" =3D 'navigation.gnss.type' AND "time" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'last_time', public.last("time", "time") FILTER (WHERE = "path" =3D 'navigation.gnss.type' AND "time" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'last_val', public.last("value"#>>'{}', "time") FILTER = (WHERE "path" =3D 'navigation.gnss.type' AND "time" IS NOT NULL) =C2=A0 ) AS "navigation.gnss.type", =C2=A0 jsonb_build_object( =C2=A0=C2=A0=C2=A0 'max', max("value"::double precision) FILTER (WHERE "pa= th" =3D 'navigation.headingTrue'), =C2=A0=C2=A0=C2=A0 'min', min("value"::double precision) FILTER (WHERE "pa= th" =3D 'navigation.headingTrue'), =C2=A0=C2=A0=C2=A0 'mode', mode() WITHIN GROUP (ORDER BY "value") FILTER (= WHERE "path" =3D 'navigation.headingTrue' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'cardinality', COUNT(DISTINCT "value") FILTER (WHERE "p= ath" =3D 'navigation.headingTrue' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'average', public.average(public.time_weight('Linear', = "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.headingTrue')), =C2=A0=C2=A0=C2=A0 'integral', public.average(public.time_weight('Linear',= "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.headingTrue')) * EXTRACT(EPOCH FROM INTERVAL '5 min'), =C2=A0=C2=A0=C2=A0 'stddev', public.stddev(public.stats_agg("value"::doubl= e precision) FILTER (WHERE "path" =3D 'navigation.headingTrue')), =C2=A0=C2=A0=C2=A0 'skewness', public.skewness(public.stats_agg("value"::d= ouble precision) FILTER (WHERE "path" =3D 'navigation.headingTrue')), =C2=A0=C2=A0=C2=A0 'first_time', public.first_time(public.time_weight('Lin= ear', "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.headingTrue')), =C2=A0=C2=A0=C2=A0 'first_val', public.first_val(public.time_weight('Linea= r', "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.headingTrue')), =C2=A0=C2=A0=C2=A0 'last_time', public.last_time(public.time_weight('Linea= r', "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.headingTrue')), =C2=A0=C2=A0=C2=A0 'last_val', public.last_val(public.time_weight('Linear'= , "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.headingTrue= ')) =C2=A0 ) AS "navigation.headingTrue", =C2=A0 jsonb_build_object( =C2=A0=C2=A0=C2=A0 'mode', mode() WITHIN GROUP (ORDER BY "value") FILTER (= WHERE "path" =3D 'navigation.position' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'cardinality', COUNT(DISTINCT "value") FILTER (WHERE "p= ath" =3D 'navigation.position' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'first_time', public.first("time", "time") FILTER (WHER= E "path" =3D 'navigation.position' AND "time" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'first_val', public.first("value"#>>'{}', "time") FILTE= R (WHERE "path" =3D 'navigation.position' AND "time" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'last_time', public.last("time", "time") FILTER (WHERE = "path" =3D 'navigation.position' AND "time" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'last_val', public.last("value"#>>'{}', "time") FILTER = (WHERE "path" =3D 'navigation.position' AND "time" IS NOT NULL) =C2=A0 ) AS "navigation.position", =C2=A0 jsonb_build_object( =C2=A0=C2=A0=C2=A0 'max', max("value"::double precision) FILTER (WHERE "pa= th" =3D 'navigation.rateOfTurn'), =C2=A0=C2=A0=C2=A0 'min', min("value"::double precision) FILTER (WHERE "pa= th" =3D 'navigation.rateOfTurn'), =C2=A0=C2=A0=C2=A0 'mode', mode() WITHIN GROUP (ORDER BY "value") FILTER (= WHERE "path" =3D 'navigation.rateOfTurn' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'cardinality', COUNT(DISTINCT "value") FILTER (WHERE "p= ath" =3D 'navigation.rateOfTurn' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'average', public.average(public.time_weight('Linear', = "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.rateOfTurn'= )), =C2=A0=C2=A0=C2=A0 'integral', public.average(public.time_weight('Linear',= "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.rateOfTurn')) * EXTRACT(EPOCH FROM INTERVAL '5 min'), =C2=A0=C2=A0=C2=A0 'stddev', public.stddev(public.stats_agg("value"::doubl= e precision) FILTER (WHERE "path" =3D 'navigation.rateOfTurn')), =C2=A0=C2=A0=C2=A0 'skewness', public.skewness(public.stats_agg("value"::d= ouble precision) FILTER (WHERE "path" =3D 'navigation.rateOfTurn')), =C2=A0=C2=A0=C2=A0 'first_time', public.first_time(public.time_weight('Lin= ear', "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.rateOfTurn')), =C2=A0=C2=A0=C2=A0 'first_val', public.first_val(public.time_weight('Linea= r', "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.rateOfTurn'= )), =C2=A0=C2=A0=C2=A0 'last_time', public.last_time(public.time_weight('Linea= r', "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.rateOfTurn'= )), =C2=A0=C2=A0=C2=A0 'last_val', public.last_val(public.time_weight('Linear'= , "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.rateOfTurn'= )) =C2=A0 ) AS "navigation.rateOfTurn", =C2=A0 jsonb_build_object( =C2=A0=C2=A0=C2=A0 'max', max("value"::double precision) FILTER (WHERE "pa= th" =3D 'navigation.speedOverGround'), =C2=A0=C2=A0=C2=A0 'min', min("value"::double precision) FILTER (WHERE "pa= th" =3D 'navigation.speedOverGround'), =C2=A0=C2=A0=C2=A0 'mode', mode() WITHIN GROUP (ORDER BY "value") FILTER (= WHERE "path" =3D 'navigation.speedOverGround' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'cardinality', COUNT(DISTINCT "value") FILTER (WHERE "p= ath" =3D 'navigation.speedOverGround' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'average', public.average(public.time_weight('Linear', = "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.speedOverGround')), =C2=A0=C2=A0=C2=A0 'integral', public.average(public.time_weight('Linear',= "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.speedOverGround')) * EXTRACT(EPOCH FROM INTERVAL '5 min'), =C2=A0=C2=A0=C2=A0 'stddev', public.stddev(public.stats_agg("value"::doubl= e precision) FILTER (WHERE "path" =3D 'navigation.speedOverGround')), =C2=A0=C2=A0=C2=A0 'skewness', public.skewness(public.stats_agg("value"::d= ouble precision) FILTER (WHERE "path" =3D 'navigation.speedOverGround')), =C2=A0=C2=A0=C2=A0 'first_time', public.first_time(public.time_weight('Lin= ear', "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.speedOverGround')), =C2=A0=C2=A0=C2=A0 'first_val', public.first_val(public.time_weight('Linea= r', "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.speedOverGround')), =C2=A0=C2=A0=C2=A0 'last_time', public.last_time(public.time_weight('Linea= r', "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.speedOverGround')), =C2=A0=C2=A0=C2=A0 'last_val', public.last_val(public.time_weight('Linear'= , "time", "value"::double precision) FILTER (WHERE "path" =3D 'navigation.speedOverGround')) =C2=A0 ) AS "navigation.speedOverGround", =C2=A0 jsonb_build_object( =C2=A0=C2=A0=C2=A0 'mode', mode() WITHIN GROUP (ORDER BY "value") FILTER (= WHERE "path" =3D 'navigation.state' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'cardinality', COUNT(DISTINCT "value") FILTER (WHERE "p= ath" =3D 'navigation.state' AND "value" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'first_time', public.first("time", "time") FILTER (WHER= E "path" =3D 'navigation.state' AND "time" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'first_val', public.first("value"#>>'{}', "time") FILTE= R (WHERE "path" =3D 'navigation.state' AND "time" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'last_time', public.last("time", "time") FILTER (WHERE = "path" =3D 'navigation.state' AND "time" IS NOT NULL), =C2=A0=C2=A0=C2=A0 'last_val', public.last("value"#>>'{}', "time") FILTER = (WHERE "path" =3D 'navigation.state' AND "time" IS NOT NULL) =C2=A0 ) AS "navigation.state" FROM =C2=A0=C2=A0=C2=A0 "gosk"."mapped_data" WHERE =C2=A0=C2=A0=C2=A0 "path" IN ( =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 'navigation.courseOverGroundTru= e', =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 'navigation.datetime', =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 'navigation.gnss.methodQuality'= , =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 'navigation.gnss.satellites', =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 'navigation.gnss.type', =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 'navigation.headingTrue', =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 'navigation.position', =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 'navigation.rateOfTurn', =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 'navigation.speedOverGround', =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 'navigation.state' =C2=A0=C2=A0=C2=A0 ) GROUP BY =C2=A0=C2=A0=C2=A0 1, 2 WITH NO DATA; And that results in an error: ERROR:=C2=A0 only immutable functions supported in continuous aggregate vie= w HINT:=C2=A0 Make sure all functions in the continuous aggregate definition have IMMUTABLE volatility. Note that functions or expressions may be IMMUTABLE for one data type, but STABLE or VOLATILE for another. The error is caused by the jsonb_build_object function, is there any reason why this function is not IMMUTABLE? I would have expected it to be IMMUTABLE. Kind regards, Martijn de Munnik