public inbox for [email protected]
help / color / mirror / Atom feedFrom: Rafia Sabih <[email protected]>
To: [email protected]
To: [email protected]
Subject: Re: BUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9)
Date: Mon, 23 Feb 2026 11:04:54 -0800
Message-ID: <CA+FpmFcMqn8hf92g90iVbSVjKtJCVc7q=L5Syh2qJCnymr2y=Q@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
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 <[email protected]>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 19414
> Logged by: José Antonio Morcillo Valenciano
> Email address: [email protected]
> 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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: BUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9)
In-Reply-To: <CA+FpmFcMqn8hf92g90iVbSVjKtJCVc7q=L5Syh2qJCnymr2y=Q@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox