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:

https://www.postgresql.org/message-id/eb0a6586187fdc0b2449fdccded46ccc52d08bef.camel%40j-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