public inbox for [email protected]
help / color / mirror / Atom feedBUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9)
4+ messages / 4 participants
[nested] [flat]
* BUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9)
@ 2026-02-19 12:24 PG Bug reporting form <[email protected]>
2026-02-23 19:04 ` Re: BUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9) Rafia Sabih <[email protected]>
2026-02-23 20:23 ` Re: BUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9) Álvaro Herrera <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: PG Bug reporting form @ 2026-02-19 12:24 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
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!!!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9)
2026-02-19 12:24 BUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9) PG Bug reporting form <[email protected]>
@ 2026-02-23 19:04 ` Rafia Sabih <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Rafia Sabih @ 2026-02-23 19:04 UTC (permalink / raw)
To: [email protected]; [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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9)
2026-02-19 12:24 BUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9) PG Bug reporting form <[email protected]>
@ 2026-02-23 20:23 ` Álvaro Herrera <[email protected]>
2026-02-24 09:26 ` Re: BUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9) José Antonio Morcillo Valenciano <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: Álvaro Herrera @ 2026-02-23 20:23 UTC (permalink / raw)
To: [email protected]; [email protected]
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"
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9)
2026-02-19 12:24 BUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9) PG Bug reporting form <[email protected]>
2026-02-23 20:23 ` Re: BUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9) Álvaro Herrera <[email protected]>
@ 2026-02-24 09:26 ` José Antonio Morcillo Valenciano <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: José Antonio Morcillo Valenciano @ 2026-02-24 09:26 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: [email protected]
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 (<[email protected]>)
escribió:
> 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"
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-02-24 09:26 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-19 12:24 BUG #19414: Wrong rows returned by index scan on HASH-partitioned table with PRIMARY KEY (PostgreSQL 16.9) PG Bug reporting form <[email protected]>
2026-02-23 19:04 ` Rafia Sabih <[email protected]>
2026-02-23 20:23 ` Álvaro Herrera <[email protected]>
2026-02-24 09:26 ` José Antonio Morcillo Valenciano <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox