Hi Andrew,
Commit ecd9288624a (patch by Jian, CF 5759) makes 19 accept
JSON_ARRAY() and JSON_OBJECT() expression indexes that 18 rejects, and
several of them return wrong results after a later ALTER TYPE or
CREATE/DROP CAST. The attached v5 is Jian's fix plus a small one of
mine for arrays; it passes check-world on master and REL_19_STABLE.
Robert asked us to move this here from his thread; history at the end.
The shortest repro (repro.sql.txt):
CREATE TYPE c AS (x int);
CREATE TABLE t (v c);
INSERT INTO t VALUES (ROW(1));
CREATE INDEX t_i ON t ((json_array(v RETURNING jsonb)));
ALTER TYPE c ADD ATTRIBUTE y int;
SET enable_seqscan = off;
SELECT count(*) FROM t
WHERE json_array(v RETURNING jsonb) = '[{"x": 1, "y": null}]';
On 19 and master the index scan returns 0 and a seq scan returns 1;
after REINDEX the index returns 1 too. 18.6 rejects the CREATE INDEX.
contain_mutable_functions() asks to_json_is_immutable() about each
argument of these constructors. In 18 every array and composite was
mutable; ecd9288624a recurses into them, but the JSON text of some
types changes while the stored values don't. Case by case, on 18.6 /
19 / 19 with v5 ("stale": index and seq scan disagree afterwards):
- composite, ALTER TYPE ADD/RENAME/DROP ATTRIBUTE (shihao's case):
rejected / stale / rejected; unique and partial indexes, stored
generated columns and partition keys break the same way
- user-defined range, CREATE or DROP CAST: rejected / stale / rejected
- range over an enum with a cast, RENAME VALUE: rejected / stale /
rejected
- array of a user-defined base type, CREATE CAST: rejected / stale /
rejected (by 0002)
- float4, float8, then extra_float_digits = 0: stale / stale / stale,
since float8out is still IMMUTABLE in 18 and 19; on master it is
STABLE now, and there v5 rejects them
- int4range, int4multirange: rejected / accepted / accepted, which is
right: json casts are looked up only for user-defined types
(jsonfuncs.c:6060 on master); a cast on int4range is never used.
v5-0001 is Jian's v4 code unchanged, with a new commit message that
describes the problem and the fix (v4 reused Robert's v1-0002 one). It
makes composites, record, enums, user-defined ranges and multiranges,
anyarray and anycompatiblearray mutable, and stops treating float4 and
float8 as always immutable. v5-0002, mine, does the same for arrays of
a user-defined base type or a domain over one, with a test. Both apply
with git am on master eaa5961efc6 and REL_19_STABLE e33700c7670, build
with assertions and no warnings, and pass check-world on both.
Of the 342 types in matrix.txt, the only ones v5 rejects while 18.6
and the unpatched branch accept them are an enum or a user-defined
range with an IMMUTABLE cast to json (which can be dropped later), and
on master float4, float8 and a domain over float8.
Out of scope: a user-defined base type used directly or through a
domain goes stale the same way on 18.6; it is older than 19.
History (2026, UTC):
- Jul 2, Robert's v1-0002 drops the record special case, not knowing
it was new in 19:
https://postgr.es/m/ca+tgmoaiothgqrw9otgsmzbucnqj2jdagtbs6o3fkjcd+lf...@mail.gmail.com
- Oct 13, Jian proposes that fix alone for 19 (v2):
https://postgr.es/m/cacjufxf4yyqstavphkfyhrfvh1ro7xwh39u9prj9fgi5wb_...@mail.gmail.com
- Oct 18, shihao: a named composite plus ADD ATTRIBUTE still goes stale:
https://postgr.es/m/CAGRkXqTgwNKmZVK=3cpyzadcatheajoysyb072vwnuyutx1...@mail.gmail.com
- Oct 18, me: any structural change does, in every index form:
https://postgr.es/m/[email protected]
- Oct 22, Jian's v3 makes every composite mutable:
https://postgr.es/m/CACJufxHQJ=5ee1fmhkqnjz4tax4q6dwsgieejjggb1ahcf1...@mail.gmail.com
- Oct 22, me: a range over an enum with a cast still goes stale:
https://postgr.es/m/[email protected]
- Oct 23, shihao: v3 still treats float4/float8 as immutable on master:
https://postgr.es/m/cagrkxqqz500qngzrf36xu+vvol9e3+fgyvvx8zw0f6jfnkm...@mail.gmail.com
- Oct 23, Jian's v4 adds enums and user-defined ranges:
https://postgr.es/m/CACJufxER-VJzaH33HYzoU-Z4ZOd_iJCX9+3r5TLhcEEN=NS=f...@mail.gmail.com
- Oct 23, me: arrays of a user-defined base type still go stale:
https://postgr.es/m/[email protected]
- Nov 3, Robert asks for this thread and the open item:
https://postgr.es/m/CA+TgmoYJMxeY2XoQ88qHgi2NCA2LbQd=aovwcoaplq3kz22...@mail.gmail.com
I'll add it to the PG19 open items, with Andrew as owner, as soon as
my wiki editor access comes through.
regards,
Manu
From 0000000000000000000000000000000000000000 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Fri, 23 Oct 2026 12:46:04 +0800
Subject: [PATCH v5 1/2] Fix immutability check of SQL/JSON constructors for
container types
contain_mutable_functions() asks to_json_is_immutable() or
to_jsonb_is_immutable() about the type of every argument of
JSON_ARRAY(), JSON_OBJECT() and the other SQL/JSON constructors.
Until 18 both returned "mutable" for any array or composite argument.
Commit ecd9288624a made them recurse into container types instead: an
array, composite, domain, range or multirange is now treated as
immutable when its element, attribute or subtype types are.
That is not enough, because the JSON text of several of these types
can change while the stored values do not, and nothing then rebuilds
an index, a stored generated column or a partition key computed from
it:
- The output of a composite type includes the attribute names and
their number, which ALTER TYPE ... ADD/DROP/RENAME ATTRIBUTE, or
ALTER TABLE on a table's row type, can change.
- The output of an enum changes with ALTER TYPE ... RENAME VALUE.
Before ecd9288624a an enum inside a container was never reached.
Now a range over an enum with an IMMUTABLE cast to json looks
immutable, although range_out prints the enum with enum_out and
not with the cast.
- A user-defined range or multirange is converted through a cast to
json if one exists, looked up at run time, so creating or dropping
such a cast later changes the output.
- record values built with ROW() reached the JSONTYPE_COMPOSITE case,
which treated them as immutable.
- float4 and float8 were classified as JSONTYPE_NUMERIC, which is
treated as immutable, but float4out and float8out are STABLE on
master, so float8::text is rejected there while JSON_ARRAY(float8)
was accepted.
18 rejects all of these expressions. 19 accepts them, and after one
of the changes above an index scan and a sequential scan return
different rows until REINDEX. A unique index can accept a duplicate,
a stored generated column keeps the old value, and partition pruning
can skip the partition that holds a row.
This patch makes json_check_mutability() treat as mutable:
- every composite type and record (attribute names can change);
- every enum (RENAME VALUE);
- user-defined ranges and multiranges; built-in ones still recurse
into the subtype, since a cast to json is looked up only for
user-defined types (see json_categorize_type());
- record, anyarray and anycompatiblearray, whose element types are
not known until run time.
Any other type is judged by the volatility of its user-defined cast to
json if there is one, else of its output function. float4 and float8
are no longer special-cased, so they follow float4out and float8out:
mutable on master, unchanged on 19. The is_jsonb argument of
json_check_mutability() goes away, as the same cast lookup is used for
json and jsonb.
Compared with 18, this still accepts JSON_ARRAY() and JSON_OBJECT()
over built-in ranges and multiranges with an immutable subtype, such
as int4range, which 18 rejected. It now rejects them over an enum, or
over a user-defined range, that has an IMMUTABLE cast to json, which
18 accepted; such a cast can be dropped after CREATE INDEX. On master
it also rejects float4 and float8, which 18 accepted.
Author: jian he <[email protected]>
Reported-by: Robert Haas <[email protected]>
Reported-by: shihao zhong <[email protected]>
Discussion: https://postgr.es/m/CACJufxFz=osxqdsmj-cqoqspd9ajrwntsqp-u2a-uav_m+-...@mail.gmail.com
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 | 135 +++++++++++++++++---------
src/include/utils/jsonfuncs.h | 3 +-
src/test/regress/expected/sqljson.out | 69 +++++++++++--
src/test/regress/sql/sqljson.sql | 34 +++++--
6 files changed, 182 insertions(+), 63 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 1d3c8c0865a..3bee50f948b 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
@@ -6088,18 +6089,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();
@@ -6111,41 +6111,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_COMPOSITE)
+ else if (att_typtype == TYPTYPE_ENUM)
{
/*
- * For a composite type, recurse into its attributes. Use the
- * typcache to avoid opening the relation directly.
+ * ALTER TYPE ... RENAME VALUE can change an enum's output without
+ * rebuilding indexes, so an enum 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_COMPOSITE)
{
- json_check_mutability(get_range_subtype(typoid), is_jsonb,
- has_mutable);
+ /*
+ * The output includes the attribute names, which ALTER TYPE can
+ * change, so a composite is never immutable.
+ */
+ *has_mutable = true;
return;
}
- else if (att_typtype == TYPTYPE_MULTIRANGE)
+ else if (att_typtype == TYPTYPE_RANGE ||
+ 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
@@ -6155,36 +6156,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.56.0
From 0000000000000000000000000000000000000000 Mon Sep 17 00:00:00 2001
From: Manuel Reyes Bravo <[email protected]>
Date: Tue, 3 Nov 2026 12:44:24 -0300
Subject: [PATCH v5 2/2] Treat arrays of user-defined base types as mutable in
JSON check
Since ecd9288624a, json_check_mutability() recurses into the element
type of an array. When the element is a user-defined base type, or a
domain over one, it is converted through a cast to json if one exists,
looked up at run time, so a cast created or dropped after CREATE INDEX
changes the indexed value. 18 treats every array as mutable.
Treat such arrays as mutable, as user-defined ranges already are.
Arrays of built-in types, and of domains over them, are not affected.
The same type used directly, or through a domain, has had this
problem since before 19 and is left alone here.
---
src/backend/utils/adt/jsonfuncs.c | 13 +++++++++--
src/test/regress/expected/sqljson.out | 32 +++++++++++++++++++++++++++
src/test/regress/sql/sqljson.sql | 17 ++++++++++++++
3 files changed, 60 insertions(+), 2 deletions(-)
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 3bee50f948b..d8e5712f90c 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -6155,8 +6155,17 @@ json_check_mutability(Oid typoid, bool *has_mutable)
if (OidIsValid(att_typelem))
{
- /* recurse into array element type */
- json_check_mutability(att_typelem, has_mutable);
+ /*
+ * Elements of a user-defined base type are printed through a cast
+ * to json if one exists, looked up at run time, so creating or
+ * dropping that cast later changes the output. Treat such arrays
+ * as mutable, like user-defined ranges; otherwise recurse into the
+ * element type.
+ */
+ if (getBaseType(att_typelem) >= FirstNormalObjectId)
+ *has_mutable = true;
+ else
+ json_check_mutability(att_typelem, has_mutable);
return;
}
}
diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out
index 10116dc0785..f5f1056de74 100644
--- a/src/test/regress/expected/sqljson.out
+++ b/src/test/regress/expected/sqljson.out
@@ -1487,6 +1487,38 @@ LINE 1: create index on test_range_immutable(json_object('key' value...
^
drop table test_range_immutable;
drop type range_int;
+-- Arrays of a user-defined base type, or of a domain over one, are mutable:
+-- their elements are converted through a cast to json, looked up at run time
+create type mytext_t;
+create function mytext_t_in(cstring) returns mytext_t
+ language internal immutable strict as 'textin';
+NOTICE: return type mytext_t is only a shell
+create function mytext_t_out(mytext_t) returns cstring
+ language internal immutable strict as 'textout';
+NOTICE: argument type mytext_t is only a shell
+LINE 1: create function mytext_t_out(mytext_t) returns cstring
+ ^
+create type mytext_t (input = mytext_t_in, output = mytext_t_out, like = text);
+create domain d_mytext_t as mytext_t;
+create table test_base_array(a mytext_t[], b d_mytext_t[]);
+create index on test_base_array(json_array(a returning jsonb)); -- error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_base_array(json_array(a returning jsonb...
+ ^
+create index on test_base_array(json_array(b returning jsonb)); -- error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_base_array(json_array(b returning jsonb...
+ ^
+create index on test_base_array(json_object('key' value a returning jsonb)); -- error
+ERROR: functions in index expression must be marked IMMUTABLE
+LINE 1: create index on test_base_array(json_object('key' value a re...
+ ^
+drop table test_base_array;
+drop domain d_mytext_t;
+drop type mytext_t cascade;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to function mytext_t_in(cstring)
+drop cascades to function mytext_t_out(mytext_t)
-- 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 504d00b5b2b..f32b5def1ed 100644
--- a/src/test/regress/sql/sqljson.sql
+++ b/src/test/regress/sql/sqljson.sql
@@ -573,6 +573,23 @@ create index on test_range_immutable(json_object('key' value m returning jsonb))
drop table test_range_immutable;
drop type range_int;
+-- Arrays of a user-defined base type, or of a domain over one, are mutable:
+-- their elements are converted through a cast to json, looked up at run time
+create type mytext_t;
+create function mytext_t_in(cstring) returns mytext_t
+ language internal immutable strict as 'textin';
+create function mytext_t_out(mytext_t) returns cstring
+ language internal immutable strict as 'textout';
+create type mytext_t (input = mytext_t_in, output = mytext_t_out, like = text);
+create domain d_mytext_t as mytext_t;
+create table test_base_array(a mytext_t[], b d_mytext_t[]);
+create index on test_base_array(json_array(a returning jsonb)); -- error
+create index on test_base_array(json_array(b returning jsonb)); -- error
+create index on test_base_array(json_object('key' value a returning jsonb)); -- error
+drop table test_base_array;
+drop domain d_mytext_t;
+drop type mytext_t cascade;
+
-- IS JSON predicate
SELECT NULL IS JSON;
SELECT NULL IS NOT JSON;
--
2.56.0
-- Minimal repro: a json_array() expression index over a composite type.
-- 18 rejects the CREATE INDEX; 19 accepts it, and after ALTER TYPE the
-- index scan and a sequential scan disagree until REINDEX.
CREATE TYPE c AS (x int);
CREATE TABLE t (v c);
INSERT INTO t VALUES (ROW(1));
CREATE INDEX t_i ON t ((json_array(v RETURNING jsonb)));
ALTER TYPE c ADD ATTRIBUTE y int;
SET enable_seqscan = off;
SELECT count(*) AS index_scan FROM t
WHERE json_array(v RETURNING jsonb) = '[{"x": 1, "y": null}]';
RESET enable_seqscan;
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT count(*) AS seq_scan FROM t
WHERE json_array(v RETURNING jsonb) = '[{"x": 1, "y": null}]';
RESET ALL;
REINDEX INDEX t_i;
SET enable_seqscan = off;
SELECT count(*) AS index_scan_after_reindex FROM t
WHERE json_array(v RETURNING jsonb) = '[{"x": 1, "y": null}]';
Per-statement verdicts of the measurement scripts, re-run 2026-11-03.
Builds (all --enable-cassert --enable-debug, -O2):
r18 18.6 (724edf9bde9)
mbase master eaa5961efc6 r19base REL_19_STABLE e33700c7670
m0001 master + v5-0001 r190001 REL_19_STABLE + v5-0001
mv5 master + v5-0001 + v5-0002 r19v5 REL_19_STABLE + v5-0001 + 0002
CREATE INDEX / GENERATED / PARTITION BY rows: A accepted, R rejected as not
immutable, E another error. ORACLE rows (after the change the script makes):
index-scan count / seq-scan count; "!" marks a mismatch, "-" means no index
was created. BLAST rows: CREATE INDEX on json_array(v RETURNING jsonb) for
a column of that type. Columns, in order:
r18 mbase m0001 r19base r190001 mv5 r19v5
== sweep.sql
R A R A R R R L9 CREATE INDEX ON t ((json_array(v r
R R R R R R R L21 CREATE INDEX ON tk0 ((json_array(
R R R R R R R L25 CREATE INDEX ON tk0n ((json_array
R A R A R R R L31 CREATE INDEX t1_i ON t1 ((json_ar
R A R A R R R L44 CREATE INDEX t2_i ON t2 ((json_ar
R A R A R R R L57 CREATE INDEX t3_i ON t3 ((json_ar
R A R A R R R L70 CREATE INDEX t4_i ON t4 ((json_ar
R A R A R R R L81 CREATE INDEX t4b_i ON t4b ((json_
R A R A R R R L91 CREATE INDEX t5_i ON t5 ((json_ar
R A R A R R R L105 CREATE INDEX t6_i ON t6 ((json_a
R A R A R R R L117 CREATE INDEX t7_i ON t7 ((json_a
R A R A R R R L129 CREATE INDEX t8_i ON t8 ((json_o
R A R A R R R L130 CREATE INDEX t8_j ON t8 ((json_a
R A R A R R R L140 CREATE INDEX ON t9 ((json_array(
R A R A R R R L146 CREATE UNIQUE INDEX tu_i ON tu (
R A R A R R R L160 CREATE INDEX tp_i ON tp (id) WHE
R A R A R R R L169 CREATE TABLE tg (v cg, g jsonb G
R A R A R R R L179 CREATE TABLE tk (v ck) PARTITION
- 0/1! - 0/1! - - - ORACLE S0 shihao add-ts+TZ
- 0/1! - 0/1! - - - ORACLE C1 add-attr int
- 1/1 - 1/1 - - - ORACLE C1 add-attr int +REINDEX
- 0/1! - 0/1! - - - ORACLE C2 rename-attr
- 1/1 - 1/1 - - - ORACLE C2 rename-attr +REINDEX
- 0/1! - 0/1! - - - ORACLE C3 drop-attr
- 1/1 - 1/1 - - - ORACLE C3 drop-attr +REINDEX
- 0/1! - 0/1! - - - ORACLE C4 rowtype rename-col
- 1/1 - 1/1 - - - ORACLE C4 rowtype rename-col +REINDEX
- 0/1! - 0/1! - - - ORACLE C4b rowtype add-col
- 0/1! - 0/1! - - - ORACLE C5 nested rename
- 1/1 - 1/1 - - - ORACLE C5 nested rename +REINDEX
- 0/1! - 0/1! - - - ORACLE C6 domain-over-composite renam
- 1/1 - 1/1 - - - ORACLE C6 domain-over-composite renam
- 0/1! - 0/1! - - - ORACLE C7 array-of-composite rename
- 1/1 - 1/1 - - - ORACLE C7 array-of-composite rename +
- 0/1! - 0/1! - - - ORACLE C8 json_object rename
- 0/1! - 0/1! - - - ORACLE C8b returning-json::text renam
- 1/2! - 1/2! - - - ORACLE U1 unique after dup insert
- 1/0! - 1/0! - - - ORACLE P1 partial-index predicate
- 0/0 - 0/0 - - - ORACLE P1 partial-index predicate +RE
== holes.sql
R A R A R R R L52 CREATE INDEX th4_i ON th4 ((json_
R A R A R R R L78 CREATE INDEX th5_i ON th5 ((json_
A A A A A A A TRY H1a json_array(int) control
R A R A R R R TRY H1b json_array(composite)
R A R A R R R TRY H1c json_array(row(int)) anonymou
R A R A R R R TRY H1d json_array(array[row(int)]) r
R A R A R R R TRY H1e json_array(array[composite])
R A R A R R R TRY H1f json_array(nested composite)
R A R A R R R TRY H1g json_array(domain over compos
R A R A R R R TRY H1h json_array(domain over compos
R A R A R R R TRY H1i json_array(array of domain ov
R R R R R R R TRY H1j json_array(whole-row th1)
R A R A R R R TRY H1k json_object(composite) return
R R R R R R R TRY H1l to_jsonb(composite)
R R R R R R R TRY H1m to_json(composite)::text
R R R R R R R TRY H1n row_to_json(composite)::text
R R R R R R R TRY H1o json_build_array(composite)::
R R R R R R R TRY H1p jsonb_build_object(k, composi
R A R A R R R TRY H1q json_scalar(composite)::text
R R R R R R R TRY H1r json_exists PASSING composite
R A A A A A A TRY H1s json_array(int4range)
R A A A A A A TRY H1t json_array(int4multirange)
R R R R R R R TRY H1u json_array(interval)
R R R R R R R TRY H1v json_array(money)
A A A A A A A TRY H1w json_array(timetz)
A A A A A A A TRY H1x json_array(time)
R A R A R R R TRY H1y gencol json_array(composite)
R A R A R R R TRY H1z partkey json_array(composite)
- 0/1! - 0/1! - - - ORACLE H4 range + CREATE CAST
- 1/1 - 1/1 - - - ORACLE H4 range + CREATE CAST +REINDE
- 0/1! - 0/1! - - - ORACLE H4b range + DROP CAST
A A R A R R R TRY H5 control json_array(e5) direct
1/1 0/1! - 0/1! - - - ORACLE H5 range-of-enum + RENAME VALU
1/1 1/1 - 1/1 - - - ORACLE H5 range-of-enum + RENAME VALU
== h6.sql
A A A A A A A L11 CREATE INDEX th6_i ON th6 ((json_
R A R A R R R L26 CREATE INDEX t5_i ON t5 ((json_ar
R R R R R R R L34 CREATE INDEX t7_i ON t7 ((json_ar
E E E E E E E L38 CREATE INDEX t5m_i ON t5m ((json_
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE H6 user base type + CREATE CAS
- 0/1! - 0/1! - - - ORACLE H5min range-of-enum + RENAME V
== h5m.sql
R A R A R R R L11 CREATE INDEX t5m_i ON t5m ((json_
R A R A R R R L18 CREATE INDEX t5a_i ON t5a ((json_
- 0/1! - 0/1! - - - ORACLE H5m multirange-of-enum + RENAM
== repro-range-enum.sql
R A R A R R R L10 CREATE INDEX t_i ON t ((json_arra
== floats.sql
A A R A A R A L10 CREATE INDEX tf_i ON tf ((json_ar
A A R A A R A L19 CREATE INDEX tf4_i ON tf4 ((json_
A R R A A R A L28 CREATE INDEX tft_i ON tft ((f::te
0/1! 0/1! - 0/1! 0/1! - 0/1! ORACLE F8 float8 + extra_float_digits
0/1! 0/1! - 0/1! 0/1! - 0/1! ORACLE F4 float4 + extra_float_digits
== builtin-ranges.sql
R A A A A A A L7 CREATE INDEX tbr_r ON tbr ((json_a
R A A A A A A L8 CREATE INDEX tbr_m ON tbr ((json_a
R A A A A A A L9 CREATE INDEX tbr_ro ON tbr ((json_
R A A A A A A L10 CREATE INDEX tbr_n ON tbr ((json_
R R R R R R R L11 CREATE INDEX tbr_d ON tbr ((json_
R R R R R R R L12 CREATE INDEX tbr_ts ON tbr ((json
R A A A A A A L13 CREATE INDEX tbr_ra ON tbr ((json
- 1/1 1/1 1/1 1/1 1/1 1/1 ORACLE BR int4range + CREATE CAST (su
== jian-demo.sql
R A R A R R R L9 CREATE INDEX tr_idx ON tr ((json_a
== jian-demo-explain.sql
- 0/1! - 0/1! - - - ORACLE JD jian demo myr + CREATE CAST
== holes4.sql
A A A A A A A TRY Q1 json_array(user base type), no
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE Q1 user base type + CREATE CAS
1/1 1/1 1/1 1/1 1/1 1/1 1/1 ORACLE Q1 user base type + CREATE CAS
A A A A A A A TRY Q2 json_array(user base type), ca
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE Q2 user base type + DROP CAST
A A A A A A A TRY Q3 json_array(domain over user ba
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE Q3 domain over user base type
A A A A A A A TRY Q3b json_array(domain over user b
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE Q3b domain over user base type
R A A A A R R TRY Q4 json_array(user base type[]),
- 0/1! 0/1! 0/1! 0/1! - - ORACLE Q4 user base type[] + CREATE C
A A A A A A A TRY Q5 json_array(domain), cast on th
1/1 1/1 1/1 1/1 1/1 1/1 1/1 ORACLE Q5 domain + CREATE CAST on the
R A R A R R R TRY Q6a json_array(user range[])
R A R A R R R TRY Q6b json_array(domain over user r
R A R A R R R TRY Q6c json_array(user multirange[])
R A R A R R R TRY Q6d json_array(array of domain ov
A A R A R R R TRY Q6e json_array(enum with IMMUTABL
R A R A R R R TRY Q6f json_array(enum[] with IMMUTA
R A R A R R R TRY Q6g json_object(user range)
R A R A R R R TRY Q6h json_array(user range) return
R A R A R R R TRY Q6i gencol json_array(user range[
R A R A R R R TRY Q6j partkey json_array(user range
== blast.sql
A A A A A A A BLAST "char"
R A A A A A A BLAST "char"[]
R R R R R R R BLAST aclitem[]
A A A A A A A BLAST bigint
R A A A A A A BLAST bigint[]
A A A A A A A BLAST bit
A A A A A A A BLAST bit varying
R A A A A A A BLAST bit varying[]
R A A A A A A BLAST bit[]
A A A A A A A BLAST boolean
R A A A A A A BLAST boolean[]
A R R A A R A BLAST box
R R R A A R A BLAST box[]
A R R A A R A BLAST bytea
R R R A A R A BLAST bytea[]
A A A A A A A BLAST character
A A A A A A A BLAST character varying
R A A A A A A BLAST character varying[]
R A A A A A A BLAST character[]
A A A A A A A BLAST cid
R A A A A A A BLAST cid[]
A A A A A A A BLAST cidr
R A A A A A A BLAST cidr[]
A R R A A R A BLAST circle
R R R A A R A BLAST circle[]
E E E E E E E BLAST cstring[]
R R R R R R R BLAST date
R R R R R R R BLAST date[]
R R R R R R R BLAST datemultirange
R R R R R R R BLAST datemultirange[]
R R R R R R R BLAST daterange
R R R R R R R BLAST daterange[]
A A R A A R A BLAST double precision
R A R A A R A BLAST double precision[]
A A A A A A A BLAST gtsvector
R A A A A A A BLAST gtsvector[]
A A A A A A A BLAST inet
R A A A A A A BLAST inet[]
R A A A A A A BLAST int2vector
R A A A A A A BLAST int2vector[]
R A A A A A A BLAST int4multirange
R A A A A A A BLAST int4multirange[]
R A A A A A A BLAST int4range
R A A A A A A BLAST int4range[]
R A A A A A A BLAST int8multirange
R A A A A A A BLAST int8multirange[]
R A A A A A A BLAST int8range
R A A A A A A BLAST int8range[]
A A A A A A A BLAST integer
R A A A A A A BLAST integer[]
R R R R R R R BLAST interval
R R R R R R R BLAST interval[]
A A A A A A A BLAST json
R A A A A A A BLAST json[]
A A A A A A A BLAST jsonb
R A A A A A A BLAST jsonb[]
A A A A A A A BLAST jsonpath
R A A A A A A BLAST jsonpath[]
A R R A A R A BLAST line
R R R A A R A BLAST line[]
A R R A A R A BLAST lseg
R R R A A R A BLAST lseg[]
A A A A A A A BLAST macaddr
A A A A A A A BLAST macaddr8
R A A A A A A BLAST macaddr8[]
R A A A A A A BLAST macaddr[]
R R R R R R R BLAST money
R R R R R R R BLAST money[]
A A A A A A A BLAST name
R A A A A A A BLAST name[]
A A A A A A A BLAST numeric
R A A A A A A BLAST numeric[]
R A A A A A A BLAST nummultirange
R A A A A A A BLAST nummultirange[]
R A A A A A A BLAST numrange
R A A A A A A BLAST numrange[]
A A A A A A A BLAST oid
R A A A A A A BLAST oid[]
R A A A A A A BLAST oidvector
R A A A A A A BLAST oidvector[]
A R R A A R A BLAST path
R R R A A R A BLAST path[]
R R R R R R R BLAST pg_aggregate[]
R A R A R R R BLAST pg_aios[]
R R R R R R R BLAST pg_am[]
R A R A R R R BLAST pg_amop[]
R R R R R R R BLAST pg_amproc[]
R A R A R R R BLAST pg_attrdef[]
E E E E E E E BLAST pg_attribute[]
R A R A R R R BLAST pg_auth_members[]
R R R R R R R BLAST pg_authid[]
R A R A R R R BLAST pg_available_extension_versions
R A R A R R R BLAST pg_available_extensions[]
R A R A R R R BLAST pg_backend_memory_contexts[]
A A A A A A A BLAST pg_brin_bloom_summary
A R R A A R A BLAST pg_brin_minmax_multi_summary
R A R A R R R BLAST pg_cast[]
R R R R R R R BLAST pg_class[]
R A R A R R R BLAST pg_collation[]
R A R A R R R BLAST pg_config[]
R A R A R R R BLAST pg_constraint[]
R R R R R R R BLAST pg_conversion[]
R R R R R R R BLAST pg_cursors[]
R R R R R R R BLAST pg_database[]
R A R A R R R BLAST pg_db_role_setting[]
R R R R R R R BLAST pg_default_acl[]
R A R A R R R BLAST pg_depend[]
A A A A A A A BLAST pg_dependencies
R A R A R R R BLAST pg_description[]
R A R A R R R BLAST pg_enum[]
R A R A R R R BLAST pg_event_trigger[]
R A R A R R R BLAST pg_extension[]
R A R A R R R BLAST pg_file_settings[]
R R R R R R R BLAST pg_foreign_data_wrapper[]
R R R R R R R BLAST pg_foreign_server[]
R A R A R R R BLAST pg_foreign_table[]
R A R A R R R BLAST pg_group[]
R A R A R R R BLAST pg_hba_file_rules[]
R A R A R R R BLAST pg_ident_file_mappings[]
R A R A R R R BLAST pg_index[]
R A R A R R R BLAST pg_indexes[]
R A R A R R R BLAST pg_inherits[]
R R R R R R R BLAST pg_init_privs[]
R R R R R R R BLAST pg_language[]
R R R A R R R BLAST pg_largeobject[]
R R R R R R R BLAST pg_largeobject_metadata[]
R R R R R R R BLAST pg_locks[]
A A A A A A A BLAST pg_lsn
R A A A A A A BLAST pg_lsn[]
R A R A R R R BLAST pg_matviews[]
A R R A A R A BLAST pg_mcv_list
R R R R R R R BLAST pg_namespace[]
A A A A A A A BLAST pg_ndistinct
A A A A A A A BLAST pg_node_tree
R A R A R R R BLAST pg_opclass[]
R R R R R R R BLAST pg_operator[]
R A R A R R R BLAST pg_opfamily[]
R R R R R R R BLAST pg_parameter_acl[]
R A R A R R R BLAST pg_partitioned_table[]
R A R A R R R BLAST pg_policies[]
R A R A R R R BLAST pg_policy[]
R R R R R R R BLAST pg_prepared_statements[]
R R R R R R R BLAST pg_prepared_xacts[]
R R R R R R R BLAST pg_proc[]
R A R A R R R BLAST pg_publication[]
R A R A R R R BLAST pg_publication_namespace[]
R A R A R R R BLAST pg_publication_rel[]
R A R A R R R BLAST pg_publication_tables[]
R R R R R R R BLAST pg_range[]
R A R A R R R BLAST pg_replication_origin[]
R A R A R R R BLAST pg_replication_origin_status[]
R R R R R R R BLAST pg_replication_slots[]
R A R A R R R BLAST pg_rewrite[]
R R R R R R R BLAST pg_roles[]
R A R A R R R BLAST pg_rules[]
R A R A R R R BLAST pg_seclabel[]
R A R A R R R BLAST pg_seclabels[]
R A R A R R R BLAST pg_sequence[]
R R R R R R R BLAST pg_sequences[]
R A R A R R R BLAST pg_settings[]
R R R R R R R BLAST pg_shadow[]
R A R A R R R BLAST pg_shdepend[]
R A R A R R R BLAST pg_shdescription[]
R A R A R R R BLAST pg_shmem_allocations[]
R A R A R R R BLAST pg_shmem_allocations_numa[]
R A R A R R R BLAST pg_shseclabel[]
A A A A A A A BLAST pg_snapshot
R A A A A A A BLAST pg_snapshot[]
R R R R R R R BLAST pg_stat_activity[]
R R R R R R R BLAST pg_stat_all_indexes[]
R R R R R R R BLAST pg_stat_all_tables[]
R R R R R R R BLAST pg_stat_archiver[]
R R R R R R R BLAST pg_stat_bgwriter[]
R R R R R R R BLAST pg_stat_checkpointer[]
R R R R R R R BLAST pg_stat_database[]
R R R R R R R BLAST pg_stat_database_conflicts[]
R A R A R R R BLAST pg_stat_gssapi[]
R R R R R R R BLAST pg_stat_io[]
R A R A R R R BLAST pg_stat_progress_analyze[]
R A R A R R R BLAST pg_stat_progress_basebackup[]
R A R A R R R BLAST pg_stat_progress_cluster[]
R A R A R R R BLAST pg_stat_progress_copy[]
R A R A R R R BLAST pg_stat_progress_create_index[]
R A R A R R R BLAST pg_stat_progress_vacuum[]
R R R R R R R BLAST pg_stat_recovery_prefetch[]
R R R R R R R BLAST pg_stat_replication[]
R R R R R R R BLAST pg_stat_replication_slots[]
R R R R R R R BLAST pg_stat_slru[]
R A R A R R R BLAST pg_stat_ssl[]
R R R R R R R BLAST pg_stat_subscription[]
R R R R R R R BLAST pg_stat_subscription_stats[]
R R R R R R R BLAST pg_stat_sys_indexes[]
R R R R R R R BLAST pg_stat_sys_tables[]
R R R R R R R BLAST pg_stat_user_functions[]
R R R R R R R BLAST pg_stat_user_indexes[]
R R R R R R R BLAST pg_stat_user_tables[]
R R R R R R R BLAST pg_stat_wal[]
R R R R R R R BLAST pg_stat_wal_receiver[]
R A R A R R R BLAST pg_stat_xact_all_tables[]
R A R A R R R BLAST pg_stat_xact_sys_tables[]
R A R A R R R BLAST pg_stat_xact_user_functions[]
R A R A R R R BLAST pg_stat_xact_user_tables[]
R R R R R R R BLAST pg_statio_all_indexes[]
R R R R R R R BLAST pg_statio_all_sequences[]
R R R R R R R BLAST pg_statio_all_tables[]
R R R R R R R BLAST pg_statio_sys_indexes[]
R R R R R R R BLAST pg_statio_sys_sequences[]
R R R R R R R BLAST pg_statio_sys_tables[]
R R R R R R R BLAST pg_statio_user_indexes[]
R R R R R R R BLAST pg_statio_user_sequences[]
R R R R R R R BLAST pg_statio_user_tables[]
E E E E E E E BLAST pg_statistic[]
R A R A R R R BLAST pg_statistic_ext[]
E E E E E E E BLAST pg_statistic_ext_data[]
E E E E E E E BLAST pg_stats[]
R A R A R R R BLAST pg_stats_ext[]
E E E E E E E BLAST pg_stats_ext_exprs[]
R A R A R R R BLAST pg_subscription[]
R A R A R R R BLAST pg_subscription_rel[]
R A R A R R R BLAST pg_tables[]
R R R R R R R BLAST pg_tablespace[]
R R R R R R R BLAST pg_timezone_abbrevs[]
R R R R R R R BLAST pg_timezone_names[]
R R R R R R R BLAST pg_transform[]
R R R A R R R BLAST pg_trigger[]
R A R A R R R BLAST pg_ts_config[]
R A R A R R R BLAST pg_ts_config_map[]
R A R A R R R BLAST pg_ts_dict[]
R R R R R R R BLAST pg_ts_parser[]
R R R R R R R BLAST pg_ts_template[]
R R R R R R R BLAST pg_type[]
R R R R R R R BLAST pg_user[]
R A R A R R R BLAST pg_user_mapping[]
R A R A R R R BLAST pg_user_mappings[]
R A R A R R R BLAST pg_views[]
R A R A R R R BLAST pg_wait_events[]
A R R A A R A BLAST point
R R R A A R A BLAST point[]
A R R A A R A BLAST polygon
R R R A A R A BLAST polygon[]
A A R A A R A BLAST real
R A R A A R A BLAST real[]
A A A A A A A BLAST refcursor
R A A A A A A BLAST refcursor[]
R R R R R R R BLAST regclass
R R R R R R R BLAST regclass[]
R R R R R R R BLAST regcollation
R R R R R R R BLAST regcollation[]
R R R R R R R BLAST regconfig
R R R R R R R BLAST regconfig[]
R R R R R R R BLAST regdictionary
R R R R R R R BLAST regdictionary[]
R R R R R R R BLAST regnamespace
R R R R R R R BLAST regnamespace[]
R R R R R R R BLAST regoper
R R R R R R R BLAST regoper[]
R R R R R R R BLAST regoperator
R R R R R R R BLAST regoperator[]
R R R R R R R BLAST regproc
R R R R R R R BLAST regproc[]
R R R R R R R BLAST regprocedure
R R R R R R R BLAST regprocedure[]
R R R R R R R BLAST regrole
R R R R R R R BLAST regrole[]
R R R R R R R BLAST regtype
R R R R R R R BLAST regtype[]
A A A A A A A BLAST smallint
R A A A A A A BLAST smallint[]
A A A A A A A BLAST text
R A A A A A A BLAST text[]
A A A A A A A BLAST tid
R A A A A A A BLAST tid[]
A A A A A A A BLAST time with time zone
R A A A A A A BLAST time with time zone[]
A A A A A A A BLAST time without time zone
R A A A A A A BLAST time without time zone[]
R R R R R R R BLAST timestamp with time zone
R R R R R R R BLAST timestamp with time zone[]
R R R R R R R BLAST timestamp without time zone
R R R R R R R BLAST timestamp without time zone[]
R R R R R R R BLAST tsmultirange
R R R R R R R BLAST tsmultirange[]
A A A A A A A BLAST tsquery
R A A A A A A BLAST tsquery[]
R R R R R R R BLAST tsrange
R R R R R R R BLAST tsrange[]
R R R R R R R BLAST tstzmultirange
R R R R R R R BLAST tstzmultirange[]
R R R R R R R BLAST tstzrange
R R R R R R R BLAST tstzrange[]
A A A A A A A BLAST tsvector
R A A A A A A BLAST tsvector[]
A A A A A A A BLAST txid_snapshot
R A A A A A A BLAST txid_snapshot[]
R A R A R R R BLAST ub_comp
R A R A R R R BLAST ub_comp[]
A A R A A R A BLAST ub_dfloat8
R A R A A R A BLAST ub_dfloat8[]
A A A A A A A BLAST ub_dint
R A A A A A A BLAST ub_dint[]
A A A A A A A BLAST ub_dtext
R A A A A R R BLAST ub_dtext[]
R R R R R R R BLAST ub_enum
R R R R R R R BLAST ub_enum[]
A A R A R R R BLAST ub_enumc
R A R A R R R BLAST ub_enumc[]
R A R A R R R BLAST ub_multirange
R A R A R R R BLAST ub_multirange[]
R A R A R R R BLAST ub_multirangec
R A R A R R R BLAST ub_multirangec[]
R A R A R R R BLAST ub_nummultirange
R A R A R R R BLAST ub_nummultirange[]
R A R A R R R BLAST ub_numrange
R A R A R R R BLAST ub_numrange[]
R A R A R R R BLAST ub_range
R A R A R R R BLAST ub_range[]
A A R A R R R BLAST ub_rangec
R A R A R R R BLAST ub_rangec[]
A A A A A A A BLAST ub_text
R A A A A R R BLAST ub_text[]
A A A A A A A BLAST ub_textc
R A A A A R R BLAST ub_textc[]
A A A A A A A BLAST uuid
R A A A A A A BLAST uuid[]
A A A A A A A BLAST xid
A A A A A A A BLAST xid8
R A A A A A A BLAST xid8[]
R A A A A A A BLAST xid[]
A A A A A A A BLAST xml
R A A A A A A BLAST xml[]
E A A A A A A BLAST oid8
E A A A A A A BLAST oid8[]
E A R A R R R BLAST pg_dsm_registry_allocations[]
E A R A R R R BLAST pg_publication_sequences[]
E A R A R R R BLAST pg_stat_autovacuum_scores[]
E A R E E R E BLAST pg_stat_kind_info[]
E R R R R R R BLAST pg_stat_lock[]
E A R E E R E BLAST pg_stat_progress_data_checksums
E A R A R R R BLAST pg_stat_progress_repack[]
E R R R R R R BLAST pg_stat_recovery[]
E R R R R R R BLAST regdatabase
E R R R R R R BLAST regdatabase[]
== repro-array.sql
R A A A A R R L12 CREATE INDEX t_i ON t ((json_arra
== contrib.sql
R A A A A R R TRY contrib citext array
A A A A A A A TRY contrib citext direct
A A A A A A A TRY contrib citext domain
R A A A A R R TRY contrib hstore array
A A A A A A A TRY contrib hstore direct
A A A A A A A TRY contrib hstore domain
R A A A A R R TRY contrib ltree array
A A A A A A A TRY contrib ltree direct
A A A A A A A TRY contrib ltree domain
R A A A A R R TRY contrib lquery array
A A A A A A A TRY contrib lquery direct
A A A A A A A TRY contrib lquery domain
R A A A A R R TRY contrib ltxtquery array
A A A A A A A TRY contrib ltxtquery direct
A A A A A A A TRY contrib ltxtquery domain
R A A A A R R TRY contrib cube array
A A A A A A A TRY contrib cube direct
A A A A A A A TRY contrib cube domain
R A A A A R R TRY contrib seg array
A A A A A A A TRY contrib seg direct
A A A A A A A TRY contrib seg domain
R A A A A R R TRY contrib ean13 array
A A A A A A A TRY contrib ean13 direct
A A A A A A A TRY contrib ean13 domain
R A A A A R R TRY contrib isbn13 array
A A A A A A A TRY contrib isbn13 direct
A A A A A A A TRY contrib isbn13 domain
R A A A A R R TRY contrib isbn array
A A A A A A A TRY contrib isbn direct
A A A A A A A TRY contrib isbn domain
R A A A A R R TRY contrib issn array
A A A A A A A TRY contrib issn direct
A A A A A A A TRY contrib issn domain
R A A A A R R TRY contrib issn13 array
A A A A A A A TRY contrib issn13 direct
A A A A A A A TRY contrib issn13 domain
R A A A A R R TRY contrib ismn13 array
A A A A A A A TRY contrib ismn13 direct
A A A A A A A TRY contrib ismn13 domain
R A A A A R R TRY contrib ismn array
A A A A A A A TRY contrib ismn direct
A A A A A A A TRY contrib ismn domain
R A A A A R R TRY contrib upc array
A A A A A A A TRY contrib upc direct
A A A A A A A TRY contrib upc domain
- 0/1! 0/1! 0/1! 0/1! - - ORACLE contrib citext array + CREATE
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib citext direct + CREATE
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib citext domain + CREATE
- 0/1! 0/1! 0/1! 0/1! - - ORACLE contrib hstore array + ALTER E
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib hstore direct + ALTER
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib hstore domain + ALTER
- 0/1! 0/1! 0/1! 0/1! - - ORACLE contrib ltree array + CREATE C
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib ltree direct + CREATE
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib ltree domain + CREATE
- 0/1! 0/1! 0/1! 0/1! - - ORACLE contrib lquery array + CREATE
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib lquery direct + CREATE
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib lquery domain + CREATE
- 0/1! 0/1! 0/1! 0/1! - - ORACLE contrib ltxtquery array + CREA
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib ltxtquery direct + CRE
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib ltxtquery domain + CRE
- 0/1! 0/1! 0/1! 0/1! - - ORACLE contrib cube array + CREATE CA
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib cube direct + CREATE C
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib cube domain + CREATE C
- 0/1! 0/1! 0/1! 0/1! - - ORACLE contrib seg array + CREATE CAS
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib seg direct + CREATE CA
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib seg domain + CREATE CA
- 0/1! 0/1! 0/1! 0/1! - - ORACLE contrib ean13 array + CREATE C
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib ean13 direct + CREATE
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib ean13 domain + CREATE
- 0/1! 0/1! 0/1! 0/1! - - ORACLE contrib isbn13 array + CREATE
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib isbn13 direct + CREATE
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib isbn13 domain + CREATE
- 0/1! 0/1! 0/1! 0/1! - - ORACLE contrib isbn array + CREATE CA
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib isbn direct + CREATE C
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib isbn domain + CREATE C
- 0/1! 0/1! 0/1! 0/1! - - ORACLE contrib issn array + CREATE CA
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib issn direct + CREATE C
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib issn domain + CREATE C
- 0/1! 0/1! 0/1! 0/1! - - ORACLE contrib issn13 array + CREATE
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib issn13 direct + CREATE
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib issn13 domain + CREATE
- 0/1! 0/1! 0/1! 0/1! - - ORACLE contrib ismn13 array + CREATE
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib ismn13 direct + CREATE
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib ismn13 domain + CREATE
- 0/1! 0/1! 0/1! 0/1! - - ORACLE contrib ismn array + CREATE CA
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib ismn direct + CREATE C
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib ismn domain + CREATE C
- 0/1! 0/1! 0/1! 0/1! - - ORACLE contrib upc array + CREATE CAS
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib upc direct + CREATE CA
0/1! 0/1! 0/1! 0/1! 0/1! 0/1! 0/1! ORACLE contrib upc domain + CREATE CA
== repro.sql
R A R A R R R L7 CREATE INDEX t_i ON t ((json_array
== t0002.sql
== sweep.sql, stored generated column (G1) and partition key (K1)
r18: (CREATE rejected, nothing to show)
mbase:
G1 stored gencol: stored=[{"x": 1}] recomputed=[{"z": 1}] DIVERGE
K1 partkey pruning=on count=0
K1 partkey pruning=off count=1
K1 row sits in tk_1, partition constraint now VIOLATED
m0001: (CREATE rejected, nothing to show)
r19base:
G1 stored gencol: stored=[{"x": 1}] recomputed=[{"z": 1}] DIVERGE
K1 partkey pruning=on count=0
K1 partkey pruning=off count=1
K1 row sits in tk_1, partition constraint now VIOLATED
r190001: (CREATE rejected, nothing to show)
mv5: (CREATE rejected, nothing to show)
r19v5: (CREATE rejected, nothing to show)
