Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR5j1-00000000fn9-455U for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 10:38:43 +0000 Received: from localhost ([127.0.0.1]:44518 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xR5iz-0000000AE0t-1LGa for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 10:38:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:41014) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR0qW-00000008o4R-3aQb for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 05:26:09 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR0qU-00000000PAP-1rop for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 05:26:08 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=GuNp6b7APg6ygLlDlIYrBuwzOykovO0ekTx+QCHd5Ls=; b=Z0yJFPK77RyduS4jHvS1/eflW8 jErjIRYT2gM3/SF35cjffYG5Aan83NK+VURlLaHXca7z7ND7EOZkPTex93FDk2AG4B5PlCFN6AtVM 7Tik+ZfcbBSqTTfJfoDtTqx5fJGWXv3kUvUVb0crDbodrD+RsdBaGU0I+s6HXMf/t1s6O2mlhRsmx 4K1r+Y9wbVhnFB/taPgpYadlHJd6zO3nSgvvR6hUAa4Q32Atkb+Edjogw+ang1H5/hQj9/kKWmiWG q2vQLXTqdEKasv0slxxIalGIsUQlTYd7OByslyTXYgG68jeahjZo83s2rTeNTMLC5OULeaMb1pFJ1 vW4bHO8A==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR0qS-00000000sib-2q4q for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 05:26:05 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xR0qR-00000002Hfz-2B2Y for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 05:26:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19863: Adding a partition hides a row in a DEFAULT subpartition with reordered columns To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: idrissaajalloh@gmail.com Reply-To: idrissaajalloh@gmail.com, pgsql-bugs@lists.postgresql.org Date: Wed, 11 Nov 2026 05:25:46 +0000 Message-ID: <19863-8e20a9720ea67c30@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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: =20 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 >=3D 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 =3D 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 =3D 5; SELECT count(*) AS physically_in_default_leaf FROM ONLY leaf_p WHERE a =3D 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 =3D 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.