MIME-Version: 1.0
References: <19414-add8251d7863a802@postgresql.org> <202602232020.e2fyql4ip6hd@alvherre.pgsql>
In-Reply-To: <202602232020.e2fyql4ip6hd@alvherre.pgsql>
From: =?UTF-8?Q?Jos=C3=A9_Antonio_Morcillo_Valenciano?= <jose.morcillo.valenciano@gmail.com>
Date: Tue, 24 Feb 2026 10:26:45 +0100
Message-ID: <CA+nR40nyF++Y9v6u6qrSGMmhH9FxzoN_d4NAMXQDtn5KCaqaWA@mail.gmail.com>
Subject: Re: BUG #19414: Wrong rows returned by index scan on HASH-partitioned
 table with PRIMARY KEY (PostgreSQL 16.9)
To: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>
Cc: pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000002e6341064b8e7dfc"
Archived-At: <https://www.postgresql.org/message-id/CA%2BnR40nyF%2B%2BY9v6u6qrSGMmhH9FxzoN_d4NAMXQDtn5KCaqaWA%40mail.gmail.com>
Precedence: bulk

--0000000000002e6341064b8e7dfc
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi!

After upgrading we reindex all our databases, so I don't see it clear.
The difficulty here is finding a test case to work with.


El lun, 23 feb 2026 a las 21:27, =C3=81lvaro Herrera (<alvherre@kurilemu.de=
>)
escribi=C3=B3:

> On 2026-Feb-19, PG Bug reporting form wrote:
>
> > CREATE TABLE r_has_stock (
> >     site_public_id varchar(10) NOT NULL,
> [...]
> > ) PARTITION BY HASH (site_public_id);
> [...]
> > Additional notes
> >         =E2=80=A2       Database collation: en_US.UTF-8
> >         =E2=80=A2       Server encoding: UTF8
> >         =E2=80=A2       Explicit casts and COLLATE tests did not change
> behaviour.
> >         =E2=80=A2       Issue appears only when index scan is used.
> >         =E2=80=A2       Table and indexes originate from a cluster init=
ially
> running
> > PostgreSQL 14.12 and later upgraded to 16.9.
> >         =E2=80=A2       After reindex the issue was resolved.
> >
> >
> > Question
> >
> > Could this be related to HASH partitioning combined with PRIMARY KEY /
> > UNIQUE indexes, or to data created in older PostgreSQL versions and lat=
er
> > upgraded?
>
> I would be pretty certain that the problem is related to collation
> changes, since the partition key is varchar and you said the problem
> disappeared after the reindex.
>
> The solution is exactly that: reindex any indexes potentially affected
> by collation changes as part of a platform upgrade.
>
> --
> =C3=81lvaro Herrera         PostgreSQL Developer  =E2=80=94
> https://www.EnterpriseDB.com/
> "No hay hombre que no aspire a la plenitud, es decir,
> la suma de experiencias de que un hombre es capaz"
>
>
>

--0000000000002e6341064b8e7dfc
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi!<div><br></div><div>After upgrading we=
 reindex all our databases, so I don&#39;t see it clear.</div><div>The diff=
iculty here is finding a test case to work with.</div><div><br></div><div><=
br></div></div><br><div class=3D"gmail_quote gmail_quote_container"><div di=
r=3D"ltr" class=3D"gmail_attr">El lun, 23 feb 2026 a las 21:27, =C3=81lvaro=
 Herrera (&lt;<a href=3D"mailto:alvherre@kurilemu.de">alvherre@kurilemu.de<=
/a>&gt;) escribi=C3=B3:<br></div><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex">On 2026-Feb-19, PG Bug reporting form wrote:<br>
<br>
&gt; CREATE TABLE r_has_stock (<br>
&gt;=C2=A0 =C2=A0 =C2=A0site_public_id varchar(10) NOT NULL,<br>
[...]<br>
&gt; ) PARTITION BY HASH (site_public_id);<br>
[...]<br>
&gt; Additional notes<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=E2=80=A2=C2=A0 =C2=A0 =C2=A0 =C2=A0D=
atabase collation: en_US.UTF-8<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=E2=80=A2=C2=A0 =C2=A0 =C2=A0 =C2=A0S=
erver encoding: UTF8<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=E2=80=A2=C2=A0 =C2=A0 =C2=A0 =C2=A0E=
xplicit casts and COLLATE tests did not change behaviour.<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=E2=80=A2=C2=A0 =C2=A0 =C2=A0 =C2=A0I=
ssue appears only when index scan is used.<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=E2=80=A2=C2=A0 =C2=A0 =C2=A0 =C2=A0T=
able and indexes originate from a cluster initially running<br>
&gt; PostgreSQL 14.12 and later upgraded to 16.9.<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=E2=80=A2=C2=A0 =C2=A0 =C2=A0 =C2=A0A=
fter reindex the issue was resolved.<br>
&gt; <br>
&gt; <br>
&gt; Question<br>
&gt; <br>
&gt; Could this be related to HASH partitioning combined with PRIMARY KEY /=
<br>
&gt; UNIQUE indexes, or to data created in older PostgreSQL versions and la=
ter<br>
&gt; upgraded?<br>
<br>
I would be pretty certain that the problem is related to collation<br>
changes, since the partition key is varchar and you said the problem<br>
disappeared after the reindex.<br>
<br>
The solution is exactly that: reindex any indexes potentially affected<br>
by collation changes as part of a platform upgrade.<br>
<br>
-- <br>
=C3=81lvaro Herrera=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0PostgreSQL Developer=
=C2=A0 =E2=80=94=C2=A0 <a href=3D"https://www.EnterpriseDB.com/" rel=3D"nor=
eferrer" target=3D"_blank">https://www.EnterpriseDB.com/</a><br>
&quot;No hay hombre que no aspire a la plenitud, es decir,<br>
la suma de experiencias de que un hombre es capaz&quot;<br>
<br>
<br>
</blockquote></div></div>

--0000000000002e6341064b8e7dfc--