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], that only needs a release note, so the patch leaves it out.
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].
[1]
https://postgr.es/m/cagrkxqsfdjqzp_kwrz7dv6lhx-csrkdnynnxgntrscenqtk...@mail.gmail.com
[2] https://postgr.es/m/[email protected]
Thanks,
Shihao
0001-pg_upgrade-Rebuild-indexes-affected-by-19-s-local.patch
Description: Binary data
