On Sun, Oct 18, 2026 at 12:32 PM Manu <[email protected]> wrote: > > shihao zhong <[email protected]> writes: > > Named composite types still have the problem. ALTER TYPE can add a > > timestamptz after the index is built. > > I measured this on 18.6, 19rc1 and master, and it is broader than a mutable > attribute: a pure structural change does it too, with no mutable type and no > GUC. Adding a plain int attribute is enough. > > CREATE TYPE c1 AS (x int); > CREATE TABLE t1 (v c1); > INSERT INTO t1 VALUES (row(1)); > CREATE INDEX t1_i ON t1 ((json_array(v returning jsonb))); > ALTER TYPE c1 ADD ATTRIBUTE y int; > SELECT count(*) FROM t1 > WHERE json_array(v returning jsonb) = > json_array(row(1, null)::c1 returning jsonb); > > The index scan returns 0 and a seq scan returns 1; REINDEX restores 1, so the
The attached patch should resolve these issues. Note that float8out and float4out are considered immutable for PG19, we can only change this in PG20. see https://www.postgresql.org/message-id/19644-d6db3fb5e5ad92f4%40postgresql.org -- jian https://www.enterprisedb.com/
From 5e38d1b66bfe24af4887d9818bb6abe13d5ccc9f Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Thu, 22 Oct 2026 20:00:43 +0800 Subject: [PATCH v3 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 | 132 ++++++++++++++++---------- src/include/utils/jsonfuncs.h | 3 +- src/test/regress/expected/sqljson.out | 47 ++++++++- src/test/regress/sql/sqljson.sql | 24 ++++- 6 files changed, 155 insertions(+), 55 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..89dc96a77f9 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) 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,33 @@ 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_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); + *has_mutable = true; + return; + } + else if (att_typtype == TYPTYPE_RANGE || + att_typtype == TYPTYPE_MULTIRANGE) + { + /* a cast to json, if any, is used instead of the output function */ + Oid castfunc = get_json_cast_for_type(typoid); - for (int i = 0; i < tupdesc->natts; i++) + if (OidIsValid(castfunc)) { - Form_pg_attribute attr = TupleDescAttr(tupdesc, i); - - if (attr->attisdropped) - continue; - - json_check_mutability(attr->atttypid, is_jsonb, has_mutable); - if (*has_mutable) - break; + if (func_volatile(castfunc) != PROVOLATILE_IMMUTABLE) + *has_mutable = true; } - 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); + 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 +6128,78 @@ 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: + case FLOAT4OID: + case FLOAT8OID: + /* 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..57bbca04901 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,12 +1452,20 @@ 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; +drop type comp4; +drop cast (myr AS json); +drop function myr_json; +drop type myr; -- Range/multirange with immutable subtype should be considered immutable create type range_int as range(subtype=int); create table test_range_immutable(r range_int, m multirange_int); diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql index 9fa203ccb53..52f0762ca0c 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,6 +558,10 @@ 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 create type range_int as range(subtype=int); -- 2.34.1
