I will review this and hopefully apply it on Friday.
--------------------------------------------------------------------------- On Sat, Oct 31, 2026 at 11:24:33PM -0400, shihao zhong wrote: > Hi Bruce, > > One more item for the migration section. After the BUG #19749 fix > (18740523a), older btree indexes on character columns must be rebuilt. > The same goes for oidvector. pg_upgrade does not do it. Until then, > index-only scans can return wrong values. > > The attached patch adds a short entry that tells people to REINDEX them > after upgrading. It is for REL_19_STABLE only. Would you add it? > > The fix was also back-patched to 14 through 18 (882fd416d6e on 18). > The next minor release notes for those branches need the same REINDEX > advice for character indexes. oidvector keeps deduplication there, so > only 19 needs it. > > Thanks, > Shihao > From 83088d79d6d7ecb998070afa06bcce30ebe33a4d Mon Sep 17 00:00:00 2001 > From: Shihao Zhong <[email protected]> > Date: Sat, 31 Oct 2026 22:52:18 -0400 > Subject: [PATCH v1] doc: Add PG 19 migration note about bpchar index > deduplication. > > Commit 18740523a4a stopped nbtree deduplication for bpchar indexes, but > indexes built by earlier major versions keep the old allequalimage flag. > pg_upgrade does not rebuild them, and pg_upgrade --check does not warn > about them, so amcheck reports them until they are rebuilt, and an > index-only scan can return a value that differs from the heap in > trailing spaces. Tell users to REINDEX them after upgrading. > > Discussion: > https://postgr.es/m/CAGRkXqQQ311Lg=62x5udztpurfgpt6xppmw4x9e3tgbtu16...@mail.gmail.com > --- > doc/src/sgml/release-19.sgml | 32 ++++++++++++++++++++++++++++++++ > 1 file changed, 32 insertions(+) > > diff --git a/doc/src/sgml/release-19.sgml b/doc/src/sgml/release-19.sgml > index 253fa07179a..91eb2a3aae3 100644 > --- a/doc/src/sgml/release-19.sgml > +++ b/doc/src/sgml/release-19.sgml > @@ -431,6 +431,38 @@ recommended to reindex any indexes that use these > functions after the upgrade. > </para> > </listitem> > > +<!-- > +Author: Peter Geoghegan <[email protected]> > +2026-10-06 [18740523a] Rescind unsafe deduplication support. > +--> > + > +<listitem> > +<para> > +Disable <link linkend="btree-deduplication">B-tree deduplication</link> for > +indexes on <type>character</type> (<type>bpchar</type>) columns > +(Shihao Zhong, Peter Geoghegan) > +<ulink url="&commit_baseurl;18740523a">§</ulink> > +</para> > + > +<para> > +Values of <type>character</type> that differ only in trailing spaces compare > +as equal but are stored differently, so deduplication could make an > index-only > +scan return a value other than the one in the table. Indexes built by > earlier > +major versions still claim to support deduplication, and > +<link linkend="pgupgrade"><application>pg_upgrade</application></link> does > +not change that. After upgrading, run <command>REINDEX</command> on every > +B-tree index on a <type>character</type> column, including > +<literal>character(<replaceable>n</replaceable>)</literal> columns and > indexes > +that use <literal>bpchar_pattern_ops</literal>. > +<xref linkend="amcheck"/> reports such indexes until they are rebuilt. > +</para> > + > +<para> > +The same applies to B-tree indexes on <type>oidvector</type> columns, which > +are rarely used outside the system catalogs. > +</para> > +</listitem> > + > <!-- > Author: Thomas Munro <[email protected]> > 2026-04-08 [77645d44e] Remove MULE_INTERNAL encoding. > -- > 2.37.1 (Apple Git-137.1) > -- Bruce Momjian <[email protected]> https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
