MIME-Version: 1.0
References: <19414-add8251d7863a802@postgresql.org>
In-Reply-To: <19414-add8251d7863a802@postgresql.org>
From: Rafia Sabih <rafia.pghackers@gmail.com>
Date: Mon, 23 Feb 2026 11:04:54 -0800
Message-ID: <CA+FpmFcMqn8hf92g90iVbSVjKtJCVc7q=L5Syh2qJCnymr2y=Q@mail.gmail.com>
Subject: Re: BUG #19414: Wrong rows returned by index scan on HASH-partitioned
 table with PRIMARY KEY (PostgreSQL 16.9)
To: jose.morcillo.valenciano@gmail.com, pgsql-bugs@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000efbf0d064b82721c"
Archived-At: <https://www.postgresql.org/message-id/CA%2BFpmFcMqn8hf92g90iVbSVjKtJCVc7q%3DL5Syh2qJCnymr2y%3DQ%40mail.gmail.com>
Precedence: bulk

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

Hello,

Thanks for reporting, but we can be more helpful if you may provide a
reproducible case to investigate this further.

On Fri, 20 Feb 2026 at 00:21, PG Bug reporting form <noreply@postgresql.org=
>
wrote:

> The following bug has been logged on the website:
>
> Bug reference:      19414
> Logged by:          Jos=C3=A9 Antonio Morcillo Valenciano
> Email address:      jose.morcillo.valenciano@gmail.com
> PostgreSQL version: 16.9
> Operating system:   Red Hat Enterprise Linux 9.4 (Plow)
> Description:
>
> Hi folks!!
>
> Summary
>
> An index scan on a partition of a HASH-partitioned table returns rows tha=
t
> do not satisfy the query predicate.
> Sequential scans return correct results.
> The issue affects a PRIMARY KEY index inherited from a partitioned table.
> amcheck reports no corruption.
>
> Table definition (simplified)
>
> CREATE TABLE r_has_stock (
>     site_public_id varchar(10) NOT NULL,
>     site_storage_location_id varchar(4) NOT NULL,
>     product_format_public_id varchar(18) NOT NULL,
>     logisticvariantid varchar(1) NOT NULL,
>     stockstatus_id varchar(3) NOT NULL,
>     has_stock boolean,
>     PRIMARY KEY (
>       site_public_id,
>       site_storage_location_id,
>       product_format_public_id,
>       logisticvariantid,
>       stockstatus_id
>     )
> ) PARTITION BY HASH (site_public_id);
>
> The table has 10 HASH partitions:
> FOR VALUES WITH (modulus 10, remainder N)
>
> Problem description
>
> 1. Query using default plan (index scan)
> EXPLAIN (ANALYZE, VERBOSE, BUFFERS)
> SELECT *
> FROM r_has_stock
> WHERE site_public_id =3D '2781'
>   AND product_format_public_id =3D '21594';
>
> Plan:
> Index Scan using has_stock_p6_pkey
>
> Result:
> 1 row returned
>
> Returned row values:
> site_public_id =3D 2175
> product_format_public_id =3D 84706
>
> Row does NOT match predicate.
>
> 2. Force heap scan (disable index access)
>
> BEGIN;
> SET LOCAL enable_indexscan =3D off;
> SET LOCAL enable_bitmapscan =3D off;
> SET LOCAL enable_indexonlyscan =3D off;
>
> SELECT *
> FROM r_has_stock
> WHERE site_public_id =3D '2781'
>   AND product_format_public_id =3D '21594';
>
> ROLLBACK;
>
> Result:
> 0 rows
>
> 3. Verify tuple in partition
>
> SELECT ctid, site_public_id, product_format_public_id
> FROM has_stock_p6
> WHERE site_public_id =3D '2781'
>   AND product_format_public_id =3D '21594';
>
> Result:
> 0 rows
>
> But:
> SELECT ctid, site_public_id, product_format_public_id
> FROM has_stock_p6
> WHERE site_public_id =3D '2175'
>   AND product_format_public_id =3D '84706';
>
> returns:
> (3157,8)
>
> 4. amcheck results
> SELECT bt_index_check('has_stock_p6_pkey'::regclass, false);
> SELECT bt_index_check('has_stock_p6_pkey'::regclass, true);
> SELECT bt_index_parent_check('has_stock_p6_pkey'::regclass, true, true);
>
> All return success (no errors).
>
> Expected behavior
>
> Index scan should return exactly the same rows as heap scan.
>
> Expected result:
>
> 0 rows
>
> Actual behavior
>
> Index scan returns a tuple that does not satisfy the predicate.
>
> Additional checks
>
> Data checksums
>
> SHOW data_checksums;
>
> on
>
> Checksum failures
>
> SELECT datname, checksum_failures, checksum_last_failure
> FROM pg_stat_database
> WHERE datname =3D current_database();
>
> Result:
>
> checksum_failures =3D 0
>
> 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 b=
ehaviour.
>         =E2=80=A2       Issue appears only when index scan is used.
>         =E2=80=A2       Table and indexes originate from a cluster initia=
lly
> 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 later
> upgraded?
>
> Availability
>
> I can provide additional details or attempt to build a reduced reproducib=
le
> test case if needed.
> REINDEX of similar partitions previously failed due to duplicate keys.
>
>
> Thanks!!!
>
>
>
>
>

--=20
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH

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

<div dir=3D"ltr">Hello,<div><br></div><div>Thanks for reporting, but we can=
 be more helpful if you may provide a reproducible case to investigate this=
 further.</div></div><br><div class=3D"gmail_quote gmail_quote_container"><=
div dir=3D"ltr" class=3D"gmail_attr">On Fri, 20 Feb 2026 at 00:21, PG Bug r=
eporting form &lt;<a href=3D"mailto:noreply@postgresql.org">noreply@postgre=
sql.org</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"=
margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-lef=
t:1ex">The following bug has been logged on the website:<br>
<br>
Bug reference:=C2=A0 =C2=A0 =C2=A0 19414<br>
Logged by:=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Jos=C3=A9 Antonio Morcillo Val=
enciano<br>
Email address:=C2=A0 =C2=A0 =C2=A0 <a href=3D"mailto:jose.morcillo.valencia=
no@gmail.com" target=3D"_blank">jose.morcillo.valenciano@gmail.com</a><br>
PostgreSQL version: 16.9<br>
Operating system:=C2=A0 =C2=A0Red Hat Enterprise Linux 9.4 (Plow)<br>
Description:=C2=A0 =C2=A0 =C2=A0 =C2=A0 <br>
<br>
Hi folks!!<br>
<br>
Summary<br>
<br>
An index scan on a partition of a HASH-partitioned table returns rows that<=
br>
do not satisfy the query predicate.<br>
Sequential scans return correct results.<br>
The issue affects a PRIMARY KEY index inherited from a partitioned table.<b=
r>
amcheck reports no corruption.<br>
<br>
Table definition (simplified)<br>
<br>
CREATE TABLE r_has_stock (<br>
=C2=A0 =C2=A0 site_public_id varchar(10) NOT NULL,<br>
=C2=A0 =C2=A0 site_storage_location_id varchar(4) NOT NULL,<br>
=C2=A0 =C2=A0 product_format_public_id varchar(18) NOT NULL,<br>
=C2=A0 =C2=A0 logisticvariantid varchar(1) NOT NULL,<br>
=C2=A0 =C2=A0 stockstatus_id varchar(3) NOT NULL,<br>
=C2=A0 =C2=A0 has_stock boolean,<br>
=C2=A0 =C2=A0 PRIMARY KEY (<br>
=C2=A0 =C2=A0 =C2=A0 site_public_id,<br>
=C2=A0 =C2=A0 =C2=A0 site_storage_location_id,<br>
=C2=A0 =C2=A0 =C2=A0 product_format_public_id,<br>
=C2=A0 =C2=A0 =C2=A0 logisticvariantid,<br>
=C2=A0 =C2=A0 =C2=A0 stockstatus_id<br>
=C2=A0 =C2=A0 )<br>
) PARTITION BY HASH (site_public_id);<br>
<br>
The table has 10 HASH partitions:<br>
FOR VALUES WITH (modulus 10, remainder N)<br>
<br>
Problem description<br>
<br>
1. Query using default plan (index scan)<br>
EXPLAIN (ANALYZE, VERBOSE, BUFFERS)<br>
SELECT *<br>
FROM r_has_stock<br>
WHERE site_public_id =3D &#39;2781&#39;<br>
=C2=A0 AND product_format_public_id =3D &#39;21594&#39;;<br>
<br>
Plan:<br>
Index Scan using has_stock_p6_pkey<br>
<br>
Result:<br>
1 row returned<br>
<br>
Returned row values:<br>
site_public_id =3D 2175<br>
product_format_public_id =3D 84706<br>
<br>
Row does NOT match predicate.<br>
<br>
2. Force heap scan (disable index access)<br>
<br>
BEGIN;<br>
SET LOCAL enable_indexscan =3D off;<br>
SET LOCAL enable_bitmapscan =3D off;<br>
SET LOCAL enable_indexonlyscan =3D off;<br>
<br>
SELECT *<br>
FROM r_has_stock<br>
WHERE site_public_id =3D &#39;2781&#39;<br>
=C2=A0 AND product_format_public_id =3D &#39;21594&#39;;<br>
<br>
ROLLBACK;<br>
<br>
Result:<br>
0 rows<br>
<br>
3. Verify tuple in partition<br>
<br>
SELECT ctid, site_public_id, product_format_public_id<br>
FROM has_stock_p6<br>
WHERE site_public_id =3D &#39;2781&#39;<br>
=C2=A0 AND product_format_public_id =3D &#39;21594&#39;;<br>
<br>
Result:<br>
0 rows<br>
<br>
But:<br>
SELECT ctid, site_public_id, product_format_public_id<br>
FROM has_stock_p6<br>
WHERE site_public_id =3D &#39;2175&#39;<br>
=C2=A0 AND product_format_public_id =3D &#39;84706&#39;;<br>
<br>
returns:<br>
(3157,8)<br>
<br>
4. amcheck results<br>
SELECT bt_index_check(&#39;has_stock_p6_pkey&#39;::regclass, false);<br>
SELECT bt_index_check(&#39;has_stock_p6_pkey&#39;::regclass, true);<br>
SELECT bt_index_parent_check(&#39;has_stock_p6_pkey&#39;::regclass, true, t=
rue);<br>
<br>
All return success (no errors).<br>
<br>
Expected behavior<br>
<br>
Index scan should return exactly the same rows as heap scan.<br>
<br>
Expected result:<br>
<br>
0 rows<br>
<br>
Actual behavior<br>
<br>
Index scan returns a tuple that does not satisfy the predicate.<br>
<br>
Additional checks<br>
<br>
Data checksums<br>
<br>
SHOW data_checksums;<br>
<br>
on<br>
<br>
Checksum failures<br>
<br>
SELECT datname, checksum_failures, checksum_last_failure<br>
FROM pg_stat_database<br>
WHERE datname =3D current_database();<br>
<br>
Result:<br>
<br>
checksum_failures =3D 0<br>
<br>
Additional notes<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =E2=80=A2=C2=A0 =C2=A0 =C2=A0 =C2=A0Database co=
llation: en_US.UTF-8<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =E2=80=A2=C2=A0 =C2=A0 =C2=A0 =C2=A0Server enco=
ding: UTF8<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =E2=80=A2=C2=A0 =C2=A0 =C2=A0 =C2=A0Explicit ca=
sts and COLLATE tests did not change behaviour.<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =E2=80=A2=C2=A0 =C2=A0 =C2=A0 =C2=A0Issue appea=
rs only when index scan is used.<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =E2=80=A2=C2=A0 =C2=A0 =C2=A0 =C2=A0Table and i=
ndexes originate from a cluster initially running<br>
PostgreSQL 14.12 and later upgraded to 16.9.<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =E2=80=A2=C2=A0 =C2=A0 =C2=A0 =C2=A0After reind=
ex the issue was resolved.<br>
<br>
<br>
Question<br>
<br>
Could this be related to HASH partitioning combined with PRIMARY KEY /<br>
UNIQUE indexes, or to data created in older PostgreSQL versions and later<b=
r>
upgraded?<br>
<br>
Availability<br>
<br>
I can provide additional details or attempt to build a reduced reproducible=
<br>
test case if needed.<br>
REINDEX of similar partitions previously failed due to duplicate keys.<br>
<br>
<br>
Thanks!!!<br>
<br>
<br>
<br>
<br>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr">Regards,<br>Rafia Sabih<div>CYBERTEC PostgreSQL =
International GmbH<br></div></div></div>

--000000000000efbf0d064b82721c--