pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: idrissaajalloh@gmail.com
Subject: BUG #19863: Adding a partition hides a row in a DEFAULT subpartition with reordered columns
Date: Wed, 11 Nov 2026 05:25:46 +0000
Message-ID: <19863-8e20a9720ea67c30@postgresql.org> (raw)

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.








Message-ID: <19863-8e20a9720ea67c30@postgresql.org>
Permalink:  ../19863-8e20a9720ea67c30@postgresql.org/
Also on:    postgresql.org/message-id/19863-8e20a9720ea67c30@postgresql.org

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, idrissaajalloh@gmail.com
  Subject: Re: BUG #19863: Adding a partition hides a row in a DEFAULT subpartition with reordered columns
  In-Reply-To: <19863-8e20a9720ea67c30@postgresql.org>
  References: <19863-8e20a9720ea67c30@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox