public inbox for [email protected]
help / color / mirror / Atom feedFrom: Kirill Reshke <[email protected]>
To: Chao Li <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: Dmitry Koval <[email protected]>
Cc: Alexander Korotkov <[email protected]>
Subject: Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
Date: Wed, 13 May 2026 10:07:56 +0500
Message-ID: <CALdSSPhquxjxisujDcfLqfoY2=SnUAHhsqgM9PvO0uO3uZC9jQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
On Wed, 13 May 2026 at 09:39, Chao Li <[email protected]> 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
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 using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
In-Reply-To: <CALdSSPhquxjxisujDcfLqfoY2=SnUAHhsqgM9PvO0uO3uZC9jQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox