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

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