On Fri, Oct 23, 2026 at 9:36 AM shihao zhong <[email protected]> wrote:
>
> float8out is STABLE on master now, but v3 still lists FLOAT4OID
> and FLOAT8OID as immutable. So this is accepted:
>
> CREATE INDEX ON tf ((json_array(f returning jsonb))); -- f float8
>
> and gives wrong results after SET extra_float_digits = 0. Dropping
> those two case labels fixes it on master and changes nothing on 19.
>
Hi.
This issue is fixed.
Another old issue: indexes don't have dependency with CAST. Dropping a
user-defined cast will change the index definition.
Hopefully, the attached v4 fix should be bullet-proof.
Enums and composite types should be marked as mutable when casting to json or
jsonb. Because ALTER TYPE ... RENAME VALUE, ADD/DROP/RENAME ATTRIBUTE (and ALTER
TABLE on a table's row type) does not rebuilding dependent indexes.
user-defined range and multirange types as mutable too. Their conversion uses a
cast to json function if exists, looked up at runtime, so creating or dropping
such a cast after CREATE INDEX changes the output.
Built-in ranges are unaffected, see json_categorize_type DEFAULT branch code.
Below is a code demo for a user-defined range type.
-------------------------
DROP TABLE IF EXISTS tr;
DROP CAST IF EXISTS (myr AS json);
DROP FUNCTION IF EXISTS myr_json(myr);
DROP TYPE IF EXISTS myr;
CREATE TYPE myr AS RANGE (subtype = int);
CREATE TABLE tr (r myr) WITH (autovacuum_enabled = off);
INSERT INTO tr VALUES ('[1,3)');
-- no cast exists yet, so the index stores the range_out text
CREATE INDEX tr_idx ON tr ((json_array(r returning jsonb)));
CREATE FUNCTION myr_json(myr) RETURNS json IMMUTABLE LANGUAGE sql AS
$$ SELECT '{"1": "2"}'::json $$;
CREATE CAST (myr AS json) WITH FUNCTION myr_json(myr);
SET enable_seqscan = off;
SELECT count(*) FROM tr WHERE json_array(r returning jsonb) = '[{"1": "2"}]';
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT count(*) FROM tr WHERE json_array(r returning jsonb) = '[{"1": "2"}]';
From 4b7f93f3c9927138159969b41b504e312cccd58d Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Fri, 23 Oct 2026 12:46:04 +0800
Subject: [PATCH v4 1/1] Don't treat record -> json(b) conversions as
immutable.
The existing behavior is accidental and incorrect. Output functions
are required to be stable, but only some of them are immutable; hence,
output functions for container types should be treated as stable.
The JSON code has not gotten this right. A recent refactoring surfaced
the defect but preserved it via a special-case exception; this commit
removes that exception and updates the test accordingly.
This also removes the corresponding exceptions for anyarray and
anycompatiblearray, which are wrong for the same reasons, but removing
those exceptions is not expected to have any significant user-visible
consequences. Values of type record can be constructed using
ROW(), but there's no equivalent for anyarray or anycompatiblearray.
Note that the change for type record is a (minor) backward compatibility
break that could block upgrades; see the regression test for an example
of a case that would break.
Related commit:
https://git.postgresql.org/cgit/postgresql.git/commit/?id=e75fcc9867fdfe295957ad5c9a98cbe2e1b4c5c3
Discussion:
https://postgr.es/m/ca+tgmoaiothgqrw9otgsmzbucnqj2jdagtbs6o3fkjcd+lf...@mail.gmail.com
---
src/backend/utils/adt/json.c | 2 +-
src/backend/utils/adt/jsonb.c | 2 +-
src/backend/utils/adt/jsonfuncs.c | 139 +++++++++++++++++---------
src/include/utils/jsonfuncs.h | 3 +-
src/test/regress/expected/sqljson.out | 69 +++++++++++--
src/test/regress/sql/sqljson.sql | 34 +++++--
6 files changed, 184 insertions(+), 65 deletions(-)
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index 28e5f3cf9c0..5aab80f8763 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -697,7 +697,7 @@ to_json_is_immutable(Oid typoid)
{
bool has_mutable = false;
- json_check_mutability(typoid, false, &has_mutable);
+ json_check_mutability(typoid, &has_mutable);
return !has_mutable;
}
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 29efd5885ea..a00301853aa 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -1082,7 +1082,7 @@ to_jsonb_is_immutable(Oid typoid)
{
bool has_mutable = false;
- json_check_mutability(typoid, true, &has_mutable);
+ json_check_mutability(typoid, &has_mutable);
return !has_mutable;
}
diff --git a/src/backend/utils/adt/jsonfuncs.c
b/src/backend/utils/adt/jsonfuncs.c
index 274ed9d3184..85de8797fc0 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -506,6 +506,7 @@ static JsonParseErrorType
transform_string_values_object_field_start(void *state
static JsonParseErrorType transform_string_values_array_element_start(void
*state, bool isnull);
static JsonParseErrorType transform_string_values_scalar(void *state, char
*token, JsonTokenType tokentype);
+static Oid get_json_cast_for_type(Oid typoid);
/*
* pg_parse_json_or_errsave
@@ -6069,18 +6070,17 @@ json_categorize_type(Oid typoid, bool is_jsonb,
/*
* 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.
+ * functions. This recurses into container types (arrays, ranges, multiranges,
+ * domains, etc) to check their element/sub types. Composite types are always
+ * considered mutable.
*
* 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)
+json_check_mutability(Oid typoid, 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();
@@ -6092,41 +6092,42 @@ json_check_mutability(Oid typoid, bool is_jsonb, bool
*has_mutable)
if (att_typtype == TYPTYPE_DOMAIN)
{
- json_check_mutability(getBaseType(typoid), is_jsonb,
has_mutable);
+ json_check_mutability(getBaseType(typoid), has_mutable);
+ return;
+ }
+ else if (att_typtype == TYPTYPE_ENUM)
+ {
+ /*
+ * ALTER TYPE ... RENAME VALUE can change an enum's output
without
+ * rebuilding indexes, so an enum is never immutable.
+ */
+ *has_mutable = true;
return;
}
else if (att_typtype == TYPTYPE_COMPOSITE)
{
/*
- * For a composite type, recurse into its attributes. Use the
- * typcache to avoid opening the relation directly.
+ * The output includes the attribute names, which ALTER TYPE can
+ * change, so a composite is never immutable.
*/
- 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);
- if (*has_mutable)
- break;
- }
- ReleaseTupleDesc(tupdesc);
+ *has_mutable = true;
return;
}
- else if (att_typtype == TYPTYPE_RANGE)
+ else if (att_typtype == TYPTYPE_RANGE ||
+ att_typtype == TYPTYPE_MULTIRANGE)
{
- 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);
+ /*
+ * A cast to json on a user-defined range or multirange can be
created
+ * or dropped after this check, changing the output without
+ * invalidating anything that depends on it, so treat such
types as
+ * mutable.
+ */
+ if (typoid >= FirstNormalObjectId)
+ *has_mutable = true;
+ else if (att_typtype == TYPTYPE_RANGE)
+ json_check_mutability(get_range_subtype(typoid),
has_mutable);
+ else
+ json_check_mutability(get_multirange_range(typoid),
has_mutable);
return;
}
else
@@ -6136,36 +6137,76 @@ json_check_mutability(Oid typoid, bool is_jsonb, bool
*has_mutable)
if (OidIsValid(att_typelem))
{
/* recurse into array element type */
- json_check_mutability(att_typelem, is_jsonb,
has_mutable);
+ json_check_mutability(att_typelem, has_mutable);
return;
}
}
- json_categorize_type(typoid, is_jsonb, &tcategory, &outfuncoid);
-
- switch (tcategory)
+ switch (typoid)
{
- case JSONTYPE_NULL:
- case JSONTYPE_BOOL:
- case JSONTYPE_NUMERIC:
+ case BOOLOID:
+ case INT2OID:
+ case INT4OID:
+ case INT8OID:
+ case NUMERICOID:
+ case JSONOID:
+ case JSONBOID:
+ /* known immutable */
break;
- case JSONTYPE_DATE:
- case JSONTYPE_TIMESTAMP:
- case JSONTYPE_TIMESTAMPTZ:
+ case DATEOID:
+ case TIMESTAMPOID:
+ case TIMESTAMPTZOID:
*has_mutable = true;
break;
- case JSONTYPE_JSON:
- case JSONTYPE_JSONB:
- case JSONTYPE_ARRAY:
- case JSONTYPE_COMPOSITE:
+ case RECORDOID:
+ case ANYARRAYOID:
+ case ANYCOMPATIBLEARRAYOID:
+
+ /*
+ * The field or element types of these pseudo-types are
not known
+ * until runtime, so assume the worst.
+ */
+ *has_mutable = true;
break;
- case JSONTYPE_CAST:
- case JSONTYPE_OTHER:
- if (func_volatile(outfuncoid) != PROVOLATILE_IMMUTABLE)
- *has_mutable = true;
+ default:
+ {
+ Oid funcoid =
get_json_cast_for_type(typoid);
+
+ if (!OidIsValid(funcoid))
+ {
+ bool typisvarlena;
+
+ getTypeOutputInfo(typoid, &funcoid,
&typisvarlena);
+ }
+ if (func_volatile(funcoid) !=
PROVOLATILE_IMMUTABLE)
+ *has_mutable = true;
+ }
break;
}
}
+
+/*
+ * Return the OID of the cast function from typoid to JSON, or InvalidOid if
+ * there is no such cast. As a matter of policy, we only consider explicit,
+ * user-defined casts. This must match the lookup in json_categorize_type():
+ * it is used for both json and jsonb output, a cast to jsonb is never used.
+ */
+static Oid
+get_json_cast_for_type(Oid typoid)
+{
+ if (typoid >= FirstNormalObjectId)
+ {
+ Oid castfunc;
+ CoercionPathType ctype;
+
+ ctype = find_coercion_pathway(JSONOID, typoid,
+
COERCION_EXPLICIT,
+
&castfunc);
+ if (ctype == COERCION_PATH_FUNC && OidIsValid(castfunc))
+ return castfunc;
+ }
+ return InvalidOid;
+}
diff --git a/src/include/utils/jsonfuncs.h b/src/include/utils/jsonfuncs.h
index 27713be3aeb..cc5e0244cca 100644
--- a/src/include/utils/jsonfuncs.h
+++ b/src/include/utils/jsonfuncs.h
@@ -83,8 +83,7 @@ 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 void json_check_mutability(Oid typoid, 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 30f9e10ef0a..10116dc0785 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -1271,13 +1271,20 @@ 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 type comp4 as (a int, b int);
+create type myr AS range (subtype = int);
+create function myr_json(myr) returns json volatile
+ language sql AS $$ SELECT json_build_array(random()) $$;
+create cast (myr AS json) with function myr_json(myr);
create table test_mutability(
a text[], b timestamp, c timestamptz,
d date, f1 comp1[], f2 timestamp[],
f3 d_comp1[],
f4 mydomainrange[],
f5 comp3,
- f6 mydomainmultirange);
+ f6 mydomainmultirange,
+ f7 comp4,
+ f8 myr);
-- JSON_OBJECTAGG, JSON_ARRAYAGG are aggregate functions, cannot 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
@@ -1333,6 +1340,30 @@ create index on test_mutability(json_array(f6 returning
jsonb)); -- error
ERROR: functions in index expression must be marked IMMUTABLE
LINE 1: create index on test_mutability(json_array(f6 returning json...
^
+create index on test_mutability(json_array(array[f6] returning jsonb)); --
error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_array(array[f6] returni...
+ ^
+create index on test_mutability(json_array(row(f6) returning jsonb)); -- error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_array(row(f6) returning...
+ ^
+create index on test_mutability(json_array(array[row(c)] returning jsonb)); --
error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_array(array[row(c)] ret...
+ ^
+create index on test_mutability(json_array(row(1) returning jsonb)); -- error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_array(row(1) returning ...
+ ^
+create index on test_mutability(json_array(f7 returning jsonb)); -- error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_array(f7 returning json...
+ ^
+create index on test_mutability(json_array(f8 returning jsonb)); -- error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_mutability(json_array(f8 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
@@ -1393,6 +1424,12 @@ alter table test_mutability add column f11 json
generated always as (json_array(
ERROR: generation expression is not immutable
alter table test_mutability add column f11 json generated always as
(json_array(f6 returning json)); -- error
ERROR: generation expression is not immutable
+alter table test_mutability add column f11 json generated always as
(json_array(array[row(f6)] returning json)); -- error
+ERROR: generation expression is not immutable
+alter table test_mutability add column f11 json generated always as
(json_array(array[row(f7)] returning json)); -- error
+ERROR: generation expression is not immutable
+alter table test_mutability add column f11 json generated always as
(json_array(array[row(f8)] returning json)); -- error
+ERROR: generation expression is not immutable
-- data type json doesn't have a default operator class for access method
"btree" so
-- we use a generated column to test whether the JSON_OBJECT expression is
-- immutable
@@ -1415,19 +1452,39 @@ alter table test_mutability add column f12 json
generated always as (json_object
ERROR: generation expression is not immutable
alter table test_mutability add column f12 json generated always as
(json_object('hello' value f6 returning json)); -- error
ERROR: generation expression is not immutable
+alter table test_mutability add column f12 json generated always as
(json_object('hello' value f7 returning json)); -- error
+ERROR: generation expression is not immutable
+alter table test_mutability add column f12 json generated always as
(json_object('hello' value row(f2) 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;
--- Range/multirange with immutable subtype should be considered immutable
+drop type comp4;
+drop cast (myr AS json);
+drop function myr_json;
+drop type myr;
+-- User-defined range/multirange always considered as mutable
create type range_int as range(subtype=int);
create table test_range_immutable(r range_int, m multirange_int);
-create index on test_range_immutable(json_array(r returning jsonb)); -- ok
-create index on test_range_immutable(json_array(m returning jsonb)); -- ok
-create index on test_range_immutable(json_object('key' value r returning
jsonb)); -- ok
-create index on test_range_immutable(json_object('key' value m returning
jsonb)); -- ok
+create index on test_range_immutable(json_array(r returning jsonb)); -- error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_range_immutable(json_array(r returning ...
+ ^
+create index on test_range_immutable(json_array(m returning jsonb)); -- error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_range_immutable(json_array(m returning ...
+ ^
+create index on test_range_immutable(json_object('key' value r returning
jsonb)); -- error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_range_immutable(json_object('key' value...
+ ^
+create index on test_range_immutable(json_object('key' value m returning
jsonb)); -- error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_range_immutable(json_object('key' value...
+ ^
drop table test_range_immutable;
drop type range_int;
-- IS JSON predicate
diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql
index 9fa203ccb53..504d00b5b2b 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -468,13 +468,20 @@ 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 type comp4 as (a int, b int);
+create type myr AS range (subtype = int);
+create function myr_json(myr) returns json volatile
+ language sql AS $$ SELECT json_build_array(random()) $$;
+create cast (myr AS json) with function myr_json(myr);
create table test_mutability(
a text[], b timestamp, c timestamptz,
d date, f1 comp1[], f2 timestamp[],
f3 d_comp1[],
f4 mydomainrange[],
f5 comp3,
- f6 mydomainmultirange);
+ f6 mydomainmultirange,
+ f7 comp4,
+ f8 myr);
-- JSON_OBJECTAGG, JSON_ARRAYAGG are aggregate functions, cannot be used in
index
create index xx on test_mutability(json_objectagg(a: b absent on null with
unique keys returning jsonb));
@@ -493,6 +500,12 @@ 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
create index on test_mutability(json_array(f6 returning jsonb)); -- error
+create index on test_mutability(json_array(array[f6] returning jsonb)); --
error
+create index on test_mutability(json_array(row(f6) returning jsonb)); -- error
+create index on test_mutability(json_array(array[row(c)] returning jsonb)); --
error
+create index on test_mutability(json_array(row(1) returning jsonb)); -- error
+create index on test_mutability(json_array(f7 returning jsonb)); -- error
+create index on test_mutability(json_array(f8 returning jsonb)); -- error
-- jsonb: create expression index via json_object
create index on test_mutability(json_object('hello' value a returning jsonb));
-- ok
@@ -519,6 +532,9 @@ alter table test_mutability add column f11 json generated
always as (json_array(
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
alter table test_mutability add column f11 json generated always as
(json_array(f6 returning json)); -- error
+alter table test_mutability add column f11 json generated always as
(json_array(array[row(f6)] returning json)); -- error
+alter table test_mutability add column f11 json generated always as
(json_array(array[row(f7)] returning json)); -- error
+alter table test_mutability add column f11 json generated always as
(json_array(array[row(f8)] returning json)); -- error
-- data type json doesn't have a default operator class for access method
"btree" so
-- we use a generated column to test whether the JSON_OBJECT expression is
@@ -533,6 +549,8 @@ alter table test_mutability add column f12 json generated
always as (json_object
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
alter table test_mutability add column f12 json generated always as
(json_object('hello' value f6 returning json)); -- error
+alter table test_mutability add column f12 json generated always as
(json_object('hello' value f7 returning json)); -- error
+alter table test_mutability add column f12 json generated always as
(json_object('hello' value row(f2) returning json)); -- error
drop table test_mutability;
drop domain d_comp1;
@@ -540,14 +558,18 @@ drop type comp3;
drop type mydomainrange;
drop domain mydomain;
drop type comp1;
+drop type comp4;
+drop cast (myr AS json);
+drop function myr_json;
+drop type myr;
--- Range/multirange with immutable subtype should be considered immutable
+-- User-defined range/multirange always considered as mutable
create type range_int as range(subtype=int);
create table test_range_immutable(r range_int, m multirange_int);
-create index on test_range_immutable(json_array(r returning jsonb)); -- ok
-create index on test_range_immutable(json_array(m returning jsonb)); -- ok
-create index on test_range_immutable(json_object('key' value r returning
jsonb)); -- ok
-create index on test_range_immutable(json_object('key' value m returning
jsonb)); -- ok
+create index on test_range_immutable(json_array(r returning jsonb)); -- error
+create index on test_range_immutable(json_array(m returning jsonb)); -- error
+create index on test_range_immutable(json_object('key' value r returning
jsonb)); -- error
+create index on test_range_immutable(json_object('key' value m returning
jsonb)); -- error
drop table test_range_immutable;
drop type range_int;
--
2.34.1
