[PG19] Indexes from 18 miss rows after pg_upgrade

Started by shihao zhongabout 1 month ago8 messageshackers
Jump to latest
#1shihao zhong
zhong950419@gmail.com

Hi hackers,

After pg_upgrade from 18 to 19, some indexes built by 18 miss rows that
a seq scan finds, and nothing tells the user to rebuild them. Three 19
commits stopped using LC_CTYPE, so these functions now give different
results for the same input. to_tsvector() and pg_trgm changed in ICU and
builtin databases (e113f9c102b). dmetaphone() changed there too, and in
multibyte libc databases with a non C locale (e39ece0343f). soundex()
and metaphone() changed in single-byte databases with a non C locale
(b96a9fd76f3), for example soundex('Éric') in LATIN1 fr_FR went from
É620 to R200.

On 18.6:

create database d template template0 encoding 'UTF8'
locale_provider builtin builtin_locale 'C.UTF-8' locale 'C';
\c d
create extension pg_trgm;
create table doc (t text);
insert into doc values ('foo—bar'), ('naïve');
create index on doc using gin (to_tsvector('simple', t));
create index on doc using gin (t gin_trgm_ops);

After pg_upgrade to 19, with enable_seqscan off, both queries return no
rows. A seq scan, or 18.6, returns one.

select * from doc
where to_tsvector('simple', t) @@ plainto_tsquery('simple',
'foo—bar');
select * from doc where t like '%naïve%';

The attached patch makes pg_upgrade find indexes that use these
functions or the pg_trgm opclasses, in the databases where the results
changed. --check warns, and the real run writes reindex_locale.sql with
a REINDEX for each one. The query takes 0.08 seconds on a database with
22000 indexes. Stored tsvector columns also keep what 18 computed and
REINDEX does not change them, so the release notes need a sentence for
those.

7dc95cc3b94 also changed some Snowball stems, like biologist to biolog
in english, so such indexes can miss rows in any database. Per Tom in
[2]: /messages/by-id/12827.1462331634@sss.pgh.pa.us

I tested upgrading 18.6 to REL_19_STABLE (9b505ef6cc1) on Linux. Without
the patch 24 of my index probes return fewer rows than a seq scan. After
running the generated script none do, and a cluster without such indexes
upgrades with no warning. The patch applies to master too. I think this
belongs on the open items list. It is item 9 of my list in [1]/messages/by-id/CAGRkXqSFdjqZP_kwRz7dv6LHX-CSrkDnynNXgntRsCEnQTKxmg@mail.gmail.com.

[1]: /messages/by-id/CAGRkXqSFdjqZP_kwRz7dv6LHX-CSrkDnynNXgntRsCEnQTKxmg@mail.gmail.com
/messages/by-id/CAGRkXqSFdjqZP_kwRz7dv6LHX-CSrkDnynNXgntRsCEnQTKxmg@mail.gmail.com
[2]: /messages/by-id/12827.1462331634@sss.pgh.pa.us

Thanks,
Shihao

Attachments:

