Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9)
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: jose.morcillo.valenciano@gmail.com
Reply-To: jose.morcillo.valenciano@gmail.com, pgsql-bugs@lists.postgresql.org
Date: Thu, 19 Feb 2026 12:24:50 +0000
Message-ID: <19414-add8251d7863a802@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: <https://www.postgresql.org/message-id/19414-add8251d7863a802%40postgresql.org>
Precedence: bulk

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:       =20

Hi folks!!

Summary

An index scan on a partition of a HASH-partitioned table returns rows that
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 beh=
aviour.
        =E2=80=A2       Issue appears only when index scan is used.
        =E2=80=A2       Table and indexes originate from a cluster initiall=
y 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 reproducible
test case if needed.
REINDEX of similar partitions previously failed due to duplicate keys.


Thanks!!!