json/jsonb cleanup + FmgrInfo caching
Hi,
While working on my "sandboxing untrusted code" project, I found
myself investigating how the JSON and JSONB code calls type output and
cast functions. I feel like this needs some cleanup in order to avoid
blocking that project, and it turns out that there are also
significant opportunities to improve performance, so here are some
patches. One caveat: one of these patches causes a small backward
compatibility break, because the current behavior is wrong. See below
for full details.
First, a quick performance demonstration:
CREATE TABLE hstores AS SELECT hstore('k', g::text) x FROM
generate_series(1,4000000) g;
SELECT any_value(json_build_object('a', x)) FROM hstores;
Unpatched, median-of-three runs was 398.571 ms. Patched, 193.714 ms.
That's a 2.05x speedup, which is the highest I observed in my test
queries. What I found is that this technique shows the largest gains
with user-defined types like hstore, smaller gains with built-in types
like int, and the smallest gains of all with container types such as a
user-defined record type. Generally, jsonb benefited more than json.
The aggregates - json_agg and jsonb_agg - showed very little benefit
or even small regressions, but I'm pretty confident that the
regressions are simply noise that goes away with a sufficiently large
number of sufficiently-careful test runs. Everything else gets faster,
often by 50%+. Leaving aside the aggregates which are expected to show
little or no benefit, here's one of the less-sympathetic cases:
CREATE TABLE ints AS SELECT x FROM generate_series(1,4000000) x;
SELECT any_value(to_json(x)) FROM ints;
The speedup is smaller here because it's json rather than jsonb and
because it's int rather than hstore, but it's still 117.461 ms
unpatched vs. 89.895 patched, a 30% speedup.
OK, now let's go through the patches:
0001 refactors the json_categorize_type() function to initialize an
FmgrInfo instead of returning a base function OID. All of the built-in
JSON aggregates are updated to cache this FmgrInfo across calls, but
it really doesn't save much. In the process of working on this
refactoring it came to light that the current behavior of
json_check_mutability() is incorrect: it erroneously treats record,
anyarray, and anycompatiblearray as immutable when in fact they should
be treated as stable. This refactoring preserves that incorrect
behavior.
0002 fixes the bug discovered during the development of 0001 by
removing the special case. AFAICT, the anyarray and anycompatiblearray
cases are unreachable, but the record case is reachable, and the
included test case shows how this could hypothetically matter. It
seems unlikely we'll inconvenience any significant number of users by
changing this, but in theory somebody's upgrade could fail.
0003 moves some code around to avoid problems with circular header
dependencies, creating new files jsontypes.c/h.
0004 refactors the SQL-level JSON constructors -- JSON_OBJECT,
JSON_ARRAY, and JSON_SCALAR -- to make use of the new type caching
infrastructure.
0005 refactors the SQL-callable functions similarly. This means
to_json(b), json(b)_build_object, and json(b)_build_array.
Thanks,
--
Robert Haas
EDB: http://www.enterprisedb.com
Attachments:
t248717_1v1-0003-Create-jsontypes.c-h-and-use-that-to-tidy-up-a-fe.patchapplication/octet-stream; name=v1-0003-Create-jsontypes.c-h-and-use-that-to-tidy-up-a-fe.patchDownload+293-247
v1-0005-Cache-JSON-type-information-in-various-SQL-callab.patchapplication/octet-stream; name=v1-0005-Cache-JSON-type-information-in-various-SQL-callab.patchDownload+207-53
v1-0002-Don-t-treat-record-json-b-conversions-as-immutabl.patchapplication/octet-stream; name=v1-0002-Don-t-treat-record-json-b-conversions-as-immutabl.patchDownload+1-7
v1-0004-Allow-JSON_OBJECT-JSON_ARRAY-JSON_SCALAR-to-cache.patchapplication/octet-stream; name=v1-0004-Allow-JSON_OBJECT-JSON_ARRAY-JSON_SCALAR-to-cache.patchDownload+129-128
v1-0001-Refactor-json_categorize_type-to-populate-an-Fmgr.patchapplication/octet-stream; name=v1-0001-Refactor-json_categorize_type-to-populate-an-Fmgr.patchDownload+235-194
On Thu, Jul 2, 2026 at 12:25 PM Robert Haas <robertmhaas@gmail.com> wrote:
Hi,
While working on my "sandboxing untrusted code" project, I found
myself investigating how the JSON and JSONB code calls type output and
cast functions. I feel like this needs some cleanup in order to avoid
blocking that project, and it turns out that there are also
significant opportunities to improve performance, so here are some
patches. One caveat: one of these patches causes a small backward
compatibility break, because the current behavior is wrong. See below
for full details.First, a quick performance demonstration:
CREATE TABLE hstores AS SELECT hstore('k', g::text) x FROM
generate_series(1,4000000) g;
SELECT any_value(json_build_object('a', x)) FROM hstores;Unpatched, median-of-three runs was 398.571 ms. Patched, 193.714 ms.
That's a 2.05x speedup, which is the highest I observed in my test
queries. What I found is that this technique shows the largest gains
with user-defined types like hstore, smaller gains with built-in types
like int, and the smallest gains of all with container types such as a
user-defined record type. Generally, jsonb benefited more than json.
The aggregates - json_agg and jsonb_agg - showed very little benefit
or even small regressions, but I'm pretty confident that the
regressions are simply noise that goes away with a sufficiently large
number of sufficiently-careful test runs. Everything else gets faster,
often by 50%+. Leaving aside the aggregates which are expected to show
little or no benefit, here's one of the less-sympathetic cases:CREATE TABLE ints AS SELECT x FROM generate_series(1,4000000) x;
SELECT any_value(to_json(x)) FROM ints;The speedup is smaller here because it's json rather than jsonb and
because it's int rather than hstore, but it's still 117.461 ms
unpatched vs. 89.895 patched, a 30% speedup.OK, now let's go through the patches:
0001 refactors the json_categorize_type() function to initialize an
FmgrInfo instead of returning a base function OID. All of the built-in
JSON aggregates are updated to cache this FmgrInfo across calls, but
it really doesn't save much. In the process of working on this
refactoring it came to light that the current behavior of
json_check_mutability() is incorrect: it erroneously treats record,
anyarray, and anycompatiblearray as immutable when in fact they should
be treated as stable. This refactoring preserves that incorrect
behavior.0002 fixes the bug discovered during the development of 0001 by
removing the special case. AFAICT, the anyarray and anycompatiblearray
cases are unreachable, but the record case is reachable, and the
included test case shows how this could hypothetically matter. It
seems unlikely we'll inconvenience any significant number of users by
changing this, but in theory somebody's upgrade could fail.0003 moves some code around to avoid problems with circular header
dependencies, creating new files jsontypes.c/h.0004 refactors the SQL-level JSON constructors -- JSON_OBJECT,
JSON_ARRAY, and JSON_SCALAR -- to make use of the new type caching
infrastructure.0005 refactors the SQL-callable functions similarly. This means
to_json(b), json(b)_build_object, and json(b)_build_array.
Looks pretty good.
The old `add_json()`/`add_jsonb()` helpers (removed in 0004/0005) checked
`val_type == InvalidOid` and raised a clean
`ERRCODE_INVALID_PARAMETER_VALUE` / "could not determine input data type"
error. Now I think we'd get something like "cache lookup failed for type
0", which is rather more opaque. Not sure if that matters.
For VARIADIC arrays,
for (int i = 0; i < nargs; ++i)
fmgr_info_copy(&(*outflinfos)[i], &jcache->flinfos[0],
CurrentMemoryContext);
is going to copy the same flinfo for every array element for every row, The
comment says that it's done that way to avoid complicating the code, and
that seems reasonable. I don't know how often these functions are used with
explicit VARIADIC array arguments, so it might be a niche case.
cheers
andrew
On Wed, Jul 8, 2026 at 11:16 AM Andrew Dunstan <andrew@dunslane.net> wrote:
Looks pretty good.
Thanks for looking.
The old `add_json()`/`add_jsonb()` helpers (removed in 0004/0005) checked `val_type == InvalidOid` and raised a clean `ERRCODE_INVALID_PARAMETER_VALUE` / "could not determine input data type" error. Now I think we'd get something like "cache lookup failed for type 0", which is rather more opaque. Not sure if that matters.
I definitely don't want to show "cache lookup failed" errors, but I
don't think that's reachable. If I'm wrong and it is, we should add a
guard. Do you have a test case, by any chance?
For VARIADIC arrays,
for (int i = 0; i < nargs; ++i)
fmgr_info_copy(&(*outflinfos)[i], &jcache->flinfos[0], CurrentMemoryContext);is going to copy the same flinfo for every array element for every row, The comment says that it's done that way to avoid complicating the code, and that seems reasonable. I don't know how often these functions are used with explicit VARIADIC array arguments, so it might be a niche case.
Right. I think it is a niche case, but I also don't think this is
really costing us anything. As far as I can tell from my benchmarking
to date, fmgr_info() is kind of expensive, but fmgr_info_copy() is
pretty cheap. So, we could arrange to share a single flinfo for every
argument of the VARIADIC list, but the savings are pretty limited
because we wouldn't save fmgr_info() calls, only fmgr_info_copy()
calls. And on the downside json{,b}_build_{array,object}_worker would
all need to support two modes, one where there is an fcinfo per Datum
and a second where there is one fcinfo for all Datums. There's a
code-complexity argument against that, but it would also have some
cost in CPU cycles, because every call would have to branch into one
path or the other. We'd be slowing down both variadic and non-variadic
calls slightly by adding those branches, in the hopes that saving
fmgr_info_copy() calls in the non-variadic case would be valuable
enough to make it worthwhile. Somebody can certainly try that, but my
guess is that there won't be any performance benefit worth caring
about and the code will be uglier.
--
Robert Haas
EDB: http://www.enterprisedb.com
Robert Haas <robertmhaas@gmail.com> writes:
Right. I think it is a niche case, but I also don't think this is
really costing us anything. As far as I can tell from my benchmarking
to date, fmgr_info() is kind of expensive, but fmgr_info_copy() is
pretty cheap. So, we could arrange to share a single flinfo for every
argument of the VARIADIC list, but the savings are pretty limited
because we wouldn't save fmgr_info() calls, only fmgr_info_copy()
calls.
The hole in that argument is that fmgr_info_copy() does
dstinfo->fn_extra = NULL;
so that if the function-to-be-called caches anything in fn_extra,
it will have to populate that cache for each argument. That could be
quite expensive. It might still not justify complicating the setup
code, but I don't think it's as clear-cut as you suggest. Perhaps
some benchmarking is in order.
regards, tom lane
On Thu, Jul 9, 2026 at 11:22 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
The hole in that argument is that fmgr_info_copy() does
dstinfo->fn_extra = NULL;
so that if the function-to-be-called caches anything in fn_extra,
it will have to populate that cache for each argument. That could be
quite expensive.
Agreed, in theory.
It might still not justify complicating the setup
code, but I don't think it's as clear-cut as you suggest. Perhaps
some benchmarking is in order.
The thing is, the only thing we are calling here are cast functions
and type output functions. The only such functions I turned up that
use fn_extra are record_out(), array_out(), range_out(), and
multirange_out(), but I believe that the former two are irrelevant
because we do JSON-specific things for arrays and records. So any
possible gain would be limited to a VARIADIC call using a range or
multirange type. Something like this:
CREATE TABLE table_of_range_arrays (some_column int4range[]);
INSERT INTO table_of_range_arrays SELECT array[int4range(g, g+10),
int4range(g+20, g+30)] FROM generate_series(1, 20000000) g;
And then:
SELECT any_value(json_build_array(VARIADIC some_column)) FROM
table_of_range_arrays;
In my testing, this takes 3291 ms on
d007800f02a7b9fe3ca984a1b870405657d990ed and 2687 ms with the patches
applied to that commit, so it's close to 20% faster already. Somebody
might be able to squeeze out a little more by sharing the same
FmgrInfo across all argument positions, but it would be at the cost
not only of code complexity but also of cases that don't involve
VARIADIC or that use anything other than a range or multirange type. I
feel like if somebody thinks that's worthwhile they can propose it as
a followup patch, but I'm inclined to think that it isn't. It's a very
marginal case and I think the effort would be better spent on
optimizing other cases.
For example, composite_to_json() doesn't currently do any caching of
json_categorize_type results because it has nowhere to store the
cache. One of the callers is row_to_json(), which seems like it might
be able to use fn_extra ... but are we guaranteed that all inputs will
have the same rowtype? I'm not sure how that works. It can also get
called recursively from datum_to_json_internal() or
array_dim_to_json() while recursing down through substructure, but I'm
even less certain whether the types are guaranteed to be the same from
row to row in those cases, and there's also the question of where you
would actually store the cache, since they don't have a fn_extra that
they own. If type changes are not possible at these levels or are
cheap to detect, it might be possible to store a cache entry in the
top-level fn_extra that leaves places for substructure to store
fn_extra-like pointers so that we can do caching all the way down
through the structure. I'm not sure how feasible all that is, though.
One related thing that I noticed while researching this is that, as I
currently have it, json_extract_variadic_args() does this:
*categories = palloc_array(JsonTypeCategory, nargs);
*outflinfos = palloc0_array(FmgrInfo, nargs);
if (OidIsValid(jcache->flinfos[0].fn_oid))
{
for (int i = 0; i < nargs; ++i)
{
(*categories)[i] = jcache->categories[0];
fmgr_info_copy(&(*outflinfos)[i], &jcache->flinfos[0],
CurrentMemoryContext);
}
}
else
{
for (int i = 0; i < nargs; ++i)
(*categories)[i] = jcache->categories[0];
}
It could be argued that this is a waste: if we take the OidIsValid
branch, we'll overwrite every byte of outflinfos, so zeroing it was a
waste. If we take the !OidIsValid, zeroing it is also a waste unless
there's a bug, but I zeroed it because I figured if there was a bug,
using random memory as a FmgrInfo was going to be unpleasant for
debuggability. But there's an argument that even allocating the array
is a waste in this case: when datum_to_json(b)_internal is eventually
called with (*categories)[i] and (*outflinfos)[i], it should never
access the FmgrInfo if the category is such that the FmgrInfo is
invalid. So when !OidIsValid, I suppose we could just set *outflinfos
to NULL or some random pointer address and in theory that will never
get dereferenced so I guess it should be fine, and the benefit is we
save a palloc0_array per call. And there is a small but seemingly
measurable performance benefit from doing that. But I'm reluctant to
go that way unless we can find a less hacky way to avoid the palloc,
because I feel like static analyzers (and humans) will find it
confusing.
--
Robert Haas
EDB: http://www.enterprisedb.com
On Fri, Jul 3, 2026 at 12:25 AM Robert Haas <robertmhaas@gmail.com> wrote:
0002 fixes the bug discovered during the development of 0001 by
removing the special case. AFAICT, the anyarray and anycompatiblearray
cases are unreachable, but the record case is reachable, and the
included test case shows how this could hypothetically matter. It
seems unlikely we'll inconvenience any significant number of users by
changing this, but in theory somebody's upgrade could fail.
Hi.
Shihao Zhong [1]/messages/by-id/CAGRkXqSFdjqZP_kwRz7dv6LHX-CSrkDnynNXgntRsCEnQTKxmg@mail.gmail.com direct me to here.
json_check_mutability was changed in PG19, and in 0001 you removed the
call to json_categorize_type from json_check_mutability. We can use that
portion for PG19, and the json_categorize_type refactoring can go to PG20.
[1]: /messages/by-id/CAGRkXqSFdjqZP_kwRz7dv6LHX-CSrkDnynNXgntRsCEnQTKxmg@mail.gmail.com
Attachments:
v2-0001-Don-t-treat-record-json-b-conversions-as-immutable.txttext/plain; charset=US-ASCII; name=v2-0001-Don-t-treat-record-json-b-conversions-as-immutable.txtDownload+103-30
Hi Jian,
v2 fixes the ROW() cases on 19 and master, and all tests pass.
Named composite types still have the problem. ALTER TYPE can add a
timestamptz after the index is built.
CREATE TYPE c AS (x int);
CREATE TABLE t (v c);
CREATE INDEX ON t ((json_array(v returning jsonb)));
ALTER TYPE c ADD ATTRIBUTE ts timestamptz;
SET TimeZone = 'UTC';
INSERT INTO t VALUES (row(1, '2026-01-01 00:00+00'));
SET TimeZone = 'Asia/Shanghai';
SELECT count(*) FROM t
WHERE json_array(v returning jsonb) =
json_array(row(1, '2026-01-01 00:00+00')::c returning jsonb);
The index scan returns 0 and the seq scan returns 1. 18 rejects the
CREATE INDEX.
Thanks,
Shihao
shihao zhong <zhong950419@gmail.com> 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
entries are stale. 18 rejects the CREATE INDEX. RENAME ATTRIBUTE and DROP
ATTRIBUTE do the same, and so does ALTER TABLE ... RENAME COLUMN / ADD COLUMN
on a table's rowtype.
The reason is that the jsonb output carries the attribute names and their
number. The immutability verdict is computed once, at CREATE INDEX, from the
type's definition at that moment, and the catalog does not block a later
ADD / RENAME / DROP ATTRIBUTE (only ALTER ATTRIBUTE ... TYPE is rejected). So
the timestamptz case is one instance of a more general problem, and a recheck
that only looks for a mutable attribute would leave the structural changes
open.
The same expression misbehaves in the other places it can be used, all on
19rc1 and master and all rejected by 18:
- a unique index takes a second row with the same key, and a later REINDEX
fails with "could not create unique index";
- a partial index returns a row that no longer matches its predicate;
- a STORED generated column keeps [{"x": 1}] while a recompute gives
[{"z": 1}];
- a partition key stops pruning to the right partition, and the row ends up
violating its partition constraint.
It also reaches through a nested composite, a domain over a composite and an
array of a composite.
The full matrix, the minimal repro and the per-version output on
18.6 / 19rc1 / master are in the attached evidence.txt.
regards,
Manu
Attachments:
evidence.txttext/plainDownload
On Sun, Oct 18, 2026 at 12:32 PM Manu <manuelreyesbravo@gmail.com> wrote:
shihao zhong <zhong950419@gmail.com> 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 /messages/by-id/19644-d6db3fb5e5ad92f4@postgresql.org
Attachments:
v3-0001-Don-t-treat-record-json-b-conversions-as-immutable.txttext/plain; charset=US-ASCII; name=v3-0001-Don-t-treat-record-json-b-conversions-as-immutable.txtDownload+155-56
Hi Jian,
On Thu, Oct 22, 2026 at 9:12 PM jian he
<jian.universality@gmail.com> wrote:
The attached patch should resolve these issues.
It does. v3 applies cleanly to master and REL_19_STABLE, and make
check passes. Every case from my earlier matrix is now rejected at
CREATE INDEX, as in 18: ADD / RENAME / DROP ATTRIBUTE, the table
rowtype, nested composites, domains over composites, arrays of
composites, JSON_OBJECT, RETURNING json, and the unique, partial,
generated column and partition key forms. Anonymous ROW(), record[]
and JSON_SCALAR are rejected too.
One case still gets through, in the range branch. A range is printed
by range_out, which calls the subtype's output function and never a
cast to json. But when the range has no cast of its own,
json_check_mutability() recurses into the subtype and judges the
subtype's cast to json instead. So an immutable cast on an enum makes
a range over that enum look immutable, while what actually prints it
is enum_out:
CREATE TYPE e AS ENUM ('a', 'b');
CREATE FUNCTION e_json(e) RETURNS json IMMUTABLE LANGUAGE sql
AS $$ SELECT '"x"'::json $$;
CREATE CAST (e AS json) WITH FUNCTION e_json(e);
CREATE TYPE er AS RANGE (subtype = e);
CREATE TABLE t (v er);
INSERT INTO t VALUES (er('a', 'b'));
CREATE INDEX t_i ON t ((json_array(v returning jsonb)));
ALTER TYPE e RENAME VALUE 'a' TO 'z';
SELECT count(*) FROM t
WHERE json_array(v returning jsonb) =
json_array(er('z', 'b') returning jsonb);
The index scan returns 0 and a seq scan returns 1; REINDEX restores 1.
Without the cast on the enum the CREATE INDEX is rejected, and 18
rejects it either way. The multirange behaves the same. Master has
this without v3 as well.
The attached diff, on top of v3, stops looking up casts once the
recursion is below a range or multirange. With it both cases are
rejected, nothing else in my matrix changes (int4range is still
accepted), and make check passes. It's named .txt so it doesn't
disturb the CF bot; feel free to fold it into v4 if you agree.
regards,
Manu
Attachments:
nocfbot-fix-range-on-v3.diff.txttext/plainDownload+22-6
Hi Jian, Manu,
v3 with Manu's diff fixes my case, and all tests pass.
float8out is STABLE on master now, but v3 still lists FLOAT4OID
and FLOAT8OID as immutable. So this is accepted:
CREATE INDEX ON tf ((json_array(f returning jsonb))); -- f float8
and gives wrong results after SET extra_float_digits = 0. Dropping
those two case labels fixes it on master and changes nothing on 19.
Thanks,
Shihao
On Thu, Oct 22, 2026 at 11:51 AM Manu <manuelreyesbravo@gmail.com> wrote:
Show quoted text
Hi Jian,
On Thu, Oct 22, 2026 at 9:12 PM jian he
<jian.universality@gmail.com> wrote:The attached patch should resolve these issues.
It does. v3 applies cleanly to master and REL_19_STABLE, and make
check passes. Every case from my earlier matrix is now rejected at
CREATE INDEX, as in 18: ADD / RENAME / DROP ATTRIBUTE, the table
rowtype, nested composites, domains over composites, arrays of
composites, JSON_OBJECT, RETURNING json, and the unique, partial,
generated column and partition key forms. Anonymous ROW(), record[]
and JSON_SCALAR are rejected too.One case still gets through, in the range branch. A range is printed
by range_out, which calls the subtype's output function and never a
cast to json. But when the range has no cast of its own,
json_check_mutability() recurses into the subtype and judges the
subtype's cast to json instead. So an immutable cast on an enum makes
a range over that enum look immutable, while what actually prints it
is enum_out:CREATE TYPE e AS ENUM ('a', 'b');
CREATE FUNCTION e_json(e) RETURNS json IMMUTABLE LANGUAGE sql
AS $$ SELECT '"x"'::json $$;
CREATE CAST (e AS json) WITH FUNCTION e_json(e);
CREATE TYPE er AS RANGE (subtype = e);
CREATE TABLE t (v er);
INSERT INTO t VALUES (er('a', 'b'));
CREATE INDEX t_i ON t ((json_array(v returning jsonb)));
ALTER TYPE e RENAME VALUE 'a' TO 'z';
SELECT count(*) FROM t
WHERE json_array(v returning jsonb) =
json_array(er('z', 'b') returning jsonb);The index scan returns 0 and a seq scan returns 1; REINDEX restores 1.
Without the cast on the enum the CREATE INDEX is rejected, and 18
rejects it either way. The multirange behaves the same. Master has
this without v3 as well.The attached diff, on top of v3, stops looking up casts once the
recursion is below a range or multirange. With it both cases are
rejected, nothing else in my matrix changes (int4range is still
accepted), and make check passes. It's named .txt so it doesn't
disturb the CF bot; feel free to fold it into v4 if you agree.regards,
Manu
On Fri, Oct 23, 2026 at 9:36 AM shihao zhong <zhong950419@gmail.com> wrote:
float8out is STABLE on master now, but v3 still lists FLOAT4OID
and FLOAT8OID as immutable. So this is accepted:CREATE INDEX ON tf ((json_array(f returning jsonb))); -- f float8
and gives wrong results after SET extra_float_digits = 0. Dropping
those two case labels fixes it on master and changes nothing on 19.
Hi.
This issue is fixed.
Another old issue: indexes don't have dependency with CAST. Dropping a
user-defined cast will change the index definition.
Hopefully, the attached v4 fix should be bullet-proof.
Enums and composite types should be marked as mutable when casting to json or
jsonb. Because ALTER TYPE ... RENAME VALUE, ADD/DROP/RENAME ATTRIBUTE (and ALTER
TABLE on a table's row type) does not rebuilding dependent indexes.
user-defined range and multirange types as mutable too. Their conversion uses a
cast to json function if exists, looked up at runtime, so creating or dropping
such a cast after CREATE INDEX changes the output.
Built-in ranges are unaffected, see json_categorize_type DEFAULT branch code.
Below is a code demo for a user-defined range type.
-------------------------
DROP TABLE IF EXISTS tr;
DROP CAST IF EXISTS (myr AS json);
DROP FUNCTION IF EXISTS myr_json(myr);
DROP TYPE IF EXISTS myr;
CREATE TYPE myr AS RANGE (subtype = int);
CREATE TABLE tr (r myr) WITH (autovacuum_enabled = off);
INSERT INTO tr VALUES ('[1,3)');
-- no cast exists yet, so the index stores the range_out text
CREATE INDEX tr_idx ON tr ((json_array(r returning jsonb)));
CREATE FUNCTION myr_json(myr) RETURNS json IMMUTABLE LANGUAGE sql AS
$$ SELECT '{"1": "2"}'::json $$;
CREATE CAST (myr AS json) WITH FUNCTION myr_json(myr);
SET enable_seqscan = off;
SELECT count(*) FROM tr WHERE json_array(r returning jsonb) = '[{"1": "2"}]';
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT count(*) FROM tr WHERE json_array(r returning jsonb) = '[{"1": "2"}]';
Attachments:
v4-0001-Don-t-treat-record-json-b-conversions-as-immutable.txttext/plain; charset=US-ASCII; name=v4-0001-Don-t-treat-record-json-b-conversions-as-immutable.txtDownload+184-64
Hi Jian,
On Fri, Oct 23, 2026 at 12:52 PM jian he
<jian.universality@gmail.com> wrote:
user-defined range and multirange types as mutable too. Their conversion uses a
cast to json function if exists, looked up at runtime, so creating or dropping
such a cast after CREATE INDEX changes the output.
I tested v4 on master and REL_19_STABLE. It applies cleanly, builds
without warnings, and make check passes on both. My range-over-enum
case is rejected now, and so are your myr demo and shihao's float8 and
float4 case on master. int4range and int4multirange are still
accepted. v4 covers the range case, so my diff from upthread is no
longer needed.
Compared with 18.6, the only indexes in my sweep that v4 rejects and
18 accepts are json_array() over an enum or a user range that has an
IMMUTABLE cast to json, plus float4/float8 on master, where f::text is
already rejected without v4.
One case still gets through, and it is new in 19: an array of a
user-defined base type. 18 rejects every array. 19 recurses into the
element, and for a user-defined element the check still uses the cast
it finds at CREATE INDEX time, so a cast created afterwards changes
the indexed value:
CREATE TYPE mytext;
CREATE FUNCTION mytext_in(cstring) RETURNS mytext
LANGUAGE internal IMMUTABLE STRICT AS 'textin';
CREATE FUNCTION mytext_out(mytext) RETURNS cstring
LANGUAGE internal IMMUTABLE STRICT AS 'textout';
CREATE TYPE mytext (INPUT = mytext_in, OUTPUT = mytext_out,
LIKE = text);
CREATE TABLE t (v mytext[]);
INSERT INTO t VALUES (ARRAY['a']::mytext[]);
CREATE INDEX t_i ON t ((json_array(v returning jsonb)));
CREATE FUNCTION mytext_json(mytext) RETURNS json IMMUTABLE
LANGUAGE sql AS $$ SELECT '"x"'::json $$;
CREATE CAST (mytext AS json) WITH FUNCTION mytext_json(mytext);
SELECT count(*) FROM t
WHERE json_array(v returning jsonb) =
json_array(ARRAY['a']::mytext[] returning jsonb);
The index scan returns 0 and a seq scan returns 1 (REINDEX restores
1) on both branches, with and without v4; 18.6 rejects the index.
Without the array, or through a domain, this is older: 17.11 and 18.6
accept those indexes and return the same wrong count after CREATE CAST
or DROP CAST. Measured with citext, hstore, ltree, cube, isn and seg,
it is the same for contrib types, so marking every user-defined base
type as mutable would reject indexes that work today. That is the
missing dependency on the cast you mentioned; I have a prototype that
records it (DROP CAST then fails without CASCADE, though it can't help
with a cast created later) if it's useful to you.
The attached nocfbot diff treats an array as mutable when its element
is a user-defined base type or a domain over one, the same way user
ranges are treated now. It goes on top of v4 and applies to both
branches. With it the repro is rejected, as in 18; in my sweep only
such arrays change (for those contrib types too, all rejected by 18),
and check-world passes on both.
regards,
Manu
Attachments:
nocfbot-fix-array-on-v4.diff.txttext/plainDownload+11-2
On Fri, Oct 23, 2026 at 7:28 PM Manu <manuelreyesbravo@gmail.com> wrote:
I tested v4 on master and REL_19_STABLE. It applies cleanly, builds
without warnings, and make check passes on both. My range-over-enum
case is rejected now, and so are your myr demo and shihao's float8 and
float4 case on master. int4range and int4multirange are still
accepted. v4 covers the range case, so my diff from upthread is no
longer needed.
Hi,
I think basically what's being alleged here is that Andrew's commit
ecd9288624a1582a732cf86ac5a01475a1ce7815, patch by Jian He, is broken.
What I think should have happened is a new thread about that problem
specifically, or perhaps a continuation of the original thread for
that commit. Either way, that thread should have been added to the
open items list. Instead, because I reported a portion of the problem,
without realizing it was new in v19, this thread has gotten hijacked
to talk about the v19 regression vs. v18 and prior, rather than about
my patches. v4-0001 copies my commit message from v1-0002 even though
it's not the same patch at all, and nothing's been added to the open
items list, so I'm not sure that Andrew even knows that there's a
potential problem here in need of urgent correction.
Jian and Manu, please move this discussion to an appropriate new or
existing thread, fix the patch's commit message to clearly describe
the problem and the solution, and write an email to support the patch
submission that clearly explains everything relevant, including the
history. Also, add it to the open items list ASAP.
Andrew, please take note.
Once whatever needs fixing here is fixed, I'll rebase these patches.
Thanks,
--
Robert Haas
Databricks
Hi Robert,
On Tue, Nov 3, 2026 at 9:58 AM Robert Haas <robertmhaas@gmail.com>
wrote:
Jian and Manu, please move this discussion to an appropriate new or
existing thread, fix the patch's commit message to clearly describe
the problem and the solution, and write an email to support the patch
submission that clearly explains everything relevant, including the
history. Also, add it to the open items list ASAP.
You're right, and sorry for taking over your thread. The discussion
of ecd9288624a continues here:
/messages/by-id/179372513496.2500379.6217138376932454060@gmail.com
The patch has a commit message of its own now, and the issue goes on
the PG19 open items as soon as my wiki editor access comes through.
Nothing more on it in this thread.
regards,
Manu