MIME-Version: 1.0
References: <C18878AB-DEB2-4A61-9995-A035DD644B81@gmail.com>
In-Reply-To: <C18878AB-DEB2-4A61-9995-A035DD644B81@gmail.com>
From: Kirill Reshke <reshkekirill@gmail.com>
Date: Wed, 13 May 2026 10:07:56 +0500
Message-ID: 
 <CALdSSPhquxjxisujDcfLqfoY2=SnUAHhsqgM9PvO0uO3uZC9jQ@mail.gmail.com>
Subject: Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
To: Chao Li <li.evan.chao@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>,
 Dmitry Koval <d.koval@postgrespro.ru>,
	Alexander Korotkov <aekorotkov@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CALdSSPhquxjxisujDcfLqfoY2%3DSnUAHhsqgM9PvO0uO3uZC9jQ%40mail.gmail.com>
Precedence: bulk

On Wed, 13 May 2026 at 09:39, Chao Li <li.evan.chao@gmail.com> wrote:
>
> Hi,
>
> While testing ALTER TABLE ... SPLIT PARTITION, I found a bug and a few behaviors and messages that seem worth improving.
>
> 0. A bound-overlap bug
>
> I numbered this item as 0 because I found it after finishing items 1, 2, and 3. While doing a final verification before sending this email, I was surprised to find that the partitioned table ended up with two overlapping partitions.
>
> Here is a simple repro:
> ```
> evantest=# drop table t;
> DROP TABLE
> evantest=# CREATE TABLE t (i int) PARTITION BY RANGE(i);
> CREATE TABLE
> evantest=# CREATE TABLE p0a PARTITION OF t FOR VALUES FROM (0) TO (51);
> CREATE TABLE
> evantest=# CREATE TABLE p0b PARTITION OF t FOR VALUES FROM (51) TO (100);
> CREATE TABLE
> evantest=# \d+ t;
>                                       Partitioned table "public.t"
>  Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
>  i      | integer |           |          |         | plain   |             |              |
> Partition key: RANGE (i)
> Partitions:
>     p0a FOR VALUES FROM (0) TO (51)
>     p0b FOR VALUES FROM (51) TO (100)
>
> evantest=# ALTER TABLE t SPLIT PARTITION p0a INTO
> evantest-#   (PARTITION p0a FOR VALUES FROM (0) TO (53),
> evantest(#    PARTITION pdef DEFAULT);
> ALTER TABLE
> evantest=#
> evantest=#
> evantest=# \d+ t;
>                                       Partitioned table "public.t"
>  Column |  Type   | Collation | Nullable | Default | Storage | Compression | Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
>  i      | integer |           |          |         | plain   |             |              |
> Partition key: RANGE (i)
> Partitions:
>     p0a FOR VALUES FROM (0) TO (53)
>     p0b FOR VALUES FROM (51) TO (100)
>     pdef DEFAULT
> ```
>
> As shown above, p0a and p0b now overlap. I think this is a real bug.


that's 100% real issue because of planner partition pruning

```
reshke=# CREATE TABLE t (i int) PARTITION BY RANGE(i);
CREATE TABLE
reshke=#  CREATE TABLE p0a PARTITION OF t FOR VALUES FROM (0) TO (51);
CREATE TABLE
reshke=# insert into t values (50);
INSERT 0 1
reshke=# CREATE TABLE p0b PARTITION OF t FOR VALUES FROM (51) TO (100);
CREATE TABLE
reshke=# insert into t values (51);
INSERT 0 1
reshke=# insert into t values (51);
INSERT 0 1
reshke=# insert into t values (51);
INSERT 0 1
reshke=# ALTER TABLE t SPLIT PARTITION p0a INTO(PARTITION p0a FOR
VALUES FROM (0) TO (53),PARTITION pdef DEFAULT);
ALTER TABLE
reshke=# table t;
 i
----
 50
 51
 51
 51
(4 rows)
reshke=# select * from  t where i = 51;
 i
---
(0 rows)

reshke=# set enable_partition_pruning to off;
SET
reshke=# select * from  t where i = 51;
 i
----
 51
 51
 51
(3 rows)

reshke=#
``


-- 
Best regards,
Kirill Reshke