PG19 regression in ecd9288624a: JSON_ARRAY() indexes that go stale

Started by Manu19 days ago9 messageshackers
Jump to latest
#1Manu
manuelreyesbravo@gmail.com

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:
/messages/by-id/CA+TgmoaiothgQrw9OtgsMzBUCnqJ2jdaGTbS6o3fkjCd+LfzWw@mail.gmail.com
- Oct 13, Jian proposes that fix alone for 19 (v2):
/messages/by-id/CACJufxF4yYQsTavPHkfyHRFvh1rO7xWH39u9pRJ9FGi5Wb_j9Q@mail.gmail.com
- Oct 18, shihao: a named composite plus ADD ATTRIBUTE still goes stale:
/messages/by-id/CAGRkXqTgwNKmZVK=3CpYZAdcatHEajOysyb072vwNuYUtX18eA@mail.gmail.com
- Oct 18, me: any structural change does, in every index form:
/messages/by-id/179229793051.2378320.14800418190474533566@gmail.com
- Oct 22, Jian's v3 makes every composite mutable:
/messages/by-id/CACJufxHQJ=5ee1FMhKqNjZ4TAX4q6dWsGiEEJJGgb1AhCF1cgA@mail.gmail.com
- Oct 22, me: a range over an enum with a cast still goes stale:
/messages/by-id/179268425559.902958.6506662009494214541@gmail.com
- Oct 23, shihao: v3 still treats float4/float8 as immutable on master:
/messages/by-id/CAGRkXqQz500QNGZrF36xu+VvoL9E3+FgyVvx8zW0f6jFnKM7iA@mail.gmail.com
- Oct 23, Jian's v4 adds enums and user-defined ranges:
/messages/by-id/CACJufxER-VJzaH33HYzoU-Z4ZOd_iJCX9+3r5TLhcEEN=NS=fQ@mail.gmail.com
- Oct 23, me: arrays of a user-defined base type still go stale:
/messages/by-id/179279809045.1942440.2176959044031147982@gmail.com
- Nov 3, Robert asks for this thread and the open item:
/messages/by-id/CA+TgmoYJMxeY2XoQ88qHgi2NCA2LbQd=aoVwcOAPLq3KZ22DKQ@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

Attachments:

t254541_1
v5-0001-Fix-immutability-check-of-SQL-JSON-constructors-f.patchtext/x-patchDownload+182-71
v5-0002-Treat-arrays-of-user-defined-base-types-as-mutabl.patchtext/x-patchDownload+60-3
repro.sql.txttext/plainDownload
matrix.txttext/plainDownload
#2Nikolay Samokhvalov
nik@postgres.ai
In reply to: Manu (#1)
Re: PG19 regression in ecd9288624a: JSON_ARRAY() indexes that go stale

On Tue, Nov 3, 2026 at 8:58 AM Manu <manuelreyesbravo@gmail.com> wrote:

- 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

True for scalars, but accepting arrays of them here is new in 19.
18 rejects every array here; 19 recurses into the element type, and on
REL_19_STABLE
float4out, float8out, byteaout and the geometric output functions are
still IMMUTABLE (e75fcc9867fd made them STABLE on master only [1]https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e75fcc9867fdfe295957ad5c9a98cbe2e1b4c5c3). So
19 with v5 accepts float4[], float8[], bytea[], point[] etc., and the
index goes stale:

create table t (f float8[]);
insert into t values ('{0.30000000000000004}');
create index t_i on t ((json_array(f returning jsonb)));
set extra_float_digits = 0;
set enable_seqscan = off;
select count(*) from t where json_array(f returning jsonb) = '[[0.3]]';

On REL_19_STABLE 4b1abd678223 with v5 the index scan returns 0, a seq
scan returns 1. 18.6 rejects the CREATE INDEX. bytea[] with
bytea_output = escape behaves the same.

AI found this while testing v5 and prepared the attached patch; I
haven't manually reviewed the code yet. It is for REL_19_STABLE only,
on top of v5: arrays of float4, float8, bytea and the geometric types,
or of domains over them, are mutable. Scalars keep the 18 behavior, so
existing schemas still restore. Master doesn't need it, there v5
already rejects all of these. The file name starts with nocfbot so
cfbot keeps testing v5.

Tested on REL_19_STABLE 4b1abd678223 + v5 + this patch, assertions
on: the regress suite passes; without the jsonfuncs.c hunk the new
sqljson test fails. On master b6178c942ea7 + v5, the same statements
are rejected already.

[1]: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e75fcc9867fdfe295957ad5c9a98cbe2e1b4c5c3
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e75fcc9867fdfe295957ad5c9a98cbe2e1b4c5c3

Nik

Attachments:

nocfbot-v5-REL_19-float-json-immutability.patchapplication/x-patch; name=nocfbot-v5-REL_19-float-json-immutability.patchDownload+82-4
#3Manu
manuelreyesbravo@gmail.com
In reply to: Nikolay Samokhvalov (#2)
Re: PG19 regression in ecd9288624a: JSON_ARRAY() indexes that go stale

Hi Nik,

On Tue, Nov 3, 2026 at 10:37 PM Nikolay Samokhvalov <nik@postgres.ai>
wrote:

True for scalars, but accepting arrays of them here is new in 19.

Right, I missed it: my line about floats covered scalars only, and I
never ran arrays with extra_float_digits or bytea_output changed.

Your repro on REL_19_STABLE a8e54f3dad3 with v5 returns 0 rows through
the index and 1 with a seq scan; 18.6 and master 73a28d2733a with v5
reject the CREATE INDEX. float4[], bytea[], point[], box[] and
circle[] behave the same, also as an array of a domain, as a 2-D
array and as a domain over the array type: 24 cases, all stale on 19
with v5, all rejected on 18.6, on master with v5 and on 19 with your
patch (attached: measurements.txt and the script, repro-arrays.sql.txt).

It is for REL_19_STABLE only, on top of v5: arrays of float4,
float8, bytea and the geometric types, or of domains over them, are
mutable.

I found no type missing from your list. The built-in types whose
output function is IMMUTABLE on REL_19_STABLE and STABLE on master are
your ten plus pg_mcv_list and pg_brin_minmax_multi_summary, which have
no array type. In the regression database I also hashed the JSON of
every built-in column under extra_float_digits, bytea_output,
DateStyle, IntervalStyle, TimeZone, lc_monetary and lc_numeric; the
only types whose output changed with an IMMUTABLE output function on
19 are the same ten (gucprobe.sql.txt, attached too).

The patch applies with git am on top of v5, builds with assertions
and no warnings, and passes make check and check-world; without the
jsonfuncs.c hunk the new test fails. Of the 340 types we try on 19 it
rejects eleven more than v5 (arrays of your ten and of a domain over
float8), none that 18.6 accepts, and no scalars.

One nit: in a REL_19-only patch, "On master the output functions of
these types are STABLE" might read better as "e75fcc9867f made them
STABLE in 20 but was not backpatched".

An option for Andrew: making every array mutable on 19, as in 18,
also closes this, but rejects 47 more types that v5 and your patch
accept, among them int4[] and text[], whose output no setting changed.

regards,
Manu

Attachments:

measurements.txttext/plainDownload
repro-arrays.sql.txttext/plainDownload
gucprobe.sql.txttext/plainDownload
#4Andres Freund
andres@anarazel.de
In reply to: Nikolay Samokhvalov (#2)
Re: PG19 regression in ecd9288624a: JSON_ARRAY() indexes that go stale

On 2026-11-03 22:37:52 -0800, Nikolay Samokhvalov wrote:

AI found this while testing v5 and prepared the attached patch; I
haven't manually reviewed the code yet.

Personally I think dropping unverified AI code onto the list has almost always
negative value, it just increases the already really high noise floor even
further. We all can ask LLMs to vomit out code. Putting it on the list
basically just means you're asking somebody else to do the actual work.

#5Andrey Borodin
amborodin@acm.org
In reply to: Andres Freund (#4)
Re: PG19 regression in ecd9288624a: JSON_ARRAY() indexes that go stale

On 4 Nov 2026, at 23:04, Andres Freund <andres@anarazel.de> wrote:

On 2026-11-03 22:37:52 -0800, Nikolay Samokhvalov wrote:

AI found this while testing v5 and prepared the attached patch; I
haven't manually reviewed the code yet.

Personally I think dropping unverified AI code onto the list has almost always
negative value, it just increases the already really high noise floor even
further. We all can ask LLMs to vomit out code. Putting it on the list
basically just means you're asking somebody else to do the actual work.

Do you think there was any human interaction in this thread before your message?

I like Nik's post transparency. There's no way to escape LLMs. They exist.

It would be great if we could split every post into two parts: human text and AI
output. So that humans could talk to humans and help improve mental models to each other, not
standing on a way of AI in doing leg work.

Best regards, Andrey Borodin.

#6Andres Freund
andres@anarazel.de
In reply to: Andrey Borodin (#5)
Re: PG19 regression in ecd9288624a: JSON_ARRAY() indexes that go stale

Hi,

On 2026-11-04 23:28:00 +0500, Andrey Borodin wrote:

On 4 Nov 2026, at 23:04, Andres Freund <andres@anarazel.de> wrote:

On 2026-11-03 22:37:52 -0800, Nikolay Samokhvalov wrote:

AI found this while testing v5 and prepared the attached patch; I
haven't manually reviewed the code yet.

Personally I think dropping unverified AI code onto the list has almost always
negative value, it just increases the already really high noise floor even
further. We all can ask LLMs to vomit out code. Putting it on the list
basically just means you're asking somebody else to do the actual work.

Do you think there was any human interaction in this thread before your
message?

Probably not. If indeed automated, I personally find that unacceptable. My
personal view is that anyone caught automatically interacting with the list
should simply be banned after one or two warnings. It just generates noise
without any benefit. We all have access to LLMs, if we want automated
unverified patches, it's dead trivial to set that up. And they very well might
be of higher quality. Just vomiting LLM patches without review and analysis
has purely negative value.

I like Nik's post transparency.

I think it's good to add it. I still don't see who is helped by getting
unreviewed LLM patches. Whether it's admitted to or not.

There's no way to escape LLMs. They exist.

I agree. I use LLMs. They're very very useful. I think that's a completely
orthogonal to it being ok / not ok to send stuff that nobody analyzed to
thousands of people. The list is not a chatbot output window.

The lists are clearly getting less and less readable. Parts of that is an
increase in the rate of issues being found - those are ofen real, and we have
to somehow deal with that. Part of that is lots of entities doing automated
patch review via the list. That's not useful and makes seeing actual content
ever harder.

I don'd see how making the lists unreadable is in the interest of even the
biggest LLM fan. Having lots of patches that no human looks at ever doesn't
help anyone. So I think we need to start enforcing some basic rules around
what is and what isn't ok.

Greetings,

Andres Freund

#7Kirk Wolak
wolakk@gmail.com
In reply to: Andres Freund (#6)
Re: PG19 regression in ecd9288624a: JSON_ARRAY() indexes that go stale

On Wed, Nov 4, 2026 at 1:45 PM Andres Freund <andres@anarazel.de> wrote:

The lists are clearly getting less and less readable. Parts of that is an
increase in the rate of issues being found - those are ofen real, and we
have
to somehow deal with that. Part of that is lots of entities doing
automated
patch review via the list. That's not useful and makes seeing actual
content
ever harder.

I don'd see how making the lists unreadable is in the interest of even the
biggest LLM fan. Having lots of patches that no human looks at ever doesn't
help anyone. So I think we need to start enforcing some basic rules around
what is and what isn't ok.

To be clear, we can agree here. Step 1 on a conversation with Nik and
Andrey was the concept of
human conversation plus [x] footnotes that reference the AI stuff you do
not want to see (and we want to be clear).

Footnoting seems like the PG approach here that should work. (Making the
research part of what
other AIs might want to consume, still available).

Also, in defense of Nik, he is not using "some LLM in chat". He is on
the cutting edge with tooling.
He has created (and tested, and improved) a group of working AIs that have
found numerous bugs,
then used other models to confirm they are real. He has gone as far as
integrating them into performance
monitoring down to OS Flame Graphs... Using various levels of AI
interaction. Rerunning things for days
until he, as an intelligent human, is concurring there is an issue... (ie,
this is NOT AI slop)

So, this problem is going to keep coming up. I personally AGREE with you
that we want to separate
what the AI was doing from the HUMAN discussion that requires
comprehension, in order to reduce the
cognitive load on the humans. How?

This is step one. But your response seemed to imply that AI related
patches and fixes are all the same,
and you don't want to see any part of them. Clarify if I am not correct.

My question is simply... Should we set a rule that such LLM generated
stuff should always be a footnote?
Personally, I think this makes sense. What say you?

Thanks!

#8Andrew Dunstan
andrew@dunslane.net
In reply to: Manu (#3)
Re: PG19 regression in ecd9288624a: JSON_ARRAY() indexes that go stale

On 2026-11-04 We 9:57 AM, Manu wrote:

An option for Andrew: making every array mutable on 19, as in 18,
also closes this, but rejects 47 more types that v5 and your patch
accept, among them int4[] and text[], whose output no setting changed.

I think it's far too late, on the eve of RC2, to be making decisions
like this. I think the best thing we can do is revert the feature and
have another go in the next development cycle. That leaves us still with
bugs, but stops us from introducing new bugs.

I propose to do that revert tomorrow.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

#9Manu
manuelreyesbravo@gmail.com
In reply to: Andrew Dunstan (#8)
Re: PG19 regression in ecd9288624a: JSON_ARRAY() indexes that go stale

Hi Andrew,

On Thu, Nov 5, 2026 at 4:41 PM Andrew Dunstan
<andrew@dunslane.net> wrote:

I think the best thing we can do is revert the feature and have
another go in the next development cycle. That leaves us still with
bugs, but stops us from introducing new bugs.

I propose to do that revert tomorrow.

Agreed. I tried the revert on REL_19_STABLE and master as of
de903547c74 and f1e97a29210: git revert applies cleanly, touches only
the six files of the original commit and no catalog, builds without
warnings, and make check and check-world pass on both.

After it, to_json_is_immutable() and to_jsonb_is_immutable() are
identical to 18.6's, and of the 330 types I tried in both, the same
ones are accepted in a json_array() index. All the 19-only stale
cases from this thread are rejected again, including Nik's float8[]
and the 24 array variants I posted after it. What can still go
stale is what 18.6 already has: types whose cast to json is created
or dropped after the index, and float, bytea and geometric values
after a change to extra_float_digits or bytea_output. On master,
where e75fcc9867f made those output functions stable, only the cast
cases remain.

One side effect: an index or stored generated column created on a 19
beta or RC with such an expression stays in place after the revert,
but a dump of it no longer restores.

Once it's in, I'll update the open item.

Regards,
Manu