t254202_1
0001-pg_upgrade-Rebuild-indexes-affected-by-19-s-local.patchapplication/octet-stream; name=0001-pg_upgrade-Rebuild-indexes-affected-by-19-s-local.patchDownload+140-1
#2Jeff Davis
pgsql@j-davis.com
In reply to: shihao zhong (#1)
Re: [PG19] Indexes from 18 miss rows after pg_upgrade

On Thu, 2026-10-15 at 00:58 -0400, shihao zhong wrote:

After pg_upgrade from 18 to 19, some indexes built by 18 miss rows
that
a seq scan finds, and nothing tells the user to rebuild them. Three
19
commits stopped using LC_CTYPE, so these functions now give different
results for the same input. to_tsvector() and pg_trgm changed in ICU
and
builtin databases (e113f9c102b). dmetaphone() changed there too, and
in
multibyte libc databases with a non C locale (e39ece0343f). soundex()
and metaphone() changed in single-byte databases with a non C locale
(b96a9fd76f3), for example soundex('Éric') in LATIN1 fr_FR went from
É620 to R200.

Thank you for the report.

From the discussion for e113f9c102b:

/messages/by-id/eb0a6586187fdc0b2449fdccded46ccc52d08bef.camel@j-davis.com

"Regarding the risk of behavior changes: this affects parsing the
values, but not the interpretation of values after parsing, so the risk
of index inconsistencies seems low. There's risk that a document parsed
in the old version would be parsed differently in the new version,
though. Overall, it seems comparable to the risk of fb1a18810f."

Mechanically, e113f9c102b is almost identical to fb1a18810f. The main
difference is for pg_trgm where the provider is ICU or builtin, and
LC_CTYPE=C. In that case, previously, all non-ASCII characters would be
treated as separators (because of LC_CTYPE=C); whereas after
e113f9c102b, it would use the database default locale which actually
looks for the character classification to find separators. The previous
behavior would seem fairly broken to me, but that also means the new
behavior would have a bigger impact. Is that still just a release note,
or does it demand something more?

7dc95cc3b94 also changed some Snowball stems, like biologist to
biolog
in english, so such indexes can miss rows in any database. Per Tom in
[2], that only needs a release note, so the patch leaves it out.

The notes already say:

"The Dutch stemmer has also been updated. The old Dutch stemmer is
available via dutch_porter."

though it looks like some other languages changed, too. Should that
note be expanded?

Regards,
Jeff Davis

#3shihao zhong
zhong950419@gmail.com
In reply to: Jeff Davis (#2)
Re: [PG19] Indexes from 18 miss rows after pg_upgrade

Hi Jeff,

Is that still just a release note, or does it demand something more?

I think it needs more. With LC_CTYPE C, text search changed too, not
only pg_trgm. In a builtin C.UTF-8 database:

select to_tsvector('simple', 'foo—bar don’t naïve');
18.6: 'don’t':2 'foo—bar':1 'naïve':3
19rc1: 'bar':2 'don':3 'foo':1 'naïve':5 't':4

In my upgrade test, every text search and pg_trgm index in ICU and
builtin databases with LC_CTYPE C missed rows. Any accent or curly
quote is enough.

I did some history check and found fb1a18810f, the 18 notes said to reindex
all of them, so I think pg_upgrade should name the indexes, as the
patch does.

Should that note be expanded?

Yes. Stems also changed for english, german, swedish, lithuanian,
french and norwegian.... The note may also need a cover extension like
fuzzystrmatch, which changed in libc databases too.

Thanks,
Shihao

#4Andreas Karlsson
andreas.karlsson@percona.com
In reply to: shihao zhong (#3)
Re: [PG19] Indexes from 18 miss rows after pg_upgrade

On 10/17/26 05:32, shihao zhong wrote:

Is that still just a release note, or does it demand something more?

I think it needs more. With LC_CTYPE C, text search changed too, not
only pg_trgm. In a builtin C.UTF-8 database:

    select to_tsvector('simple', 'foo—bar don’t naïve');
    18.6:   'don’t':2 'foo—bar':1 'naïve':3
    19rc1:  'bar':2 'don':3 'foo':1 'naïve':5 't':4

In my upgrade test, every text search and pg_trgm index in ICU and
builtin databases with LC_CTYPE C missed rows. Any accent or curly
quote is enough.

I did some history check and found fb1a18810f, the 18 notes said to reindex
all of them, so I think pg_upgrade should name the indexes, as the
patch does.

I disagree. What makes this different from any other change to a
function? Almost any change in output from a function can cause this.
And we have changed the behavior of functions before, e.g. in
fb1a18810f, and we have so far never added anything to check.c in
pg_upgrade for a case like this.

If this is something we want to change we should make this a larger
discussion. Maybe if it affected almost everyone but I do not think it does.

This feels like something which is best handled in the release notes.

Should that note be expanded?

Yes. Stems also changed for english, german, swedish, lithuanian,
french and norwegian.... The note may also need a cover extension like
fuzzystrmatch, which changed in libc databases too.

Agreed, it should probably be expanded and made sure people understand
the scope of the issue.

Andreas

#5shihao zhong
zhong950419@gmail.com
In reply to: Andreas Karlsson (#4)
Re: [PG19] Indexes from 18 miss rows after pg_upgrade

Hi Andreas,

we have so far never added anything to check.c in pg_upgrade for a
case like this.

There is one. In 18, check_for_unicode_update() (b81ffa13e35) warns
about indexes on lower(), upper(), regexp and normalize() in builtin
databases when the Unicode version changes. It fires on 18 to 19 in my
test. So pg_upgrade warns about a lower() index, and says nothing
about a text search index in the same kind of database that misses
rows for any accented word.

My patch only adds more functions to that same idea. Jeff, since you
wrote that check, what do you think? If the answer is release notes,
I am fine with that, and I can draft the text, including the stemmer
languages that changed.

Thanks,
Shihao

#6Jeff Davis
pgsql@j-davis.com
In reply to: shihao zhong (#3)
Re: [PG19] Indexes from 18 miss rows after pg_upgrade

On Fri, 2026-10-16 at 23:32 -0400, shihao zhong wrote:

Hi Jeff,

Is that still just a release note, or does it demand something
more?

I think it needs more.

I committed the release note for tsearch & pg_trgm, as that is clearly
needed.

Should that note be expanded?

Yes. Stems also changed for english, german, swedish, lithuanian,
french and norwegian.... The note may also need a cover extension
like
fuzzystrmatch,  which changed in libc databases too.

Attached a patch to expand the stemmer note.

Regards,
Jeff Davis

Attachments:

v1-0001-PG-19-expand-on-release-notes-for-stemmer.patchtext/x-patch; charset=UTF-8; name=v1-0001-PG-19-expand-on-release-notes-for-stemmer.patchDownload+9-3
#7Jeff Davis
pgsql@j-davis.com
In reply to: shihao zhong (#5)
Re: [PG19] Indexes from 18 miss rows after pg_upgrade

On Wed, 2026-10-21 at 23:39 -0400, shihao zhong wrote:

Hi Andreas,

we have so far never added anything to check.c in pg_upgrade for a
case like this.

There is one. In 18, check_for_unicode_update() (b81ffa13e35) warns
about indexes on lower(), upper(), regexp and normalize() in builtin
databases when the Unicode version changes.

I don't think I'd like to use that as a precedent for any function
behavior change, it will bloat pg_upgrade with fairly complex queries
that will only get exercised during one cycle. And it will always be
possible to miss some cases (e.g. due to affected code hiding in a SQL
function), so it can give a false sense of security.

I committed the release note changes, and I'll close the open item.

Thank you.

Regards,
Jeff Davis

#8shihao zhong
zhong950419@gmail.com
In reply to: Jeff Davis (#7)
Re: [PG19] Indexes from 18 miss rows after pg_upgrade

Hi Jeff,

Just found out that I forgot to send my email yesterday.

Thanks for your patch and I agree with you and thanks
for committing that.

Thanks,
Shihao