BUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9)

The following bug has been logged on the website:

Bug reference: 19414
Logged by: José Antonio Morcillo Valenciano
Email address: jose(dot)morcillo(dot)valenciano(at)gmail(dot)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 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 = '2781'
AND product_format_public_id = '21594';

Plan:
Index Scan using has_stock_p6_pkey

Result:
1 row returned

Returned row values:
site_public_id = 2175
product_format_public_id = 84706

Row does NOT match predicate.

2. Force heap scan (disable index access)

BEGIN;
SET LOCAL enable_indexscan = off;
SET LOCAL enable_bitmapscan = off;
SET LOCAL enable_indexonlyscan = off;

SELECT *
FROM r_has_stock
WHERE site_public_id = '2781'
AND product_format_public_id = '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 = '2781'
AND product_format_public_id = '21594';

Result:
0 rows

But:
SELECT ctid, site_public_id, product_format_public_id
FROM has_stock_p6
WHERE site_public_id = '2175'
AND product_format_public_id = '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 = current_database();

Result:

checksum_failures = 0

Additional notes
• Database collation: en_US.UTF-8
• Server encoding: UTF8
• Explicit casts and COLLATE tests did not change behaviour.
• Issue appears only when index scan is used.
• Table and indexes originate from a cluster initially running
PostgreSQL 14.12 and later upgraded to 16.9.
• 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!!!