On 2026-01-08 Th 2:05 AM, jian he wrote:
hi.
rebase due to conflict in
https://git.postgresql.org/cgit/postgresql.git/commit/?id=ba75f717526cbaa9000b546aac456e43d03aaf53
Here's a rework of this patch. It preserves the original signature of
to_json{b}_is_immutable, and fixes some code duplication. It also uses
the typecache to get composite info instead of calling relation_open,
supports MultiRange types, and exits early if we made a recursive call
(no need for json_categorize_type etc. in these cases).
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
From 71b3e509877493bce4d6f94d7d898e85637d92df Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <[email protected]>
Date: Thu, 5 Mar 2026 16:48:51 -0500
Subject: [PATCH] make to_json, to_jsonb immutability test complete
Complete the TODOs in to_json_is_immutable() and to_jsonb_is_immutable()
by recursing into container types (arrays, composites, ranges, multiranges,
domains) to check element/sub-type mutability, rather than conservatively
returning "mutable" for all arrays and composites.
The shared logic is factored into a single json_check_mutability() function
in jsonfuncs.c, with the existing exported functions as thin wrappers.
Composite type inspection uses lookup_rowtype_tupdesc() (typcache) instead
of relation_open() to avoid unnecessary lock acquisition in the optimizer.
Add comprehensive regression tests for JSON_ARRAY and JSON_OBJECT
mutability with expression indexes and generated columns, covering arrays,
composites, domains, ranges, and combinations thereof.
---
src/backend/utils/adt/json.c | 38 +------
src/backend/utils/adt/jsonb.c | 38 +------
src/backend/utils/adt/jsonfuncs.c | 103 +++++++++++++++++++
src/include/utils/jsonfuncs.h | 2 +
src/test/regress/expected/sqljson.out | 141 ++++++++++++++++++++++++++
src/test/regress/sql/sqljson.sql | 73 +++++++++++++
6 files changed, 325 insertions(+), 70 deletions(-)
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 0b161398465..52f2ef2875b 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -14,7 +14,6 @@
#include "postgres.h"
#include "access/htup_details.h"
-#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "common/hashfn.h"
#include "funcapi.h"
@@ -692,45 +691,14 @@ row_to_json_pretty(PG_FUNCTION_ARGS)
/*
* Is the given type immutable when coming out of a JSON context?
- *
- * At present, datetimes are all considered mutable, because they
- * depend on timezone. XXX we should also drill down into objects
- * and arrays, but do not.
*/
bool
to_json_is_immutable(Oid typoid)
{
- JsonTypeCategory tcategory;
- Oid outfuncoid;
+ bool has_mutable = false;
- json_categorize_type(typoid, false, &tcategory, &outfuncoid);
-
- switch (tcategory)
- {
- case JSONTYPE_BOOL:
- case JSONTYPE_JSON:
- case JSONTYPE_JSONB:
- case JSONTYPE_NULL:
- return true;
-
- case JSONTYPE_DATE:
- case JSONTYPE_TIMESTAMP:
- case JSONTYPE_TIMESTAMPTZ:
- return false;
-
- case JSONTYPE_ARRAY:
- return false; /* TODO recurse into elements */
-
- case JSONTYPE_COMPOSITE:
- return false; /* TODO recurse into fields */
-
- case JSONTYPE_NUMERIC:
- case JSONTYPE_CAST:
- case JSONTYPE_OTHER:
- return func_volatile(outfuncoid) == PROVOLATILE_IMMUTABLE;
- }
-
- return false; /* not reached */
+ json_check_mutability(typoid, false, &has_mutable);
+ return !has_mutable;
}
/*
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 0a3a77ee786..1b1a8f301f2 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -13,7 +13,6 @@
#include "postgres.h"
#include "access/htup_details.h"
-#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "funcapi.h"
#include "libpq/pqformat.h"
@@ -1077,45 +1076,14 @@ add_jsonb(Datum val, bool is_null, JsonbInState *result,
/*
* Is the given type immutable when coming out of a JSONB context?
- *
- * At present, datetimes are all considered mutable, because they
- * depend on timezone. XXX we should also drill down into objects and
- * arrays, but do not.
*/
bool
to_jsonb_is_immutable(Oid typoid)
{
- JsonTypeCategory tcategory;
- Oid outfuncoid;
+ bool has_mutable = false;
- json_categorize_type(typoid, true, &tcategory, &outfuncoid);
-
- switch (tcategory)
- {
- case JSONTYPE_NULL:
- case JSONTYPE_BOOL:
- case JSONTYPE_JSON:
- case JSONTYPE_JSONB:
- return true;
-
- case JSONTYPE_DATE:
- case JSONTYPE_TIMESTAMP:
- case JSONTYPE_TIMESTAMPTZ:
- return false;
-
- case JSONTYPE_ARRAY:
- return false; /* TODO recurse into elements */
-
- case JSONTYPE_COMPOSITE:
- return false; /* TODO recurse into fields */
-
- case JSONTYPE_NUMERIC:
- case JSONTYPE_CAST:
- case JSONTYPE_OTHER:
- return func_volatile(outfuncoid) == PROVOLATILE_IMMUTABLE;
- }
-
- return false; /* not reached */
+ json_check_mutability(typoid, true, &has_mutable);
+ return !has_mutable;
}
/*
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index d5b64d7fca5..d22b011ecb1 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -17,6 +17,8 @@
#include <limits.h>
#include "access/htup_details.h"
+#include "access/tupdesc.h"
+#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "common/int.h"
#include "common/jsonapi.h"
@@ -6062,3 +6064,104 @@ json_categorize_type(Oid typoid, bool is_jsonb,
break;
}
}
+
+/*
+ * Check whether a type conversion to JSON or JSONB involves any mutable
+ * functions. This recurses into container types (arrays, composites,
+ * ranges, multiranges, domains) to check their element/sub types.
+ *
+ * The caller must initialize *has_mutable to false before calling.
+ * If any mutable function is found, *has_mutable is set to true.
+ */
+void
+json_check_mutability(Oid typoid, bool is_jsonb, bool *has_mutable)
+{
+ char att_typtype = get_typtype(typoid);
+ JsonTypeCategory tcategory;
+ Oid outfuncoid;
+
+ /* since this function recurses, it could be driven to stack overflow */
+ check_stack_depth();
+
+ Assert(has_mutable != NULL);
+
+ if (*has_mutable)
+ return;
+
+ if (att_typtype == TYPTYPE_DOMAIN)
+ {
+ json_check_mutability(getBaseType(typoid), is_jsonb, has_mutable);
+ return;
+ }
+ else if (att_typtype == TYPTYPE_COMPOSITE)
+ {
+ /*
+ * For a composite type, recurse into its attributes. Use the
+ * typcache to avoid opening the relation directly.
+ */
+ TupleDesc tupdesc = lookup_rowtype_tupdesc(typoid, -1);
+
+ for (int i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+
+ if (attr->attisdropped)
+ continue;
+
+ json_check_mutability(attr->atttypid, is_jsonb, has_mutable);
+ }
+ ReleaseTupleDesc(tupdesc);
+ return;
+ }
+ else if (att_typtype == TYPTYPE_RANGE)
+ {
+ json_check_mutability(get_range_subtype(typoid), is_jsonb,
+ has_mutable);
+ return;
+ }
+ else if (att_typtype == TYPTYPE_MULTIRANGE)
+ {
+ json_check_mutability(get_multirange_range(typoid), is_jsonb,
+ has_mutable);
+ return;
+ }
+ else
+ {
+ Oid att_typelem = get_element_type(typoid);
+
+ if (OidIsValid(att_typelem))
+ {
+ /* recurse into array element type */
+ json_check_mutability(att_typelem, is_jsonb, has_mutable);
+ return;
+ }
+ }
+
+ json_categorize_type(typoid, is_jsonb, &tcategory, &outfuncoid);
+
+ switch (tcategory)
+ {
+ case JSONTYPE_BOOL:
+ case JSONTYPE_JSON:
+ case JSONTYPE_JSONB:
+ case JSONTYPE_NULL:
+ break;
+
+ case JSONTYPE_DATE:
+ case JSONTYPE_TIMESTAMP:
+ case JSONTYPE_TIMESTAMPTZ:
+ *has_mutable = true;
+ break;
+
+ case JSONTYPE_ARRAY:
+ case JSONTYPE_COMPOSITE:
+ break;
+
+ case JSONTYPE_NUMERIC:
+ case JSONTYPE_CAST:
+ case JSONTYPE_OTHER:
+ if (func_volatile(outfuncoid) != PROVOLATILE_IMMUTABLE)
+ *has_mutable = true;
+ break;
+ }
+}
diff --git a/src/include/utils/jsonfuncs.h b/src/include/utils/jsonfuncs.h
index 636f0f55840..27713be3aeb 100644
--- a/src/include/utils/jsonfuncs.h
+++ b/src/include/utils/jsonfuncs.h
@@ -83,6 +83,8 @@ typedef enum
extern void json_categorize_type(Oid typoid, bool is_jsonb,
JsonTypeCategory *tcategory, Oid *outfuncoid);
+extern void json_check_mutability(Oid typoid, bool is_jsonb,
+ bool *has_mutable);
extern Datum datum_to_json(Datum val, JsonTypeCategory tcategory,
Oid outfuncoid);
extern Datum datum_to_jsonb(Datum val, JsonTypeCategory tcategory,
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index c7b9e575445..5a5b78e3a16 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -1109,6 +1109,147 @@ CREATE OR REPLACE VIEW public.json_array_subquery_view AS
FROM ( SELECT foo.i
FROM ( VALUES (1), (2), (NULL::integer), (4)) foo(i)) q(a)) AS "json_array"
DROP VIEW json_array_subquery_view;
+-- Test mutability of JSON_OBJECTAGG, JSON_ARRAYAGG, JSON_ARRAY, JSON_OBJECT
+create type comp1 as (a int, b date);
+create domain d_comp1 as comp1;
+create domain mydomain as timestamptz;
+create type mydomainrange as range(subtype=mydomain);
+create type comp3 as (a int, b mydomainrange);
+create table test_mutability(
+ a text[], b timestamp, c timestamptz,
+ d date, f1 comp1[], f2 timestamp[],
+ f3 d_comp1[],
+ f4 mydomainrange[],
+ f5 comp3);
+--JSON_OBJECTAGG, JSON_ARRAYAGG is aggregate function, can not be used in index
+create index xx on test_mutability(json_objectagg(a: b absent on null with unique keys returning jsonb));
+ERROR: aggregate functions are not allowed in index expressions
+LINE 1: create index xx on test_mutability(json_objectagg(a: b absen...
+ ^
+create index xx on test_mutability(json_objectagg(a: b absent on null with unique keys returning json));
+ERROR: aggregate functions are not allowed in index expressions
+LINE 1: create index xx on test_mutability(json_objectagg(a: b absen...
+ ^
+create index xx on test_mutability(json_arrayagg(a returning jsonb));
+ERROR: aggregate functions are not allowed in index expressions
+LINE 1: create index xx on test_mutability(json_arrayagg(a returning...
+ ^
+create index xx on test_mutability(json_arrayagg(a returning json));
+ERROR: aggregate functions are not allowed in index expressions
+LINE 1: create index xx on test_mutability(json_arrayagg(a returning...
+ ^
+-- jsonb: create expression index via json_array
+create index on test_mutability(json_array(a returning jsonb)); --ok
+create index on test_mutability(json_array(b returning jsonb)); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_array(b returning jsonb...
+ ^
+create index on test_mutability(json_array(c returning jsonb)); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_array(c returning jsonb...
+ ^
+create index on test_mutability(json_array(d returning jsonb)); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_array(d returning jsonb...
+ ^
+create index on test_mutability(json_array(f1 returning jsonb)); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_array(f1 returning json...
+ ^
+create index on test_mutability(json_array(f2 returning jsonb)); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_array(f2 returning json...
+ ^
+create index on test_mutability(json_array(f3 returning jsonb)); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_array(f3 returning json...
+ ^
+create index on test_mutability(json_array(f4 returning jsonb)); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_array(f4 returning json...
+ ^
+create index on test_mutability(json_array(f5 returning jsonb)); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_array(f5 returning json...
+ ^
+--jsonb: create expression index via json_object
+create index on test_mutability(json_object('hello' value a returning jsonb)); --ok
+create index on test_mutability(json_object('hello' value b returning jsonb)); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_object('hello' value b ...
+ ^
+create index on test_mutability(json_object('hello' value c returning jsonb)); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_object('hello' value c ...
+ ^
+create index on test_mutability(json_object('hello' value d returning jsonb)); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_object('hello' value d ...
+ ^
+create index on test_mutability(json_object('hello' value f1 returning jsonb)); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_object('hello' value f1...
+ ^
+create index on test_mutability(json_object('hello' value f2 returning jsonb)); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_object('hello' value f2...
+ ^
+create index on test_mutability(json_object('hello' value f3 returning jsonb)); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_object('hello' value f3...
+ ^
+create index on test_mutability(json_object('hello' value f4 returning jsonb)); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_object('hello' value f4...
+ ^
+create index on test_mutability(json_object('hello' value f5 returning jsonb)); --error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_object('hello' value f5...
+ ^
+-- data type json don't have default operator class for access method "btree" so
+-- we use a generated column to test whether the JSON_ARRAY expression is
+-- immutable
+alter table test_mutability add column f10 json generated always as (json_array(a returning json)); --ok
+alter table test_mutability add column f11 json generated always as (json_array(b returning json)); --error
+ERROR: generation expression is not immutable
+alter table test_mutability add column f11 json generated always as (json_array(c returning json)); --error
+ERROR: generation expression is not immutable
+alter table test_mutability add column f11 json generated always as (json_array(d returning json)); --error
+ERROR: generation expression is not immutable
+alter table test_mutability add column f11 json generated always as (json_array(f1 returning json)); --error
+ERROR: generation expression is not immutable
+alter table test_mutability add column f11 json generated always as (json_array(f2 returning json)); --error
+ERROR: generation expression is not immutable
+alter table test_mutability add column f11 json generated always as (json_array(f4 returning json)); --error
+ERROR: generation expression is not immutable
+alter table test_mutability add column f11 json generated always as (json_array(f5 returning json)); --error
+ERROR: generation expression is not immutable
+-- data type json don't have default operator class for access method "btree" so
+-- we use a generated column to test whether the JSON_OBJECT expression is
+-- immutable
+alter table test_mutability add column f11 json generated always as (json_object('hello' value a returning json)); --ok
+alter table test_mutability add column f12 json generated always as (json_object('hello' value b returning json)); --error
+ERROR: generation expression is not immutable
+alter table test_mutability add column f12 json generated always as (json_object('hello' value c returning json)); --error
+ERROR: generation expression is not immutable
+alter table test_mutability add column f12 json generated always as (json_object('hello' value d returning json)); --error
+ERROR: generation expression is not immutable
+alter table test_mutability add column f12 json generated always as (json_object('hello' value f1 returning json)); --error
+ERROR: generation expression is not immutable
+alter table test_mutability add column f12 json generated always as (json_object('hello' value f2 returning json)); --error
+ERROR: generation expression is not immutable
+alter table test_mutability add column f12 json generated always as (json_object('hello' value f3 returning json)); --error
+ERROR: generation expression is not immutable
+alter table test_mutability add column f12 json generated always as (json_object('hello' value f4 returning json)); --error
+ERROR: generation expression is not immutable
+alter table test_mutability add column f12 json generated always as (json_object('hello' value f5 returning json)); --error
+ERROR: generation expression is not immutable
+drop table test_mutability;
+drop domain d_comp1;
+drop type comp3;
+drop type mydomainrange;
+drop domain mydomain;
+drop type comp1;
-- IS JSON predicate
SELECT NULL IS JSON;
?column?
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index 343d344d270..fea5ce66bee 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -386,6 +386,79 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING
DROP VIEW json_array_subquery_view;
+-- Test mutability of JSON_OBJECTAGG, JSON_ARRAYAGG, JSON_ARRAY, JSON_OBJECT
+create type comp1 as (a int, b date);
+create domain d_comp1 as comp1;
+create domain mydomain as timestamptz;
+create type mydomainrange as range(subtype=mydomain);
+create type comp3 as (a int, b mydomainrange);
+create table test_mutability(
+ a text[], b timestamp, c timestamptz,
+ d date, f1 comp1[], f2 timestamp[],
+ f3 d_comp1[],
+ f4 mydomainrange[],
+ f5 comp3);
+
+--JSON_OBJECTAGG, JSON_ARRAYAGG is aggregate function, can not be used in index
+create index xx on test_mutability(json_objectagg(a: b absent on null with unique keys returning jsonb));
+create index xx on test_mutability(json_objectagg(a: b absent on null with unique keys returning json));
+create index xx on test_mutability(json_arrayagg(a returning jsonb));
+create index xx on test_mutability(json_arrayagg(a returning json));
+
+-- jsonb: create expression index via json_array
+create index on test_mutability(json_array(a returning jsonb)); --ok
+create index on test_mutability(json_array(b returning jsonb)); --error
+create index on test_mutability(json_array(c returning jsonb)); --error
+create index on test_mutability(json_array(d returning jsonb)); --error
+create index on test_mutability(json_array(f1 returning jsonb)); --error
+create index on test_mutability(json_array(f2 returning jsonb)); --error
+create index on test_mutability(json_array(f3 returning jsonb)); --error
+create index on test_mutability(json_array(f4 returning jsonb)); --error
+create index on test_mutability(json_array(f5 returning jsonb)); --error
+
+--jsonb: create expression index via json_object
+create index on test_mutability(json_object('hello' value a returning jsonb)); --ok
+create index on test_mutability(json_object('hello' value b returning jsonb)); --error
+create index on test_mutability(json_object('hello' value c returning jsonb)); --error
+create index on test_mutability(json_object('hello' value d returning jsonb)); --error
+create index on test_mutability(json_object('hello' value f1 returning jsonb)); --error
+create index on test_mutability(json_object('hello' value f2 returning jsonb)); --error
+create index on test_mutability(json_object('hello' value f3 returning jsonb)); --error
+create index on test_mutability(json_object('hello' value f4 returning jsonb)); --error
+create index on test_mutability(json_object('hello' value f5 returning jsonb)); --error
+
+-- data type json don't have default operator class for access method "btree" so
+-- we use a generated column to test whether the JSON_ARRAY expression is
+-- immutable
+alter table test_mutability add column f10 json generated always as (json_array(a returning json)); --ok
+alter table test_mutability add column f11 json generated always as (json_array(b returning json)); --error
+alter table test_mutability add column f11 json generated always as (json_array(c returning json)); --error
+alter table test_mutability add column f11 json generated always as (json_array(d returning json)); --error
+alter table test_mutability add column f11 json generated always as (json_array(f1 returning json)); --error
+alter table test_mutability add column f11 json generated always as (json_array(f2 returning json)); --error
+alter table test_mutability add column f11 json generated always as (json_array(f4 returning json)); --error
+alter table test_mutability add column f11 json generated always as (json_array(f5 returning json)); --error
+
+-- data type json don't have default operator class for access method "btree" so
+-- we use a generated column to test whether the JSON_OBJECT expression is
+-- immutable
+alter table test_mutability add column f11 json generated always as (json_object('hello' value a returning json)); --ok
+alter table test_mutability add column f12 json generated always as (json_object('hello' value b returning json)); --error
+alter table test_mutability add column f12 json generated always as (json_object('hello' value c returning json)); --error
+alter table test_mutability add column f12 json generated always as (json_object('hello' value d returning json)); --error
+alter table test_mutability add column f12 json generated always as (json_object('hello' value f1 returning json)); --error
+alter table test_mutability add column f12 json generated always as (json_object('hello' value f2 returning json)); --error
+alter table test_mutability add column f12 json generated always as (json_object('hello' value f3 returning json)); --error
+alter table test_mutability add column f12 json generated always as (json_object('hello' value f4 returning json)); --error
+alter table test_mutability add column f12 json generated always as (json_object('hello' value f5 returning json)); --error
+
+drop table test_mutability;
+drop domain d_comp1;
+drop type comp3;
+drop type mydomainrange;
+drop domain mydomain;
+drop type comp1;
+
-- IS JSON predicate
SELECT NULL IS JSON;
SELECT NULL IS NOT JSON;
--
2.43.0
