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

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é 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 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!!!

--
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH

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
• 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?

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.

--
Álvaro Herrera PostgreSQL Developer — 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"

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, Álvaro Herrera (<alvherre@kurilemu.de>)
escribió:

On 2026-Feb-24, José Antonio Morcillo Valenciano wrote:

Hi!

After upgrading we reindex all our databases, so I don't see it clear.

Oh, sorry, you're right, the problem is potentially not with indexes,
but instead it's with the partition key. This would mean that some rows
which previously belonged in one partition, should after the upgrade be
moved to some other partition due to changes in collation rules.

I suspect it's not easy to find such items without fully scanning each
partition. Failing that, you would have to recheck the partition
constraint for each row in each partition, and move rows across
partitions if the partition constraint fails to hold. I don't have a
clear idea how to automate this.

The difficulty here is finding a test case to work with.

Yes, that is ONE difficulty, but I don't think it's the only one, or
even the most difficult one.

--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
¡Ay, ay, ay! Con lo mucho que yo lo quería (bis)
se fue de mi vera ... se fue para siempre, pa toíta ... pa toíta la vida
¡Ay Camarón! ¡Ay Camarón! (Paco de Lucía)