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.96) (envelope-from ) id 1wN5z0-000Zg9-39 for pgsql-hackers@arkaria.postgresql.org; Wed, 13 May 2026 09:34:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wN5yz-008Icr-2f for pgsql-hackers@arkaria.postgresql.org; Wed, 13 May 2026 09:34:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wN5x3-008Egr-2V for pgsql-hackers@lists.postgresql.org; Wed, 13 May 2026 09:32:25 +0000 Received: from mail-yx1-xb12d.google.com ([2607:f8b0:4864:20::b12d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wN5x1-00000000NVt-22u0 for pgsql-hackers@postgresql.org; Wed, 13 May 2026 09:32:25 +0000 Received: by mail-yx1-xb12d.google.com with SMTP id 956f58d0204a3-65890a6ca20so6764695d50.0 for ; Wed, 13 May 2026 02:32:23 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1778664742; cv=none; d=google.com; s=arc-20240605; b=TkNTudcxxMiVaLw4Z2d+quMB0ChVlKy6h2HcezRTUw95kfOZCFTD4TWuwkLUEVON/a b+A7QVnf0DOW8Zq97ZaXRw+/I1yFDCJF2m8B886ErQRypg7AynYd2YaWi5Jm1zyNu6Yu HpNvpChEuRRmrosJ5vIoB0TM3K6ylNzdu1eL9066b9rPOEiz0SDX5iCm7m2VmnyEesJI Y4XITmddnQTXDmBalMgnw5LzErVZtDQs4pYEvChRJLNtWaUE09lTIx7uclkbvzlAWMZl /f6uVB99MmEKRfNlwZE0PnCG+ws0ZBu3V1xEkyN6qsoSonPQZCBQfsxCesun6tS8ZK6+ ZRDw== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:dkim-signature; bh=g2+05doisnvuZztmM6MWpK3xjup+gqm6LS0cjbetxCI=; fh=bV5v61GFqmESb8xqDnnOMIK90tKNYuH/fLOCNjfwJHA=; b=IlRAfKra6lFTpcLnWGV1OCB+Sy4A3aBNfAVF3KVdT0rHtxC5dLRgT25fhrW/5Y7q8r 7WAH5XNqJVsiq4xg33Hcl1P7WyXD+jEJ7bF+xgcOzjuI4mIhVkp+f/5YrFQKi2ru0Vub BLcIAhlhyYLREYIjlBGkWPTC8lvDuzRF23syHA28bePLv+rAOj8FOQLjsQGw2qyG0Ahu oeI/3BSOpLd+x2yJWlo0XmWLIItn5KQEOCkFOwbRLL9t7w9tBJy0SIsGUE+Xruy+Vczl QgvaYNb6ETR9ujdBxqg0pOA4lNnRjzWontwlh+DYDD9IL1UWtvigWfjC3oP0WVdHGSbc LljA==; darn=postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1778664742; x=1779269542; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=g2+05doisnvuZztmM6MWpK3xjup+gqm6LS0cjbetxCI=; b=M/PSUIGKRkV6/FoKyIp9LpZDFKTpGXxPKTI714Clz6wBRRqrTEuKMcvLbHa6AGBDcR t3+MXzXnD+QDA7mv/zB+tun+8+1RIH7ick9Gu0t1y3BhlQsYbAzSiFUoNaZS6nlI1aoL DCLYcBW0gOEaU+QoNiLH3xvpXpcEBANRZLzjY7IBIM5JcCGZbVV0E8As/WlTf08QAmgS g0duyx+phcLNFfVJNojYsDijTyJKEpj211MJEWfbYLuqwhMN0TjHUeN7FqjF315DMIB7 eeJpO27seOrzuL7g4TTs85mHMBg6WveIwUNy8JGYoRov++vefOJMUv9T1BM0eMOwHGCb ddUw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1778664742; x=1779269542; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=g2+05doisnvuZztmM6MWpK3xjup+gqm6LS0cjbetxCI=; b=Hn6aosT7Le8UkXaAeWYFGhprpTtYW0eArhe4fBXLTfgjQNIvUynImHVWS18qqh+BKk cPWpamA0P1+MYlH/3ectyTAtlG66LPm6l6AWVhAE6o1EJqAaM4SNStOJqntMirpKrmBa 1Ixt/rbx+TLgvJLiPMXhwAuoejvskcgbUsYThIJjWAx7xv4ZGuS5ZZHQMp98Lh2DeMkI 35AsyKBU4z8bt9jCNDREIYy/gtMVqDQ99apyU0XJGIN/hv+8XsZovEOPKpdATDPGLxi9 H+MZ4i6TvvjOfIGH+ddECMnjvDJ69oIStAEiKsYY1K821xlCywUZ4mkMl7vw6x4ve/rE CKiw== X-Forwarded-Encrypted: i=1; AFNElJ8jMwZI5nT2ZAp6SYzOCoTrTD5ab6xO3BbOlbIAeF4AVNlBb8ZgZa+9dHmcCpc7ot5Ll6ySOdnw2v1+hqpu@postgresql.org X-Gm-Message-State: AOJu0YyvBnHGaKxb71Nv5hMgCZmfFyePB+auLqoXGZ891jtGm6W4WqZc nChWxOPDbuPkeerOnuctbeqnsZePOA7N2jL8v6Xk2MLYDok3T4vppk4OhAB9MDnWw/K75xO0jm1 OJ3M5CASPK4YHrylBWfWrvbypVv8SVzU= X-Gm-Gg: Acq92OGzvYkbu1YCxcV2v3pgrer248JoXi3Kj8woj4DOe/569pyhEYiWnSLrr3hD21u +M6gUf2NQB6WHlFZ+15mnSsAWBVoQaHXRxLcdQk22/lteiRzig26tXUId0Mp5Agdnx/0FgIgyab xaiJv6nXcOdv2q0+4Yme+qCsL5e0F6X4pTaTp3nwl9mBdOz6BLfebY43W+LmVU5WMsvxNpU1RkS HI4IuCFAI9eWsiSgcXs6zUwFbIC63uV6JbMXqAyzIZHzvRN8e61llIr6KoJWF7rso5tmXxvbw8p XX64Eg== X-Received: by 2002:a53:d054:0:10b0:654:1261:8b4a with SMTP id 956f58d0204a3-65df80e3456mr1445870d50.3.1778664742406; Wed, 13 May 2026 02:32:22 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Zhenwei Shang Date: Wed, 13 May 2026 17:32:09 +0800 X-Gm-Features: AVHnY4K-XpOzi_dgMMNOUlAa8WGKw87HOsI9rJWbJvCm82pgBH9amzE444nmn8U Message-ID: Subject: Re: Fix SPLIT PARTITION bound-overlap bug and other improvements To: Kirill Reshke Cc: Chao Li , PostgreSQL-development , Dmitry Koval , Alexander Korotkov Content-Type: multipart/alternative; boundary="000000000000254dd90651afa8a2" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000254dd90651afa8a2 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Kirill Reshke =E4=BA=8E2026=E5=B9=B45=E6=9C=8813= =E6=97=A5=E5=91=A8=E4=B8=89 13:08=E5=86=99=E9=81=93=EF=BC=9A > On Wed, 13 May 2026 at 09:39, Chao Li 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 overlappin= g > partitions. > > > > Here is a simple repro: > > ``` > > evantest=3D# drop table t; > > DROP TABLE > > evantest=3D# CREATE TABLE t (i int) PARTITION BY RANGE(i); > > CREATE TABLE > > evantest=3D# CREATE TABLE p0a PARTITION OF t FOR VALUES FROM (0) TO (51= ); > > CREATE TABLE > > evantest=3D# CREATE TABLE p0b PARTITION OF t FOR VALUES FROM (51) TO (1= 00); > > CREATE TABLE > > evantest=3D# \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=3D# ALTER TABLE t SPLIT PARTITION p0a INTO > > evantest-# (PARTITION p0a FOR VALUES FROM (0) TO (53), > > evantest(# PARTITION pdef DEFAULT); > > ALTER TABLE > > evantest=3D# > > evantest=3D# > > evantest=3D# \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=3D# CREATE TABLE t (i int) PARTITION BY RANGE(i); > CREATE TABLE > reshke=3D# CREATE TABLE p0a PARTITION OF t FOR VALUES FROM (0) TO (51); > CREATE TABLE > reshke=3D# insert into t values (50); > INSERT 0 1 > reshke=3D# CREATE TABLE p0b PARTITION OF t FOR VALUES FROM (51) TO (100); > CREATE TABLE > reshke=3D# insert into t values (51); > INSERT 0 1 > reshke=3D# insert into t values (51); > INSERT 0 1 > reshke=3D# insert into t values (51); > INSERT 0 1 > reshke=3D# ALTER TABLE t SPLIT PARTITION p0a INTO(PARTITION p0a FOR > VALUES FROM (0) TO (53),PARTITION pdef DEFAULT); > ALTER TABLE > reshke=3D# table t; > i > ---- > 50 > 51 > 51 > 51 > (4 rows) > reshke=3D# select * from t where i =3D 51; > i > --- > (0 rows) > > reshke=3D# set enable_partition_pruning to off; > SET > reshke=3D# select * from t where i =3D 51; > i > ---- > 51 > 51 > 51 > (3 rows) > > reshke=3D# > `` > > > > -- > Best regards, > Kirill Reshke > > > I agree this is a bug. I applied the patch locally and confirmed the bug is fixed with the patch. Overall, the patch looks good to me. Regards, Zhenwei Shang --000000000000254dd90651afa8a2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


Kirill Reshke &= lt;reshkekirill@gmail.com>= =E4=BA=8E2026=E5=B9=B45=E6=9C=8813=E6=97=A5=E5=91=A8=E4=B8=89 13:08=E5=86= =99=E9=81=93=EF=BC=9A
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=3D# drop table t;
> DROP TABLE
> evantest=3D# CREATE TABLE t (i int) PARTITION BY RANGE(i);
> CREATE TABLE
> evantest=3D# CREATE TABLE p0a PARTITION OF t FOR VALUES FROM (0) TO (5= 1);
> CREATE TABLE
> evantest=3D# CREATE TABLE p0b PARTITION OF t FOR VALUES FROM (51) TO (= 100);
> CREATE TABLE
> evantest=3D# \d+ t;
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Partit= ioned table "public.t"
>=C2=A0 Column |=C2=A0 Type=C2=A0 =C2=A0| Collation | Nullable | Default= | Storage | Compression | Stats target | Description
> --------+---------+-----------+----------+---------+---------+--------= -----+--------------+-------------
>=C2=A0 i=C2=A0 =C2=A0 =C2=A0 | integer |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0| plain=C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
> Partition key: RANGE (i)
> Partitions:
>=C2=A0 =C2=A0 =C2=A0p0a FOR VALUES FROM (0) TO (51)
>=C2=A0 =C2=A0 =C2=A0p0b FOR VALUES FROM (51) TO (100)
>
> evantest=3D# ALTER TABLE t SPLIT PARTITION p0a INTO
> evantest-#=C2=A0 =C2=A0(PARTITION p0a FOR VALUES FROM (0) TO (53),
> evantest(#=C2=A0 =C2=A0 PARTITION pdef DEFAULT);
> ALTER TABLE
> evantest=3D#
> evantest=3D#
> evantest=3D# \d+ t;
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Partit= ioned table "public.t"
>=C2=A0 Column |=C2=A0 Type=C2=A0 =C2=A0| Collation | Nullable | Default= | Storage | Compression | Stats target | Description
> --------+---------+-----------+----------+---------+---------+--------= -----+--------------+-------------
>=C2=A0 i=C2=A0 =C2=A0 =C2=A0 | integer |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0| plain=C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
> Partition key: RANGE (i)
> Partitions:
>=C2=A0 =C2=A0 =C2=A0p0a FOR VALUES FROM (0) TO (53)
>=C2=A0 =C2=A0 =C2=A0p0b FOR VALUES FROM (51) TO (100)
>=C2=A0 =C2=A0 =C2=A0pdef 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=3D# CREATE TABLE t (i int) PARTITION BY RANGE(i);
CREATE TABLE
reshke=3D#=C2=A0 CREATE TABLE p0a PARTITION OF t FOR VALUES FROM (0) TO (51= );
CREATE TABLE
reshke=3D# insert into t values (50);
INSERT 0 1
reshke=3D# CREATE TABLE p0b PARTITION OF t FOR VALUES FROM (51) TO (100); CREATE TABLE
reshke=3D# insert into t values (51);
INSERT 0 1
reshke=3D# insert into t values (51);
INSERT 0 1
reshke=3D# insert into t values (51);
INSERT 0 1
reshke=3D# ALTER TABLE t SPLIT PARTITION p0a INTO(PARTITION p0a FOR
VALUES FROM (0) TO (53),PARTITION pdef DEFAULT);
ALTER TABLE
reshke=3D# table t;
=C2=A0i
----
=C2=A050
=C2=A051
=C2=A051
=C2=A051
(4 rows)
reshke=3D# select * from=C2=A0 t where i =3D 51;
=C2=A0i
---
(0 rows)

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

reshke=3D#
``



--
Best regards,
Kirill Reshke


I agree this is a bug. I applied the patch locally and con= firmed the bug is fixed with the patch.

Overall, the patch look= s good to me.

Regards,
Zhenwei Shang
--000000000000254dd90651afa8a2--