Re: 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(at)postgresql(dot)org>
wrote:

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

--
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH