hi. rebase due to conflict in https://git.postgresql.org/cgit/postgresql.git/commit/?id=ba75f717526cbaa9000b546aac456e43d03aaf53
-- jian https://www.enterprisedb.com/
From 592c2f43485f6df2a58ab762dd2c4c53eede45ba Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Thu, 8 Jan 2026 15:01:16 +0800 Subject: [PATCH v4 1/1] make to_json, to_jsonb immutability test complete make to_json_is_immutable and to_jsonb_is_immutable to recurse into composite, range, and array element types. discussion: https://postgr.es/m/CACJufxFz%3DOsXQdsMJ-cqoqspD9aJrwntsQP-U2A-UaV_M%2B-S9g%40mail.gmail.com commitfest: https://commitfest.postgresql.org/patch/5759 --- src/backend/optimizer/util/clauses.c | 10 +- src/backend/utils/adt/json.c | 73 +++++++++++--- src/backend/utils/adt/jsonb.c | 73 +++++++++++--- src/include/utils/json.h | 2 +- src/include/utils/jsonb.h | 2 +- src/test/regress/expected/sqljson.out | 138 ++++++++++++++++++++++++++ src/test/regress/sql/sqljson.sql | 70 +++++++++++++ 7 files changed, 337 insertions(+), 31 deletions(-) diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 39d35827c35..4b63a7d32e0 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -417,10 +417,14 @@ contain_mutable_functions_walker(Node *node, void *context) foreach(lc, ctor->args) { Oid typid = exprType(lfirst(lc)); + bool contain_mutable = false; - if (is_jsonb ? - !to_jsonb_is_immutable(typid) : - !to_json_is_immutable(typid)) + if (is_jsonb) + to_jsonb_is_immutable(typid, &contain_mutable); + else + to_json_is_immutable(typid, &contain_mutable); + + if (contain_mutable) return true; } diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c index 78e84727fdc..29c5308ae17 100644 --- a/src/backend/utils/adt/json.c +++ b/src/backend/utils/adt/json.c @@ -14,6 +14,7 @@ #include "postgres.h" #include "access/htup_details.h" +#include "access/relation.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" #include "common/hashfn.h" @@ -29,6 +30,7 @@ #include "utils/json.h" #include "utils/jsonfuncs.h" #include "utils/lsyscache.h" +#include "utils/rel.h" #include "utils/typcache.h" @@ -693,15 +695,61 @@ 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. + * At present, datetimes are all considered mutable, because they depend on + * timezone. + * + * The caller should set contain_mutable to false first! This function will + * recurse through the type’s element types to check for mutability. If any + * element type is mutable, it will set contain_mutable to true. */ -bool -to_json_is_immutable(Oid typoid) +void +to_json_is_immutable(Oid typoid, bool *contain_mutable) { + char att_typtype = get_typtype(typoid); JsonTypeCategory tcategory; Oid outfuncoid; + Oid att_typelem; + + /* since this function recurses, it could be driven to stack overflow */ + check_stack_depth(); + + Assert(contain_mutable != NULL); + + if (*contain_mutable == true) + return; + + if (att_typtype == TYPTYPE_DOMAIN) + to_json_is_immutable(getBaseType(typoid), contain_mutable); + else if (att_typtype == TYPTYPE_COMPOSITE) + { + /* + * For a composite type, recurse into its attributes. + */ + Relation relation = relation_open(get_typ_typrelid(typoid), + AccessShareLock); + + TupleDesc tupdesc = RelationGetDescr(relation); + + for (int i = 0; i < tupdesc->natts; i++) + { + Form_pg_attribute attr = TupleDescAttr(tupdesc, i); + + if (attr->attisdropped) + continue; + + to_json_is_immutable(attr->atttypid, contain_mutable); + } + relation_close(relation, AccessShareLock); + } + else if (att_typtype == TYPTYPE_RANGE) + { + to_json_is_immutable(get_range_subtype(typoid), contain_mutable); + } + else if (OidIsValid((att_typelem = get_element_type(typoid)))) + { + /* recurse into array element type */ + to_json_is_immutable(att_typelem, contain_mutable); + } json_categorize_type(typoid, false, &tcategory, &outfuncoid); @@ -711,26 +759,25 @@ to_json_is_immutable(Oid typoid) case JSONTYPE_JSON: case JSONTYPE_JSONB: case JSONTYPE_NULL: - return true; + break; case JSONTYPE_DATE: case JSONTYPE_TIMESTAMP: case JSONTYPE_TIMESTAMPTZ: - return false; + *contain_mutable = true; + break; case JSONTYPE_ARRAY: - return false; /* TODO recurse into elements */ - case JSONTYPE_COMPOSITE: - return false; /* TODO recurse into fields */ + break; case JSONTYPE_NUMERIC: case JSONTYPE_CAST: case JSONTYPE_OTHER: - return func_volatile(outfuncoid) == PROVOLATILE_IMMUTABLE; + if (func_volatile(outfuncoid) != PROVOLATILE_IMMUTABLE) + *contain_mutable = true; + break; } - - return false; /* not reached */ } /* diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c index 28e7f80d77f..e54092c5e3f 100644 --- a/src/backend/utils/adt/jsonb.c +++ b/src/backend/utils/adt/jsonb.c @@ -13,6 +13,7 @@ #include "postgres.h" #include "access/htup_details.h" +#include "access/relation.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" #include "funcapi.h" @@ -24,6 +25,7 @@ #include "utils/jsonb.h" #include "utils/jsonfuncs.h" #include "utils/lsyscache.h" +#include "utils/rel.h" #include "utils/typcache.h" typedef struct JsonbAggState @@ -1078,15 +1080,61 @@ 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. + * At present, datetimes are all considered mutable, because they depend on + * timezone. + * + * The caller should set contain_mutable to false first! This function will + * recurse through the type’s element types to check for mutability. If any + * element type is mutable, it will set contain_mutable to true. */ -bool -to_jsonb_is_immutable(Oid typoid) +void +to_jsonb_is_immutable(Oid typoid, bool *contain_mutable) { + char att_typtype = get_typtype(typoid); JsonTypeCategory tcategory; Oid outfuncoid; + Oid att_typelem; + + /* since this function recurses, it could be driven to stack overflow */ + check_stack_depth(); + + Assert(contain_mutable != NULL); + + if (*contain_mutable == true) + return; + + if (att_typtype == TYPTYPE_DOMAIN) + to_jsonb_is_immutable(getBaseType(typoid), contain_mutable); + else if (att_typtype == TYPTYPE_COMPOSITE) + { + /* + * For a composite type, recurse into its attributes. + */ + Relation relation = relation_open(get_typ_typrelid(typoid), + AccessShareLock); + + TupleDesc tupdesc = RelationGetDescr(relation); + + for (int i = 0; i < tupdesc->natts; i++) + { + Form_pg_attribute attr = TupleDescAttr(tupdesc, i); + + if (attr->attisdropped) + continue; + + to_jsonb_is_immutable(attr->atttypid, contain_mutable); + } + relation_close(relation, AccessShareLock); + } + else if (att_typtype == TYPTYPE_RANGE) + { + to_jsonb_is_immutable(get_range_subtype(typoid), contain_mutable); + } + else if (OidIsValid((att_typelem = get_element_type(typoid)))) + { + /* recurse into array element type */ + to_jsonb_is_immutable(att_typelem, contain_mutable); + } json_categorize_type(typoid, true, &tcategory, &outfuncoid); @@ -1096,26 +1144,25 @@ to_jsonb_is_immutable(Oid typoid) case JSONTYPE_BOOL: case JSONTYPE_JSON: case JSONTYPE_JSONB: - return true; + break; case JSONTYPE_DATE: case JSONTYPE_TIMESTAMP: case JSONTYPE_TIMESTAMPTZ: - return false; + *contain_mutable = true; + break; case JSONTYPE_ARRAY: - return false; /* TODO recurse into elements */ - case JSONTYPE_COMPOSITE: - return false; /* TODO recurse into fields */ + break; case JSONTYPE_NUMERIC: case JSONTYPE_CAST: case JSONTYPE_OTHER: - return func_volatile(outfuncoid) == PROVOLATILE_IMMUTABLE; + if (func_volatile(outfuncoid) != PROVOLATILE_IMMUTABLE) + *contain_mutable = true; + break; } - - return false; /* not reached */ } /* diff --git a/src/include/utils/json.h b/src/include/utils/json.h index f8cc52b1e78..7e1548ec3c4 100644 --- a/src/include/utils/json.h +++ b/src/include/utils/json.h @@ -22,7 +22,7 @@ extern void escape_json_with_len(StringInfo buf, const char *str, int len); extern void escape_json_text(StringInfo buf, const text *txt); extern char *JsonEncodeDateTime(char *buf, Datum value, Oid typid, const int *tzp); -extern bool to_json_is_immutable(Oid typoid); +extern void to_json_is_immutable(Oid typoid, bool *contain_mutable); extern Datum json_build_object_worker(int nargs, const Datum *args, const bool *nulls, const Oid *types, bool absent_on_null, bool unique_keys); diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h index ca13efba0fb..b0108dea294 100644 --- a/src/include/utils/jsonb.h +++ b/src/include/utils/jsonb.h @@ -457,7 +457,7 @@ extern Datum jsonb_set_element(Jsonb *jb, const Datum *path, int path_len, JsonbValue *newval); extern Datum jsonb_get_element(Jsonb *jb, const Datum *path, int npath, bool *isnull, bool as_text); -extern bool to_jsonb_is_immutable(Oid typoid); +extern void to_jsonb_is_immutable(Oid typoid, bool *contain_mutable); extern Datum jsonb_build_object_worker(int nargs, const Datum *args, const bool *nulls, const Oid *types, bool absent_on_null, bool unique_keys); diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out index c7b9e575445..8fc2c1f9078 100644 --- a/src/test/regress/expected/sqljson.out +++ b/src/test/regress/expected/sqljson.out @@ -1109,6 +1109,144 @@ 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 mutabilily 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, mydomainrange, 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..ad2d3b19d06 100644 --- a/src/test/regress/sql/sqljson.sql +++ b/src/test/regress/sql/sqljson.sql @@ -386,6 +386,76 @@ SELECT JSON_ARRAY(SELECT i FROM (VALUES (1), (2), (NULL), (4)) foo(i) RETURNING DROP VIEW json_array_subquery_view; +-- Test mutabilily 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, mydomainrange, comp1; + -- IS JSON predicate SELECT NULL IS JSON; SELECT NULL IS NOT JSON; -- 2.34.1
