BUG #19863: Adding a partition hides a row in a DEFAULT subpartition with reordered columns

Поиск
Список
Период
Сортировка
Искать

BUG #19863: Adding a partition hides a row in a DEFAULT subpartition with reordered columns

От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website:

Bug reference:      19863
Logged by:          Wren
Email address:      idrissaajalloh@gmail.com
PostgreSQL version: 18.6
Operating system:   Linux/amd64
Description:        

Creating a range partition incorrectly succeeds when the existing DEFAULT
partition has a subpartition whose physical column order differs from its
parent and whose CHECK constraint refers to the first physical column. The
DEFAULT subtree already contains a row covered by the new partition bound.
PostgreSQL should reject the command because the updated DEFAULT partition
constraint would be violated. Instead, it creates the partition, after which
partition pruning makes the existing row invisible through the partitioned
root.

How to repeat:

```sql
DROP TABLE IF EXISTS root_p CASCADE;

CREATE TABLE root_p (a int, b int)
  PARTITION BY RANGE (a);
CREATE TABLE default_p
  PARTITION OF root_p DEFAULT
  PARTITION BY RANGE (b);

-- Reverse the physical column order relative to the parent. The validated
-- CHECK refers to b and says nothing about the root partition key a.
CREATE TABLE leaf_p
  (b int CHECK (b >= 100 AND b < 200), a int);
ALTER TABLE default_p
  ATTACH PARTITION leaf_p
  FOR VALUES FROM (100) TO (200);

-- This row currently belongs to the DEFAULT branch, but the proposed root
-- partition for a in [0,10) would claim it.
INSERT INTO root_p VALUES (5, 150);

SELECT tableoid::regclass AS stored_in, a, b
FROM root_p;

-- This must be rejected because the DEFAULT subtree contains a = 5.
CREATE TABLE new_p
  PARTITION OF root_p
  FOR VALUES FROM (0) TO (10);

SELECT count(*) AS visible_through_root
FROM root_p
WHERE a = 5;
SELECT count(*) AS physically_in_default_leaf
FROM ONLY leaf_p
WHERE a = 5;

SELECT c.relname, pg_get_expr(c.relpartbound, c.oid) AS bound
FROM pg_class c
WHERE c.relname IN ('root_p', 'default_p', 'leaf_p', 'new_p')
ORDER BY c.relname;
```

The row is initially stored in `leaf_p`. The partition creation then
succeeds, and the decisive output is:

```
CREATE TABLE
 visible_through_root
----------------------
                    0
(1 row)

 physically_in_default_leaf
----------------------------
                          1
(1 row)

  relname  |             bound
-----------+--------------------------------
 default_p | DEFAULT
 leaf_p    | FOR VALUES FROM (100) TO (200)
 new_p     | FOR VALUES FROM (0) TO (10)
 root_p    |
(4 rows)
```

Because the row has `a = 5`, it belongs to the new root partition bound
`[0,10)`. The CREATE TABLE command should fail with `updated partition
constraint ... would be violated by some row`, leaving the row visible
through `root_p`.

A likely cause is that the expression used by
`check_default_partition_contents()` is not mapped to the leaf's reordered
columns. The CHECK constraint on the leaf's physical attribute 1 (`b`) can
therefore appear to prove a condition intended for the root's attribute 1
(`a`), causing the required scan to be skipped.

The issue was reproduced on PostgreSQL 20devel and PostgreSQL 18.6 on
Linux/amd64. This occurs when a new range partition overlaps an existing row
in a DEFAULT subpartition whose leaf has reordered columns and a CHECK
constraint on its first physical column.




FAQ