public inbox for [email protected]
help / color / mirror / Atom feedFix SPLIT PARTITION bound-overlap bug and other improvements
18+ messages / 5 participants
[nested] [flat]
* Fix SPLIT PARTITION bound-overlap bug and other improvements
@ 2026-05-13 04:38 Chao Li <[email protected]>
2026-05-13 05:07 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Kirill Reshke <[email protected]>
2026-05-13 20:47 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Dmitry Koval <[email protected]>
0 siblings, 2 replies; 18+ messages in thread
From: Chao Li @ 2026-05-13 04:38 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Dmitry Koval <[email protected]>; Alexander Korotkov <[email protected]>
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.
The problem seems to be in check_partition_bounds_for_split_range(). If the only non-default replacement partition is both first and last, the function checks only the lower bound because it uses if (first) ... else .... It never checks the upper bound in that case.
1. The documentation about splitting with a DEFAULT partition is a bit unclear
Since SPLIT PARTITION allows one of the new partitions to be specified as DEFAULT, I wondered whether that new DEFAULT partition means the remaining part of the split partition's bound, or the partitioned table's global DEFAULT partition.
The current documentation says:
```
<para>
This form splits a single partition of the target table into new
partitions. Hash-partitioned target table is not supported.
Only a simple, non-partitioned partition can be split.
If the split partition is the <literal>DEFAULT</literal> partition,
one of the new partitions must be <literal>DEFAULT</literal>.
If the partitioned table does not have a <literal>DEFAULT</literal>
partition, a <literal>DEFAULT</literal> partition can be defined as one
of the new partitions.
</para>
<para>
The bounds of new partitions should not overlap with those of new or
existing partitions (except <replaceable class="parameter">partition_name</replaceable>).
The combined bounds of new partitions <literal>
<replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable>[, ...]
</literal> should be equal to the bounds of the split partition
<replaceable class="parameter">partition_name</replaceable>.
One of the new partitions can have the same name as the split partition
<replaceable class="parameter">partition_name</replaceable>
(this is suitable in case of splitting the <literal>DEFAULT</literal>
partition: after the split, the <literal>DEFAULT</literal> partition
remains with the same name, but its partition bound changes).
</para>
```
From the first paragraph, it seems that the new DEFAULT partition is a table-level default partition. However, the second paragraph says that the combined bounds of the new partitions should be equal to the bounds of the split partition, which can make it look as if the new DEFAULT partition only covers the remaining part of the split partition's bound.
My tests show that the new DEFAULT partition is the partitioned table's global DEFAULT partition. So I think the documentation can be improved to make that clearer.
2. I found this hint message confusing:
```
evantest=# ALTER TABLE t SPLIT PARTITION p0a INTO (PARTITION p0a1 FOR VALUES FROM (0) TO (5), PARTITION p0a2 FOR VALUES FROM (6) to (51), PARTITION pdef DEFAULT);
ERROR: upper bound of partition "p0a2" is greater than upper bound of split partition "p0a"
LINE 1: ...0) TO (5), PARTITION p0a2 FOR VALUES FROM (6) to (51), PARTI...
^
HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
```
In this command, one of the new explicit partition bounds exceeds the original partition bound, but the command also specifies a DEFAULT partition. In this case, the combined explicit bounds do not need to exactly match the original partition bound, they only need to stay within it. So the hint is not quite accurate for this case.
3. SPLIT PARTITION currently provides another way to add a DEFAULT partition:
```
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 (50)
p0b FOR VALUES FROM (51) TO (100)
p200 FOR VALUES FROM (200) TO (300)
evantest=# ALTER TABLE t SPLIT PARTITION p0a INTO (PARTITION p0a FOR VALUES FROM (0) TO (50), PARTITION pdef DEFAULT);
ALTER TABLE
```
Here, the new p0a partition has the same bound as the original p0a partition, so no real split happens. The command effectively only adds a new DEFAULT partition. However, it still goes through the full split-partition path: creating a new partition, moving data, attaching the new partition, and dropping the old partition.
Initially, I considered adding a fast path for this case so that it would only add the new DEFAULT partition. But after thinking about it more, I think it is better to reject this degenerate form instead. We already has direct ways to add a DEFAULT partition:
```
CREATE TABLE p PARTITION OF t DEFAULT;
or
CREATE TABLE p;
ALTER TABLE t ATTACH PARTITION p DEFAULT;
```
So I do not think SPLIT PARTITION needs to become another syntax for adding a DEFAULT partition when no actual split is performed. Accepting this form could also raise another question later: if this is allowed, why does the user have to repeat the original bound at all? Why not allow something like this?
```
ALTER TABLE t SPLIT PARTITION p0a INTO (PARTITION pdef DEFAULT);
```
That seems like an awkward direction.
The attached patch tries to address all these issues.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v1-0001-Fix-SPLIT-PARTITION-validation-with-DEFAULT.patch (12.9K, 2-v1-0001-Fix-SPLIT-PARTITION-validation-with-DEFAULT.patch)
download | inline diff:
From 4fc92276d96195c38aec4477bd14c00a514173df Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Wed, 13 May 2026 11:17:19 +0800
Subject: [PATCH v1] Fix SPLIT PARTITION validation with DEFAULT
When ALTER TABLE ... SPLIT PARTITION specified a DEFAULT partition, range
bound validation checked the lower bound for the first explicit partition
and the upper bound for non-first explicit partitions. As a result, when
there was only one explicit non-DEFAULT partition, its upper bound was not
checked. This could allow the new partition to extend beyond the split
partition's bound and overlap another existing partition.
Fix this by checking the upper bound whenever the explicit partition is
the last one, rather than only when it is not the first one.
While here, reject the degenerate form where a non-DEFAULT partition is
split into one non-DEFAULT partition with exactly the same bound plus a
DEFAULT partition. That form performs no real split and merely adds a
DEFAULT partition through the split-partition path, for which existing
commands should be used instead.
Also clarify the documentation for SPLIT PARTITION with DEFAULT, and
adjust the hint for explicit bounds that extend outside the split
partition when a DEFAULT partition is specified.
Author: Chao Li <[email protected]>
---
doc/src/sgml/ref/alter_table.sgml | 31 ++++----
src/backend/partitioning/partbounds.c | 78 +++++++++++++++++--
src/test/regress/expected/partition_split.out | 43 ++++++++++
src/test/regress/sql/partition_split.sql | 37 +++++++++
4 files changed, 171 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1f9a456fd33..97a6d331776 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1293,28 +1293,33 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form splits a single partition of the target table into new
- partitions. Hash-partitioned target table is not supported.
- Only a simple, non-partitioned partition can be split.
- If the split partition is the <literal>DEFAULT</literal> partition,
- one of the new partitions must be <literal>DEFAULT</literal>.
- If the partitioned table does not have a <literal>DEFAULT</literal>
+ partitions. Hash-partitioned target table is not supported.
+ Only a simple, non-partitioned partition can be split. If the
+ split partition is the <literal>DEFAULT</literal> partition, one
+ of the new partitions must be <literal>DEFAULT</literal>. If the
+ partitioned table does not have a <literal>DEFAULT</literal>
partition, a <literal>DEFAULT</literal> partition can be defined as one
of the new partitions.
</para>
<para>
- The bounds of new partitions should not overlap with those of new or
- existing partitions (except <replaceable class="parameter">partition_name</replaceable>).
- The combined bounds of new partitions <literal>
+ The bounds of new non-<literal>DEFAULT</literal> partitions must not
+ overlap with those of new or existing partitions, except
+ <replaceable class="parameter">partition_name</replaceable>, and
+ must not extend outside the bounds of the split partition
+ <replaceable class="parameter">partition_name</replaceable>.
+ If no new <literal>DEFAULT</literal> partition is specified, the
+ combined bounds of the new partitions
+ <literal>
<replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable>[, ...]
- </literal> should be equal to the bounds of the split partition
+ </literal> must exactly match the bounds of the split partition
<replaceable class="parameter">partition_name</replaceable>.
One of the new partitions can have the same name as the split partition
- <replaceable class="parameter">partition_name</replaceable>
- (this is suitable in case of splitting the <literal>DEFAULT</literal>
- partition: after the split, the <literal>DEFAULT</literal> partition
- remains with the same name, but its partition bound changes).
+ <replaceable class="parameter">partition_name</replaceable>.
+ This is useful when splitting the <literal>DEFAULT</literal> partition,
+ so that after the split, the <literal>DEFAULT</literal> partition
+ keeps the same name but its partition bound changes.
</para>
<para>
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index 9b4277a4987..6ca6d8b719f 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -36,6 +36,7 @@
#include "utils/datum.h"
#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
+#include "utils/memutils.h"
#include "utils/partcache.h"
#include "utils/ruleutils.h"
#include "utils/snapmgr.h"
@@ -5415,11 +5416,11 @@ check_partition_bounds_for_split_range(Relation parent,
errmsg("lower bound of partition \"%s\" is less than lower bound of split partition \"%s\"",
relname,
get_rel_name(splitPartOid)),
- errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition.",
- "ALTER TABLE ... SPLIT PARTITION"),
+ errhint("Explicit partition bounds must be contained within the bounds of the split partition when a DEFAULT partition is specified."),
parser_errposition(pstate, exprLocation((Node *) datum)));
}
- else
+
+ if (last)
{
PartitionRangeBound *split_upper;
@@ -5457,8 +5458,7 @@ check_partition_bounds_for_split_range(Relation parent,
errmsg("upper bound of partition \"%s\" is greater than upper bound of split partition \"%s\"",
relname,
get_rel_name(splitPartOid)),
- errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition.",
- "ALTER TABLE ... SPLIT PARTITION"),
+ errhint("Explicit partition bounds must be contained within the bounds of the split partition when a DEFAULT partition is specified."),
parser_errposition(pstate, exprLocation((Node *) datum)));
}
}
@@ -5701,6 +5701,70 @@ check_parent_values_in_new_partitions(Relation parent,
}
}
+/*
+ * check_split_partition_not_same_bound
+ *
+ * Reject splitting a non-DEFAULT partition into one non-DEFAULT partition
+ * with the original bound plus a DEFAULT partition. That form does not
+ * perform a real split; it merely adds a DEFAULT partition to the parent
+ * table through the split-partition path. Users should use
+ * CREATE TABLE ... PARTITION OF ... DEFAULT or ALTER TABLE ... ATTACH
+ * PARTITION ... DEFAULT for that.
+ */
+static void
+check_split_partition_not_same_bound(Relation parent,
+ Oid splitPartOid,
+ SinglePartitionSpec **parts,
+ int nparts,
+ ParseState *pstate)
+{
+ PartitionKey key = RelationGetPartitionKey(parent);
+ PartitionBoundSpec *split_spec;
+ PartitionBoundSpec *new_specs[1];
+ PartitionBoundSpec *old_specs[1];
+ PartitionBoundInfo new_boundinfo;
+ PartitionBoundInfo old_boundinfo;
+ int *new_mapping;
+ int *old_mapping;
+ MemoryContext old_cxt;
+ MemoryContext tmp_cxt;
+ bool same_bound;
+
+ if (nparts != 1)
+ return;
+
+ tmp_cxt = AllocSetContextCreate(CurrentMemoryContext,
+ "split partition bound comparison",
+ ALLOCSET_SMALL_SIZES);
+ old_cxt = MemoryContextSwitchTo(tmp_cxt);
+
+ split_spec = get_partition_bound_spec(splitPartOid);
+
+ new_specs[0] = parts[0]->bound;
+ new_boundinfo = partition_bounds_create(new_specs, 1, key, &new_mapping);
+
+ old_specs[0] = split_spec;
+ old_boundinfo = partition_bounds_create(old_specs, 1, key, &old_mapping);
+
+ same_bound = partition_bounds_equal(key->partnatts, key->parttyplen,
+ key->parttypbyval,
+ new_boundinfo, old_boundinfo);
+
+ MemoryContextSwitchTo(old_cxt);
+ MemoryContextDelete(tmp_cxt);
+
+ if (!same_bound)
+ return;
+
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("cannot split partition \"%s\" only to add a DEFAULT partition",
+ get_rel_name(splitPartOid)),
+ errdetail("The non-DEFAULT partition would keep the same partition bound."),
+ errhint("Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition."),
+ parser_errposition(pstate, parts[0]->name->location));
+}
+
/*
* check_partitions_for_split
*
@@ -5775,6 +5839,10 @@ check_partitions_for_split(Relation parent,
Assert(nparts == list_length(partlist) - 1);
}
+ if (!isSplitPartDefault && createDefaultPart)
+ check_split_partition_not_same_bound(parent, splitPartOid, new_parts,
+ nparts, pstate);
+
if (strategy == PARTITION_STRATEGY_RANGE)
{
PartitionRangeBound **lower_bounds;
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 961b37953c8..5d7380e28bb 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1188,6 +1188,49 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
--
+-- Test that SPLIT PARTITION rejects the degenerate case where the only
+-- non-DEFAULT replacement partition keeps the original bound and the command
+-- merely adds a DEFAULT partition.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_50 PARTITION OF t FOR VALUES FROM (0) TO (50);
+INSERT INTO t VALUES (1);
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_50 INTO
+ (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ PARTITION tp_default DEFAULT);
+ERROR: cannot split partition "tp_0_50" only to add a DEFAULT partition
+LINE 2: (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ ^
+DETAIL: The non-DEFAULT partition would keep the same partition bound.
+HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_50 INTO
+ (PARTITION tp_0_5 FOR VALUES FROM (0) TO (5),
+ PARTITION tp_6_51 FOR VALUES FROM (6) TO (51),
+ PARTITION tp_default DEFAULT);
+ERROR: upper bound of partition "tp_6_51" is greater than upper bound of split partition "tp_0_50"
+LINE 3: PARTITION tp_6_51 FOR VALUES FROM (6) TO (51),
+ ^
+HINT: Explicit partition bounds must be contained within the bounds of the split partition when a DEFAULT partition is specified.
+DROP TABLE t;
+--
+-- Test that the explicit partition bound cannot extend outside the split
+-- partition's bound when a DEFAULT partition is specified.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_51 PARTITION OF t FOR VALUES FROM (0) TO (51);
+CREATE TABLE tp_51_100 PARTITION OF t FOR VALUES FROM (51) TO (100);
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_51 INTO
+ (PARTITION tp_0_51 FOR VALUES FROM (0) TO (53),
+ PARTITION tp_default DEFAULT);
+ERROR: upper bound of partition "tp_0_51" is greater than upper bound of split partition "tp_0_51"
+LINE 2: (PARTITION tp_0_51 FOR VALUES FROM (0) TO (53),
+ ^
+HINT: Explicit partition bounds must be contained within the bounds of the split partition when a DEFAULT partition is specified.
+DROP TABLE t;
+--
-- Try to SPLIT partition of another table.
--
CREATE TABLE t1(i int, t text) PARTITION BY LIST (t);
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index a110fc87867..a925dacd205 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -834,6 +834,43 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
+--
+-- Test that SPLIT PARTITION rejects the degenerate case where the only
+-- non-DEFAULT replacement partition keeps the original bound and the command
+-- merely adds a DEFAULT partition.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_50 PARTITION OF t FOR VALUES FROM (0) TO (50);
+INSERT INTO t VALUES (1);
+
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_50 INTO
+ (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ PARTITION tp_default DEFAULT);
+
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_50 INTO
+ (PARTITION tp_0_5 FOR VALUES FROM (0) TO (5),
+ PARTITION tp_6_51 FOR VALUES FROM (6) TO (51),
+ PARTITION tp_default DEFAULT);
+
+DROP TABLE t;
+
+--
+-- Test that the explicit partition bound cannot extend outside the split
+-- partition's bound when a DEFAULT partition is specified.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_51 PARTITION OF t FOR VALUES FROM (0) TO (51);
+CREATE TABLE tp_51_100 PARTITION OF t FOR VALUES FROM (51) TO (100);
+
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_51 INTO
+ (PARTITION tp_0_51 FOR VALUES FROM (0) TO (53),
+ PARTITION tp_default DEFAULT);
+
+DROP TABLE t;
+
--
-- Try to SPLIT partition of another table.
--
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
@ 2026-05-13 05:07 ` Kirill Reshke <[email protected]>
2026-05-13 09:32 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Zhenwei Shang <[email protected]>
1 sibling, 1 reply; 18+ messages in thread
From: Kirill Reshke @ 2026-05-13 05:07 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: pgsql-hackers; Dmitry Koval <[email protected]>; Alexander Korotkov <[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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-13 05:07 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Kirill Reshke <[email protected]>
@ 2026-05-13 09:32 ` Zhenwei Shang <[email protected]>
0 siblings, 0 replies; 18+ messages in thread
From: Zhenwei Shang @ 2026-05-13 09:32 UTC (permalink / raw)
To: Kirill Reshke <[email protected]>; +Cc: Chao Li <[email protected]>; pgsql-hackers; Dmitry Koval <[email protected]>; Alexander Korotkov <[email protected]>
Kirill Reshke <[email protected]> 于2026年5月13日周三 13:08写道：
> 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
>
>
> 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
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
@ 2026-05-13 20:47 ` Dmitry Koval <[email protected]>
2026-05-14 06:58 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
1 sibling, 1 reply; 18+ messages in thread
From: Dmitry Koval @ 2026-05-13 20:47 UTC (permalink / raw)
To: Chao Li <[email protected]>; pgsql-hackers; +Cc: Alexander Korotkov <[email protected]>
Hi, Chao Li!
Thank you for the bug report, test script, and fix!
>> 0. A bound-overlap bug
I think this fix should be applied without much discussion:
------------------------------------------------------------------------
diff --git a/src/backend/partitioning/partbounds.c
b/src/backend/partitioning/partbounds.c
index 9b4277a4987..8b8f90569fe 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -5419,7 +5419,8 @@ check_partition_bounds_for_split_range(Relation
parent,
"ALTER
TABLE ... SPLIT PARTITION"),
parser_errposition(pstate, exprLocation((Node *) datum)));
}
- else
+
+ if (last)
{
PartitionRangeBound *split_upper;
------------------------------------------------------------------------
>> 1. The documentation about splitting with a DEFAULT partition is a
bit unclear
>> ...
>> 2. I found this hint message confusing:
>> ...
Unfortunately, I cannot comment on these points; it would be good to get
the opinion of people who know English well.
>> 3. SPLIT PARTITION currently provides another way to add a DEFAULT
partition:
>> ...
Agreed, this is another way to add a DEFAULT partition. But I'm not sure
that this way should be disabled (using the special function
check_split_partition_not_same_bound)...
Maybe it's better to keep it "as is"?
--
With best regards,
Dmitry Koval
Postgres Professional: http://postgrespro.com
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-13 20:47 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Dmitry Koval <[email protected]>
@ 2026-05-14 06:58 ` Chao Li <[email protected]>
2026-05-18 09:16 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Chao Li @ 2026-05-14 06:58 UTC (permalink / raw)
To: Dmitry Koval <[email protected]>; +Cc: pgsql-hackers; Alexander Korotkov <[email protected]>
> On May 14, 2026, at 04:47, Dmitry Koval <[email protected]> wrote:
>
> Hi, Chao Li!
>
> Thank you for the bug report, test script, and fix!
>
> >> 0. A bound-overlap bug
>
> I think this fix should be applied without much discussion:
> ------------------------------------------------------------------------
> diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
> index 9b4277a4987..8b8f90569fe 100644
> --- a/src/backend/partitioning/partbounds.c
> +++ b/src/backend/partitioning/partbounds.c
> @@ -5419,7 +5419,8 @@ check_partition_bounds_for_split_range(Relation parent,
> "ALTER TABLE ... SPLIT PARTITION"),
> parser_errposition(pstate, exprLocation((Node *) datum)));
> }
> - else
> +
> + if (last)
> {
> PartitionRangeBound *split_upper;
> ------------------------------------------------------------------------
Thanks for your confirmation.
>
> >> 1. The documentation about splitting with a DEFAULT partition is a bit unclear
> >> ...
> >> 2. I found this hint message confusing:
> >> ...
>
> Unfortunately, I cannot comment on these points; it would be good to get the opinion of people who know English well.
I want to add one more point about these two changes.
There is a code comment saying that when a DEFAULT partition is specified, the new partition's lower bound may be greater than the original lower bound:
```
/*
* The lower bound of "spec" must equal the lower bound of the
* split partition. However, if one of the new partitions is
* DEFAULT, then it is ok for the new partition's lower bound to
* be greater than that of the split partition.
*/
```
This also indicates that the original hint message mentioning “exactly match" is wrong for the DEFAULT case.
>
>
> >> 3. SPLIT PARTITION currently provides another way to add a DEFAULT partition:
> >> ...
>
> Agreed, this is another way to add a DEFAULT partition. But I'm not sure that this way should be disabled (using the special function check_split_partition_not_same_bound)...
> Maybe it's better to keep it "as is"?
>
Yeah, this may be worth more discussion. But I think we should either reject this usage or add a fast path to avoid unnecessary creation of a new partition, data movement, etc. Otherwise, it feels more like a misuse of SPLIT PARTITION rather than a useful new alternative.
To make this patch easier to process, I split it into 4 commits:
0001 - Fixes the bound-overlap bug
0002 - Fix the incorrect HINT message for the DEFAULT case
0003 - Fix the incorrect description about combined bound in the SGML doc
0004 - Reject only-create-default-partition usage
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v2-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch (4.0K, 2-v2-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch)
download | inline diff:
From 0f296d8bef250e38eee74d58cbfc04e6c9c5ea7b Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Thu, 14 May 2026 13:49:43 +0800
Subject: [PATCH v2 1/4] Fix SPLIT PARTITION range bound validation with
DEFAULT
When splitting a range partition and defining a new DEFAULT partition, the
validation checked the lower bound of the first explicit partition and the
upper bound of explicit partitions only when they were not first. If there
was exactly one explicit non-DEFAULT partition, its upper bound was therefore
not checked.
This could allow the replacement partition to extend beyond the upper bound
of the partition being split, potentially overlapping another existing
partition.
Fix this by checking the upper bound whenever the explicit partition is the
last one. Add a regression test covering the single explicit partition plus
DEFAULT case.
Author: Chao Li <[email protected]>
Reviewed-by: Kirill Reshke <[email protected]>
Reviewed-by: Zhenwei Shang <[email protected]>
Reviewed-by: Dmitry Koval <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/partitioning/partbounds.c | 3 ++-
src/test/regress/expected/partition_split.out | 16 ++++++++++++++++
src/test/regress/sql/partition_split.sql | 15 +++++++++++++++
3 files changed, 33 insertions(+), 1 deletion(-)
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index a09beec34d8..73dea0375be 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -5419,7 +5419,8 @@ check_partition_bounds_for_split_range(Relation parent,
"ALTER TABLE ... SPLIT PARTITION"),
parser_errposition(pstate, exprLocation((Node *) datum)));
}
- else
+
+ if (last)
{
PartitionRangeBound *split_upper;
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 961b37953c8..a2ccbe5138b 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1188,6 +1188,22 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
--
+-- Test that the explicit partition bound cannot extend outside the split
+-- partition's bound when a DEFAULT partition is specified.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_51 PARTITION OF t FOR VALUES FROM (0) TO (51);
+CREATE TABLE tp_51_100 PARTITION OF t FOR VALUES FROM (51) TO (100);
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_51 INTO
+ (PARTITION tp_0_51 FOR VALUES FROM (0) TO (53),
+ PARTITION tp_default DEFAULT);
+ERROR: upper bound of partition "tp_0_51" is greater than upper bound of split partition "tp_0_51"
+LINE 2: (PARTITION tp_0_51 FOR VALUES FROM (0) TO (53),
+ ^
+HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+DROP TABLE t;
+--
-- Try to SPLIT partition of another table.
--
CREATE TABLE t1(i int, t text) PARTITION BY LIST (t);
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index a110fc87867..d9821c5e2a3 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -834,6 +834,21 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
+--
+-- Test that the explicit partition bound cannot extend outside the split
+-- partition's bound when a DEFAULT partition is specified.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_51 PARTITION OF t FOR VALUES FROM (0) TO (51);
+CREATE TABLE tp_51_100 PARTITION OF t FOR VALUES FROM (51) TO (100);
+
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_51 INTO
+ (PARTITION tp_0_51 FOR VALUES FROM (0) TO (53),
+ PARTITION tp_default DEFAULT);
+
+DROP TABLE t;
+
--
-- Try to SPLIT partition of another table.
--
--
2.50.1 (Apple Git-155)
[application/octet-stream] v2-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch (3.4K, 3-v2-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch)
download | inline diff:
From 1a30c86cdc81482793298602f89d03821207339c Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Thu, 14 May 2026 13:53:25 +0800
Subject: [PATCH v2 2/4] Fix SPLIT PARTITION hint for DEFAULT partition bounds
When ALTER TABLE ... SPLIT PARTITION specifies a DEFAULT partition, the
explicit partitions do not need to cover the split partition's bound
exactly. They may cover only part of it, with the DEFAULT partition
covering the remaining range.
However, the existing hint said that the combined bounds of the new
partitions must exactly match the bound of the split partition, which is
misleading for this case and inconsistent with the code comment.
Fix the hint to state the actual requirement: explicit partition bounds
must stay within the bounds of the split partition when a DEFAULT
partition is specified.
Author: Chao Li <[email protected]>
Reviewed-by:
Discussion: https://postgr.es/m/[email protected]
---
src/backend/partitioning/partbounds.c | 6 ++----
src/test/regress/expected/partition_split.out | 2 +-
2 files changed, 3 insertions(+), 5 deletions(-)
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index 73dea0375be..19589dc687f 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -5415,8 +5415,7 @@ check_partition_bounds_for_split_range(Relation parent,
errmsg("lower bound of partition \"%s\" is less than lower bound of split partition \"%s\"",
relname,
get_rel_name(splitPartOid)),
- errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition.",
- "ALTER TABLE ... SPLIT PARTITION"),
+ errhint("Explicit partition bounds must be contained within the bounds of the split partition when a DEFAULT partition is specified."),
parser_errposition(pstate, exprLocation((Node *) datum)));
}
@@ -5458,8 +5457,7 @@ check_partition_bounds_for_split_range(Relation parent,
errmsg("upper bound of partition \"%s\" is greater than upper bound of split partition \"%s\"",
relname,
get_rel_name(splitPartOid)),
- errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition.",
- "ALTER TABLE ... SPLIT PARTITION"),
+ errhint("Explicit partition bounds must be contained within the bounds of the split partition when a DEFAULT partition is specified."),
parser_errposition(pstate, exprLocation((Node *) datum)));
}
}
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index a2ccbe5138b..d4f536c5e00 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1201,7 +1201,7 @@ ALTER TABLE t SPLIT PARTITION tp_0_51 INTO
ERROR: upper bound of partition "tp_0_51" is greater than upper bound of split partition "tp_0_51"
LINE 2: (PARTITION tp_0_51 FOR VALUES FROM (0) TO (53),
^
-HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+HINT: Explicit partition bounds must be contained within the bounds of the split partition when a DEFAULT partition is specified.
DROP TABLE t;
--
-- Try to SPLIT partition of another table.
--
2.50.1 (Apple Git-155)
[application/octet-stream] v2-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch (3.8K, 4-v2-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch)
download | inline diff:
From d2e41d07334bca65820d7840c68bfafac2eac6fd Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Thu, 14 May 2026 13:51:19 +0800
Subject: [PATCH v2 3/4] Clarify SPLIT PARTITION bound requirements in docs
The documentation said that the bounds of new partitions should not
overlap and that their combined bounds should equal the bounds of the
split partition. That is misleading when a new DEFAULT partition is
specified, because the explicit partitions may cover only part of the
split partition while the DEFAULT partition covers the rest.
Clarify that new non-DEFAULT partition bounds must not overlap with
other new or existing partitions and must be contained within the bounds
of the split partition. Also state that the combined bounds must exactly
match the split partition only when no new DEFAULT partition is specified.
While here, improve nearby wording about hash-partitioned target tables
and splitting a DEFAULT partition with the same partition name.
Author: Chao Li <[email protected]>
Reviewed-by:
Discussion: https://postgr.es/m/[email protected]
---
doc/src/sgml/ref/alter_table.sgml | 23 ++++++++++++++---------
1 file changed, 14 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1f9a456fd33..dec34337d1a 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1293,7 +1293,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form splits a single partition of the target table into new
- partitions. Hash-partitioned target table is not supported.
+ partitions. Hash-partitioned target tables are not supported.
Only a simple, non-partitioned partition can be split.
If the split partition is the <literal>DEFAULT</literal> partition,
one of the new partitions must be <literal>DEFAULT</literal>.
@@ -1303,18 +1303,23 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- The bounds of new partitions should not overlap with those of new or
- existing partitions (except <replaceable class="parameter">partition_name</replaceable>).
- The combined bounds of new partitions <literal>
+ The bounds of new non-<literal>DEFAULT</literal> partitions must not
+ overlap with those of new or existing partitions, except
+ <replaceable class="parameter">partition_name</replaceable>, and must be
+ contained within the bounds of the split partition
+ <replaceable class="parameter">partition_name</replaceable>.
+ If no new <literal>DEFAULT</literal> partition is specified, the
+ combined bounds of the new partitions
+ <literal>
<replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable>[, ...]
- </literal> should be equal to the bounds of the split partition
+ </literal> must exactly match the bounds of the split partition
<replaceable class="parameter">partition_name</replaceable>.
One of the new partitions can have the same name as the split partition
- <replaceable class="parameter">partition_name</replaceable>
- (this is suitable in case of splitting the <literal>DEFAULT</literal>
- partition: after the split, the <literal>DEFAULT</literal> partition
- remains with the same name, but its partition bound changes).
+ <replaceable class="parameter">partition_name</replaceable>.
+ This is useful when splitting the <literal>DEFAULT</literal> partition,
+ so that after the split, the <literal>DEFAULT</literal> partition
+ keeps the same name but its partition bound changes.
</para>
<para>
--
2.50.1 (Apple Git-155)
[application/octet-stream] v2-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch (6.5K, 5-v2-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch)
download | inline diff:
From 95cb0af42d75d23050b2f5ce01325863b7583e5c Mon Sep 17 00:00:00 2001
From: "Chao Li (Evan)" <[email protected]>
Date: Thu, 14 May 2026 13:55:37 +0800
Subject: [PATCH v2 4/4] Reject degenerate SPLIT PARTITION with DEFAULT
partition
ALTER TABLE ... SPLIT PARTITION allows a DEFAULT partition to be created
as one of the replacement partitions when the parent table does not
already have one. However, it should not allow the degenerate case where
a non-DEFAULT partition keeps exactly the same bound as the split
partition and the command merely adds a DEFAULT partition through the
SPLIT PARTITION path.
Detect that case by comparing the bound of the split partition with the
bound of the only non-DEFAULT replacement partition, and raise an error
when they are the same. Users should add a DEFAULT partition directly
with CREATE TABLE ... PARTITION OF ... DEFAULT or ALTER TABLE ... ATTACH
PARTITION ... DEFAULT instead.
Author: Chao Li <[email protected]>
Reviewed-by:
Discussion: https://postgr.es/m/[email protected]
---
src/backend/partitioning/partbounds.c | 69 +++++++++++++++++++
src/test/regress/expected/partition_split.out | 18 +++++
src/test/regress/sql/partition_split.sql | 16 +++++
3 files changed, 103 insertions(+)
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index 19589dc687f..5c1919e6d10 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -36,6 +36,7 @@
#include "utils/datum.h"
#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
+#include "utils/memutils.h"
#include "utils/partcache.h"
#include "utils/ruleutils.h"
#include "utils/snapmgr.h"
@@ -5700,6 +5701,70 @@ check_parent_values_in_new_partitions(Relation parent,
}
}
+/*
+ * check_split_partition_not_same_bound
+ *
+ * Reject splitting a non-DEFAULT partition into one non-DEFAULT partition
+ * with the original bound plus a DEFAULT partition. That form does not
+ * perform a real split; it merely adds a DEFAULT partition to the parent
+ * table through the split-partition path. Users should use
+ * CREATE TABLE ... PARTITION OF ... DEFAULT or ALTER TABLE ... ATTACH
+ * PARTITION ... DEFAULT for that.
+ */
+static void
+check_split_partition_not_same_bound(Relation parent,
+ Oid splitPartOid,
+ SinglePartitionSpec **parts,
+ int nparts,
+ ParseState *pstate)
+{
+ PartitionKey key = RelationGetPartitionKey(parent);
+ PartitionBoundSpec *split_spec;
+ PartitionBoundSpec *new_specs[1];
+ PartitionBoundSpec *old_specs[1];
+ PartitionBoundInfo new_boundinfo;
+ PartitionBoundInfo old_boundinfo;
+ int *new_mapping;
+ int *old_mapping;
+ MemoryContext old_cxt;
+ MemoryContext tmp_cxt;
+ bool same_bound;
+
+ if (nparts != 1)
+ return;
+
+ tmp_cxt = AllocSetContextCreate(CurrentMemoryContext,
+ "split partition bound comparison",
+ ALLOCSET_SMALL_SIZES);
+ old_cxt = MemoryContextSwitchTo(tmp_cxt);
+
+ split_spec = get_partition_bound_spec(splitPartOid);
+
+ new_specs[0] = parts[0]->bound;
+ new_boundinfo = partition_bounds_create(new_specs, 1, key, &new_mapping);
+
+ old_specs[0] = split_spec;
+ old_boundinfo = partition_bounds_create(old_specs, 1, key, &old_mapping);
+
+ same_bound = partition_bounds_equal(key->partnatts, key->parttyplen,
+ key->parttypbyval,
+ new_boundinfo, old_boundinfo);
+
+ MemoryContextSwitchTo(old_cxt);
+ MemoryContextDelete(tmp_cxt);
+
+ if (!same_bound)
+ return;
+
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("cannot split partition \"%s\" only to add a DEFAULT partition",
+ get_rel_name(splitPartOid)),
+ errdetail("The non-DEFAULT partition would keep the same partition bound."),
+ errhint("Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition."),
+ parser_errposition(pstate, parts[0]->name->location));
+}
+
/*
* check_partitions_for_split
*
@@ -5774,6 +5839,10 @@ check_partitions_for_split(Relation parent,
Assert(nparts == list_length(partlist) - 1);
}
+ if (!isSplitPartDefault && createDefaultPart)
+ check_split_partition_not_same_bound(parent, splitPartOid, new_parts,
+ nparts, pstate);
+
if (strategy == PARTITION_STRATEGY_RANGE)
{
PartitionRangeBound **lower_bounds;
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index d4f536c5e00..6869a65badb 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1188,6 +1188,24 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
--
+-- Test that SPLIT PARTITION rejects the degenerate case where the only
+-- non-DEFAULT replacement partition keeps the original bound and the command
+-- merely adds a DEFAULT partition.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_50 PARTITION OF t FOR VALUES FROM (0) TO (50);
+INSERT INTO t VALUES (1);
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_50 INTO
+ (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ PARTITION tp_default DEFAULT);
+ERROR: cannot split partition "tp_0_50" only to add a DEFAULT partition
+LINE 2: (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ ^
+DETAIL: The non-DEFAULT partition would keep the same partition bound.
+HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
+DROP TABLE t;
+--
-- Test that the explicit partition bound cannot extend outside the split
-- partition's bound when a DEFAULT partition is specified.
--
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index d9821c5e2a3..e7bbcc9f054 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -834,6 +834,22 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
+--
+-- Test that SPLIT PARTITION rejects the degenerate case where the only
+-- non-DEFAULT replacement partition keeps the original bound and the command
+-- merely adds a DEFAULT partition.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_50 PARTITION OF t FOR VALUES FROM (0) TO (50);
+INSERT INTO t VALUES (1);
+
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_50 INTO
+ (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ PARTITION tp_default DEFAULT);
+
+DROP TABLE t;
+
--
-- Test that the explicit partition bound cannot extend outside the split
-- partition's bound when a DEFAULT partition is specified.
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-13 20:47 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Dmitry Koval <[email protected]>
2026-05-14 06:58 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
@ 2026-05-18 09:16 ` Alexander Korotkov <[email protected]>
2026-05-18 11:56 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Alexander Korotkov @ 2026-05-18 09:16 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Dmitry Koval <[email protected]>; pgsql-hackers
On Mon, May 18, 2026 at 2:15 AM Chao Li <[email protected]> wrote:
> > On May 18, 2026, at 05:45, Alexander Korotkov <[email protected]> wrote:
> >
> > Hi, Chao!
> >
> > On Thu, May 14, 2026 at 9:59 AM Chao Li <[email protected]> wrote:
> >> To make this patch easier to process, I split it into 4 commits:
> >>
> >> 0001 - Fixes the bound-overlap bug
> >> 0002 - Fix the incorrect HINT message for the DEFAULT case
> >> 0003 - Fix the incorrect description about combined bound in the SGML doc
> >> 0004 - Reject only-create-default-partition usage
> >
> > Thank you for your work. I've revised the patchset.
> > 0002 - I've also fixed gramma of hints in other branches
> > 0004 - In the check_split_partition_not_same_bound(), calling
> > partition_bounds_create() and partition_bounds_equal() looks a bit
> > heavyweight. It doesn't matter much performance-wise, but it feels
> > like start processing from scratch while we're on quite late stage
> > already. I've replaced that with more lightweight check. Also I
> > removed dealing with memory context. This code implies small
> > non-repetitive memory allocations which only lives during DDL
> > operation, no need to wrap them with memory context as we don't do so
> > in other places.
> >
> > Any objections if I commit this?
> >
> > ------
> > Regards,
> > Alexander Korotkov
> > Supabase
>
> Hi Alexander,
>
> Thanks for the revisions. I think you may have missed the attachments, so I cannot review the changes.
Sorry. Here it is.
------
Regards,
Alexander Korotkov
Supabase
Attachments:
[application/octet-stream] v3-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch (3.8K, 2-v3-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch)
download | inline diff:
From 6749ef320e085e613f17ff917560b06d751f56b4 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 18 May 2026 00:37:06 +0300
Subject: [PATCH v3 3/4] Clarify SPLIT PARTITION bound requirements in docs
The documentation said that the bounds of new partitions should not
overlap and that their combined bounds should equal the bounds of the
split partition. That is misleading when a new DEFAULT partition is
specified, because the explicit partitions may cover only part of the
split partition while the DEFAULT partition covers the rest.
Clarify that new non-DEFAULT partition bounds must not overlap with
other new or existing partitions and must be contained within the bounds
of the split partition. Also state that the combined bounds must exactly
match the split partition only when no new DEFAULT partition is specified.
While here, improve nearby wording about hash-partitioned target tables
and splitting a DEFAULT partition with the same partition name.
Author: Chao Li <[email protected]>
Reviewed-by: Alexander Korotkov <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
doc/src/sgml/ref/alter_table.sgml | 23 ++++++++++++++---------
1 file changed, 14 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1f9a456fd33..dec34337d1a 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1293,7 +1293,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form splits a single partition of the target table into new
- partitions. Hash-partitioned target table is not supported.
+ partitions. Hash-partitioned target tables are not supported.
Only a simple, non-partitioned partition can be split.
If the split partition is the <literal>DEFAULT</literal> partition,
one of the new partitions must be <literal>DEFAULT</literal>.
@@ -1303,18 +1303,23 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- The bounds of new partitions should not overlap with those of new or
- existing partitions (except <replaceable class="parameter">partition_name</replaceable>).
- The combined bounds of new partitions <literal>
+ The bounds of new non-<literal>DEFAULT</literal> partitions must not
+ overlap with those of new or existing partitions, except
+ <replaceable class="parameter">partition_name</replaceable>, and must be
+ contained within the bounds of the split partition
+ <replaceable class="parameter">partition_name</replaceable>.
+ If no new <literal>DEFAULT</literal> partition is specified, the
+ combined bounds of the new partitions
+ <literal>
<replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable>[, ...]
- </literal> should be equal to the bounds of the split partition
+ </literal> must exactly match the bounds of the split partition
<replaceable class="parameter">partition_name</replaceable>.
One of the new partitions can have the same name as the split partition
- <replaceable class="parameter">partition_name</replaceable>
- (this is suitable in case of splitting the <literal>DEFAULT</literal>
- partition: after the split, the <literal>DEFAULT</literal> partition
- remains with the same name, but its partition bound changes).
+ <replaceable class="parameter">partition_name</replaceable>.
+ This is useful when splitting the <literal>DEFAULT</literal> partition,
+ so that after the split, the <literal>DEFAULT</literal> partition
+ keeps the same name but its partition bound changes.
</para>
<para>
--
2.39.5 (Apple Git-154)
[application/octet-stream] v3-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch (4.1K, 3-v3-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch)
download | inline diff:
From 52b66023c56f79ce955d351013b51d91b227e628 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 18 May 2026 00:36:23 +0300
Subject: [PATCH v3 1/4] Fix SPLIT PARTITION range bound validation with
DEFAULT
When splitting a range partition and defining a new DEFAULT partition, the
validation checked the lower bound of the first explicit partition and the
upper bound of explicit partitions only when they were not first. If there
was exactly one explicit non-DEFAULT partition, its upper bound was therefore
not checked.
This could allow the replacement partition to extend beyond the upper bound
of the partition being split, potentially overlapping another existing
partition.
Fix this by checking the upper bound whenever the explicit partition is the
last one. Add a regression test covering the single explicit partition plus
DEFAULT case.
Author: Chao Li <[email protected]>
Reviewed-by: Kirill Reshke <[email protected]>
Reviewed-by: Zhenwei Shang <[email protected]>
Reviewed-by: Dmitry Koval <[email protected]>
Reviewed-by: Alexander Korotkov <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/partitioning/partbounds.c | 3 ++-
src/test/regress/expected/partition_split.out | 16 ++++++++++++++++
src/test/regress/sql/partition_split.sql | 15 +++++++++++++++
3 files changed, 33 insertions(+), 1 deletion(-)
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index a09beec34d8..73dea0375be 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -5419,7 +5419,8 @@ check_partition_bounds_for_split_range(Relation parent,
"ALTER TABLE ... SPLIT PARTITION"),
parser_errposition(pstate, exprLocation((Node *) datum)));
}
- else
+
+ if (last)
{
PartitionRangeBound *split_upper;
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 961b37953c8..a2ccbe5138b 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1188,6 +1188,22 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
--
+-- Test that the explicit partition bound cannot extend outside the split
+-- partition's bound when a DEFAULT partition is specified.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_51 PARTITION OF t FOR VALUES FROM (0) TO (51);
+CREATE TABLE tp_51_100 PARTITION OF t FOR VALUES FROM (51) TO (100);
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_51 INTO
+ (PARTITION tp_0_51 FOR VALUES FROM (0) TO (53),
+ PARTITION tp_default DEFAULT);
+ERROR: upper bound of partition "tp_0_51" is greater than upper bound of split partition "tp_0_51"
+LINE 2: (PARTITION tp_0_51 FOR VALUES FROM (0) TO (53),
+ ^
+HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+DROP TABLE t;
+--
-- Try to SPLIT partition of another table.
--
CREATE TABLE t1(i int, t text) PARTITION BY LIST (t);
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index a110fc87867..d9821c5e2a3 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -834,6 +834,21 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
+--
+-- Test that the explicit partition bound cannot extend outside the split
+-- partition's bound when a DEFAULT partition is specified.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_51 PARTITION OF t FOR VALUES FROM (0) TO (51);
+CREATE TABLE tp_51_100 PARTITION OF t FOR VALUES FROM (51) TO (100);
+
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_51 INTO
+ (PARTITION tp_0_51 FOR VALUES FROM (0) TO (53),
+ PARTITION tp_default DEFAULT);
+
+DROP TABLE t;
+
--
-- Try to SPLIT partition of another table.
--
--
2.39.5 (Apple Git-154)
[application/octet-stream] v3-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch (10.1K, 4-v3-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch)
download | inline diff:
From 8b93d47913ead017bbe182b115d1069d514410eb Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 18 May 2026 00:36:44 +0300
Subject: [PATCH v3 2/4] Fix SPLIT PARTITION hint for DEFAULT partition bounds
When ALTER TABLE ... SPLIT PARTITION specifies a DEFAULT partition, the
explicit partitions do not need to cover the split partition's bound
exactly. They may cover only part of it, with the DEFAULT partition
covering the remaining range.
However, the existing hint said that the combined bounds of the new
partitions must exactly match the bound of the split partition, which is
misleading for this case and inconsistent with the code comment.
Fix the hint to state the actual requirement: explicit partition bounds
must stay within the bounds of the split partition when a DEFAULT
partition is specified.
Author: Chao Li <[email protected]>
Reviewed-by: Alexander Korotkov <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/partitioning/partbounds.c | 14 ++++++--------
src/test/regress/expected/partition_split.out | 14 +++++++-------
2 files changed, 13 insertions(+), 15 deletions(-)
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index 73dea0375be..7d3580cbc10 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -5405,7 +5405,7 @@ check_partition_bounds_for_split_range(Relation parent,
errmsg("lower bound of partition \"%s\" is not equal to lower bound of split partition \"%s\"",
relname,
get_rel_name(splitPartOid)),
- errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition.",
+ errhint("%s requires the combined bounds of the new partitions to exactly match the bound of the split partition.",
"ALTER TABLE ... SPLIT PARTITION"),
parser_errposition(pstate, exprLocation((Node *) datum)));
}
@@ -5415,8 +5415,7 @@ check_partition_bounds_for_split_range(Relation parent,
errmsg("lower bound of partition \"%s\" is less than lower bound of split partition \"%s\"",
relname,
get_rel_name(splitPartOid)),
- errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition.",
- "ALTER TABLE ... SPLIT PARTITION"),
+ errhint("Explicit partition bounds must be contained within the bounds of the split partition when a DEFAULT partition is specified."),
parser_errposition(pstate, exprLocation((Node *) datum)));
}
@@ -5448,7 +5447,7 @@ check_partition_bounds_for_split_range(Relation parent,
errmsg("upper bound of partition \"%s\" is not equal to upper bound of split partition \"%s\"",
relname,
get_rel_name(splitPartOid)),
- errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition.",
+ errhint("%s requires the combined bounds of the new partitions to exactly match the bound of the split partition.",
"ALTER TABLE ... SPLIT PARTITION"),
parser_errposition(pstate, exprLocation((Node *) datum)));
}
@@ -5458,8 +5457,7 @@ check_partition_bounds_for_split_range(Relation parent,
errmsg("upper bound of partition \"%s\" is greater than upper bound of split partition \"%s\"",
relname,
get_rel_name(splitPartOid)),
- errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition.",
- "ALTER TABLE ... SPLIT PARTITION"),
+ errhint("Explicit partition bounds must be contained within the bounds of the split partition when a DEFAULT partition is specified."),
parser_errposition(pstate, exprLocation((Node *) datum)));
}
}
@@ -5654,7 +5652,7 @@ check_parent_values_in_new_partitions(Relation parent,
errmsg("new partitions' combined partition bounds do not contain value (%s) but split partition \"%s\" does",
"NULL",
get_rel_name(partOid)),
- errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition.",
+ errhint("%s requires the combined bounds of the new partitions to exactly match the bound of the split partition.",
"ALTER TABLE ... SPLIT PARTITION"));
/*
@@ -5697,7 +5695,7 @@ check_parent_values_in_new_partitions(Relation parent,
errmsg("new partitions' combined partition bounds do not contain value (%s) but split partition \"%s\" does",
deparse_expression((Node *) notFoundVal, NIL, false, false),
get_rel_name(partOid)),
- errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition.",
+ errhint("%s requires the combined bounds of the new partitions to exactly match the bound of the split partition.",
"ALTER TABLE ... SPLIT PARTITION"));
}
}
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index a2ccbe5138b..2b9a6aa50ed 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -56,7 +56,7 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
ERROR: lower bound of partition "sales_feb2022" is not equal to lower bound of split partition "sales_feb_mar_apr2022"
LINE 2: (PARTITION sales_feb2022 FOR VALUES FROM ('2022-01-01') TO...
^
-HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+HINT: ALTER TABLE ... SPLIT PARTITION requires the combined bounds of the new partitions to exactly match the bound of the split partition.
-- ERROR
-- (We can create partition with the same name as split partition, but can't create two partitions with the same name)
ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
@@ -97,7 +97,7 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
ERROR: upper bound of partition "sales_apr2022" is not equal to upper bound of split partition "sales_feb_mar_apr2022"
LINE 4: ... sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-0...
^
-HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+HINT: ALTER TABLE ... SPLIT PARTITION requires the combined bounds of the new partitions to exactly match the bound of the split partition.
-- ERROR
ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
(PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
@@ -118,7 +118,7 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
ERROR: lower bound of partition "sales_feb2022" is not equal to lower bound of split partition "sales_feb_mar_apr2022"
LINE 2: (PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-02') TO...
^
-HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+HINT: ALTER TABLE ... SPLIT PARTITION requires the combined bounds of the new partitions to exactly match the bound of the split partition.
-- Check the source partition not in the search path
SET search_path = partition_split_schema2, public;
ALTER TABLE partition_split_schema.sales_range
@@ -154,7 +154,7 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
ERROR: upper bound of partition "sales_apr2022" is not equal to upper bound of split partition "sales_feb_mar_apr2022"
LINE 4: ... sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-0...
^
-HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+HINT: ALTER TABLE ... SPLIT PARTITION requires the combined bounds of the new partitions to exactly match the bound of the split partition.
DROP TABLE sales_range;
--
-- Add rows into partitioned table then split partition
@@ -917,14 +917,14 @@ ALTER TABLE sales_list SPLIT PARTITION sales_all INTO
PARTITION sales_east FOR VALUES IN ('Beijing', 'Delhi', 'Vladivostok'),
PARTITION sales_central FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv'));
ERROR: new partitions' combined partition bounds do not contain value (NULL) but split partition "sales_all" does
-HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+HINT: ALTER TABLE ... SPLIT PARTITION requires the combined bounds of the new partitions to exactly match the bound of the split partition.
-- ERROR
ALTER TABLE sales_list SPLIT PARTITION sales_all INTO
(PARTITION sales_west FOR VALUES IN ('Lisbon', 'New York', 'Madrid'),
PARTITION sales_east FOR VALUES IN ('Beijing', 'Delhi', 'Vladivostok'),
PARTITION sales_central FOR VALUES IN ('Warsaw', 'Berlin', NULL));
ERROR: new partitions' combined partition bounds do not contain value ('Kyiv'::character varying(20)) but split partition "sales_all" does
-HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+HINT: ALTER TABLE ... SPLIT PARTITION requires the combined bounds of the new partitions to exactly match the bound of the split partition.
-- ERROR
ALTER TABLE sales_list SPLIT PARTITION sales_all INTO
(PARTITION sales_west FOR VALUES IN ('Lisbon', 'New York', 'Madrid'),
@@ -1201,7 +1201,7 @@ ALTER TABLE t SPLIT PARTITION tp_0_51 INTO
ERROR: upper bound of partition "tp_0_51" is greater than upper bound of split partition "tp_0_51"
LINE 2: (PARTITION tp_0_51 FOR VALUES FROM (0) TO (53),
^
-HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+HINT: Explicit partition bounds must be contained within the bounds of the split partition when a DEFAULT partition is specified.
DROP TABLE t;
--
-- Try to SPLIT partition of another table.
--
2.39.5 (Apple Git-154)
[application/octet-stream] v3-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch (7.4K, 5-v3-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch)
download | inline diff:
From 866b82fdc77b6e5420b335588681f25acd20758c Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 18 May 2026 00:37:52 +0300
Subject: [PATCH v3 4/4] Reject degenerate SPLIT PARTITION with DEFAULT
partition
ALTER TABLE ... SPLIT PARTITION allows a DEFAULT partition to be created
as one of the replacement partitions when the parent table does not
already have one. However, it should not allow the degenerate case where
a non-DEFAULT partition keeps exactly the same bound as the split
partition and the command merely adds a DEFAULT partition through the
SPLIT PARTITION path.
Detect that case by comparing the bound of the split partition with the
bound of the only non-DEFAULT replacement partition, and raise an error
when they are the same. Users should add a DEFAULT partition directly
with CREATE TABLE ... PARTITION OF ... DEFAULT or ALTER TABLE ... ATTACH
PARTITION ... DEFAULT instead.
Author: Chao Li <[email protected]>
Reviewed-by: Alexander Korotkov <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/partitioning/partbounds.c | 91 +++++++++++++++++++
src/test/regress/expected/partition_split.out | 18 ++++
src/test/regress/sql/partition_split.sql | 16 ++++
3 files changed, 125 insertions(+)
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index 7d3580cbc10..f626dc019af 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -5700,6 +5700,87 @@ check_parent_values_in_new_partitions(Relation parent,
}
}
+/*
+ * check_split_partition_not_same_bound
+ *
+ * Reject splitting a non-DEFAULT partition into one non-DEFAULT partition
+ * with the original bound plus a DEFAULT partition. That form does not
+ * perform a real split; it merely adds a DEFAULT partition to the parent
+ * table through the split-partition path. Users should use
+ * CREATE TABLE ... PARTITION OF ... DEFAULT or ALTER TABLE ... ATTACH
+ * PARTITION ... DEFAULT for that.
+ *
+ * Must be called after the per-partition bound validation in
+ * check_partitions_for_split() so that containment of new bounds within the
+ * split partition is already established. Given containment, RANGE bounds
+ * are equal iff their lower and upper rbounds match; LIST bound sets are
+ * equal iff their listdatums lengths match.
+ */
+static void
+check_split_partition_not_same_bound(Relation parent,
+ Oid splitPartOid,
+ SinglePartitionSpec **parts,
+ int nparts,
+ ParseState *pstate)
+{
+ PartitionKey key = RelationGetPartitionKey(parent);
+ PartitionBoundSpec *new_spec;
+ PartitionBoundSpec *split_spec;
+
+ if (nparts != 1)
+ return;
+
+ new_spec = parts[0]->bound;
+ split_spec = get_partition_bound_spec(splitPartOid);
+
+ Assert(new_spec->strategy == split_spec->strategy);
+
+ if (key->strategy == PARTITION_STRATEGY_RANGE)
+ {
+ PartitionRangeBound *new_lower;
+ PartitionRangeBound *new_upper;
+ PartitionRangeBound *split_lower;
+ PartitionRangeBound *split_upper;
+
+ new_lower = make_one_partition_rbound(key, -1, new_spec->lowerdatums, true);
+ new_upper = make_one_partition_rbound(key, -1, new_spec->upperdatums, false);
+ split_lower = make_one_partition_rbound(key, -1, split_spec->lowerdatums, true);
+ split_upper = make_one_partition_rbound(key, -1, split_spec->upperdatums, false);
+
+ if (partition_rbound_cmp(key->partnatts, key->partsupfunc,
+ key->partcollation,
+ new_lower->datums, new_lower->kind, true,
+ split_lower) != 0)
+ return;
+ if (partition_rbound_cmp(key->partnatts, key->partsupfunc,
+ key->partcollation,
+ new_upper->datums, new_upper->kind, false,
+ split_upper) != 0)
+ return;
+ }
+ else
+ {
+ Assert(key->strategy == PARTITION_STRATEGY_LIST);
+
+ /*
+ * Containment of the new partition's values within the split
+ * partition was established by the per-partition validation; thus
+ * equality of value sets reduces to equality of cardinality.
+ */
+ if (list_length(new_spec->listdatums) !=
+ list_length(split_spec->listdatums))
+ return;
+ }
+
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("cannot split partition \"%s\" only to add a DEFAULT partition",
+ get_rel_name(splitPartOid)),
+ errdetail("The non-DEFAULT partition would keep the same partition bound."),
+ errhint("Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition."),
+ parser_errposition(pstate, parts[0]->name->location));
+}
+
/*
* check_partitions_for_split
*
@@ -5871,5 +5952,15 @@ check_partitions_for_split(Relation parent,
new_parts, nparts, pstate);
}
+ /*
+ * Reject the degenerate form where the single non-DEFAULT replacement
+ * partition keeps the bound of the split partition; the command then does
+ * nothing beyond adding a DEFAULT partition. Containment was established
+ * by the per-partition validation above, so an equality check is enough.
+ */
+ if (!isSplitPartDefault && createDefaultPart)
+ check_split_partition_not_same_bound(parent, splitPartOid, new_parts,
+ nparts, pstate);
+
pfree(new_parts);
}
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 2b9a6aa50ed..7216bd9d4f9 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1188,6 +1188,24 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
--
+-- Test that SPLIT PARTITION rejects the degenerate case where the only
+-- non-DEFAULT replacement partition keeps the original bound and the command
+-- merely adds a DEFAULT partition.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_50 PARTITION OF t FOR VALUES FROM (0) TO (50);
+INSERT INTO t VALUES (1);
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_50 INTO
+ (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ PARTITION tp_default DEFAULT);
+ERROR: cannot split partition "tp_0_50" only to add a DEFAULT partition
+LINE 2: (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ ^
+DETAIL: The non-DEFAULT partition would keep the same partition bound.
+HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
+DROP TABLE t;
+--
-- Test that the explicit partition bound cannot extend outside the split
-- partition's bound when a DEFAULT partition is specified.
--
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index d9821c5e2a3..e7bbcc9f054 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -834,6 +834,22 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
+--
+-- Test that SPLIT PARTITION rejects the degenerate case where the only
+-- non-DEFAULT replacement partition keeps the original bound and the command
+-- merely adds a DEFAULT partition.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_50 PARTITION OF t FOR VALUES FROM (0) TO (50);
+INSERT INTO t VALUES (1);
+
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_50 INTO
+ (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ PARTITION tp_default DEFAULT);
+
+DROP TABLE t;
+
--
-- Test that the explicit partition bound cannot extend outside the split
-- partition's bound when a DEFAULT partition is specified.
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-13 20:47 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Dmitry Koval <[email protected]>
2026-05-14 06:58 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 09:16 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
@ 2026-05-18 11:56 ` Chao Li <[email protected]>
2026-05-18 12:04 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Chao Li @ 2026-05-18 11:56 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: Dmitry Koval <[email protected]>; pgsql-hackers
> On May 18, 2026, at 17:16, Alexander Korotkov <[email protected]> wrote:
>
> On Mon, May 18, 2026 at 2:15 AM Chao Li <[email protected]> wrote:
>>> On May 18, 2026, at 05:45, Alexander Korotkov <[email protected]> wrote:
>>>
>>> Hi, Chao!
>>>
>>> On Thu, May 14, 2026 at 9:59 AM Chao Li <[email protected]> wrote:
>>>> To make this patch easier to process, I split it into 4 commits:
>>>>
>>>> 0001 - Fixes the bound-overlap bug
>>>> 0002 - Fix the incorrect HINT message for the DEFAULT case
>>>> 0003 - Fix the incorrect description about combined bound in the SGML doc
>>>> 0004 - Reject only-create-default-partition usage
>>>
>>> Thank you for your work. I've revised the patchset.
>>> 0002 - I've also fixed gramma of hints in other branches
>>> 0004 - In the check_split_partition_not_same_bound(), calling
>>> partition_bounds_create() and partition_bounds_equal() looks a bit
>>> heavyweight. It doesn't matter much performance-wise, but it feels
>>> like start processing from scratch while we're on quite late stage
>>> already. I've replaced that with more lightweight check. Also I
>>> removed dealing with memory context. This code implies small
>>> non-repetitive memory allocations which only lives during DDL
>>> operation, no need to wrap them with memory context as we don't do so
>>> in other places.
>>>
>>> Any objections if I commit this?
>>>
>>> ------
>>> Regards,
>>> Alexander Korotkov
>>> Supabase
>>
>> Hi Alexander,
>>
>> Thanks for the revisions. I think you may have missed the attachments, so I cannot review the changes.
>
> Sorry. Here it is.
>
> ------
> Regards,
> Alexander Korotkov
> Supabase
> <v3-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch><v3-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch><v3-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch><v3-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch>
v3-0001 through v3-0003 look good to me.
For v3-0004, I have a suspicion, but it's late here and my brain is getting slow, so I would like to study it more tomorrow.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-13 20:47 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Dmitry Koval <[email protected]>
2026-05-14 06:58 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 09:16 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-18 11:56 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
@ 2026-05-18 12:04 ` Alexander Korotkov <[email protected]>
2026-05-19 02:50 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Alexander Korotkov @ 2026-05-18 12:04 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Dmitry Koval <[email protected]>; pgsql-hackers
On Mon, May 18, 2026 at 2:57 PM Chao Li <[email protected]> wrote:
> > <v3-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch><v3-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch><v3-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch><v3-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch>
>
> v3-0001 through v3-0003 look good to me.
>
> For v3-0004, I have a suspicion, but it's late here and my brain is getting slow, so I would like to study it more tomorrow.
Sure, take your time.
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-13 20:47 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Dmitry Koval <[email protected]>
2026-05-14 06:58 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 09:16 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-18 11:56 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 12:04 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
@ 2026-05-19 02:50 ` Chao Li <[email protected]>
2026-05-19 11:00 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Chao Li @ 2026-05-19 02:50 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: Dmitry Koval <[email protected]>; pgsql-hackers
> On May 18, 2026, at 20:04, Alexander Korotkov <[email protected]> wrote:
>
> On Mon, May 18, 2026 at 2:57 PM Chao Li <[email protected]> wrote:
>>> <v3-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch><v3-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch><v3-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch><v3-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch>
>>
>> v3-0001 through v3-0003 look good to me.
>>
>> For v3-0004, I have a suspicion, but it's late here and my brain is getting slow, so I would like to study it more tomorrow.
>
> Sure, take your time.
>
> ------
> Regards,
> Alexander Korotkov
> Supabase
My suspicion was that check_split_partition_not_same_bound() now has two paths. The RANGE path honors collation, while the LIST path does not. So I spent some time creating a test that uses a case-insensitive collation:
```
evantest=# create collation case_insensitive (provider=icu, locale='und-u-ks-level2', deterministic = false);
CREATE COLLATION
evantest=# create table t (b text collate case_insensitive) partition by list (b);
CREATE TABLE
evantest=# create table tp_ab partition of t for values in ('a', 'b');
CREATE TABLE
evantest=# alter table t split partition tp_ab into
evantest-# (partition tp_a for values in ('a', 'A'),
evantest(# partition tp_default default);
ERROR: cannot split partition "tp_ab" only to add a DEFAULT partition
LINE 2: (partition tp_a for values in ('a', 'A'),
^
DETAIL: The non-DEFAULT partition would keep the same partition bound.
HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
```
In this test, the split partition’s bound is ('a', 'b'), and the new partition’s bound is ('a', 'A'). Their list lengths are both 2, but the two bounds are actually different, because 'a' and 'A' are considered equal by the collation.
So, in the LIST path, since check_partition_bounds_for_split_list() has already ensured that the new partition’s bound is contained within the split partition’s bound, we need to check the reverse direction as well. Whether the split partition’s bound is also contained in the new partition’s bound. If yes, the two bounds are identical.
See the attached v4 for my changes for 0004. 0001-0003 are unchanged. Since 0001 and 0003 are independent of 0004, maybe they can be pushed first.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
Attachments:
[application/octet-stream] v4-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch (4.1K, 2-v4-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch)
download | inline diff:
From cc401a3a77bc84e6e890ce4cef3e28ece8f284e0 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 18 May 2026 00:36:23 +0300
Subject: [PATCH v4 1/4] Fix SPLIT PARTITION range bound validation with
DEFAULT
When splitting a range partition and defining a new DEFAULT partition, the
validation checked the lower bound of the first explicit partition and the
upper bound of explicit partitions only when they were not first. If there
was exactly one explicit non-DEFAULT partition, its upper bound was therefore
not checked.
This could allow the replacement partition to extend beyond the upper bound
of the partition being split, potentially overlapping another existing
partition.
Fix this by checking the upper bound whenever the explicit partition is the
last one. Add a regression test covering the single explicit partition plus
DEFAULT case.
Author: Chao Li <[email protected]>
Reviewed-by: Kirill Reshke <[email protected]>
Reviewed-by: Zhenwei Shang <[email protected]>
Reviewed-by: Dmitry Koval <[email protected]>
Reviewed-by: Alexander Korotkov <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/partitioning/partbounds.c | 3 ++-
src/test/regress/expected/partition_split.out | 16 ++++++++++++++++
src/test/regress/sql/partition_split.sql | 15 +++++++++++++++
3 files changed, 33 insertions(+), 1 deletion(-)
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index a09beec34d8..73dea0375be 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -5419,7 +5419,8 @@ check_partition_bounds_for_split_range(Relation parent,
"ALTER TABLE ... SPLIT PARTITION"),
parser_errposition(pstate, exprLocation((Node *) datum)));
}
- else
+
+ if (last)
{
PartitionRangeBound *split_upper;
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 961b37953c8..a2ccbe5138b 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1188,6 +1188,22 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
--
+-- Test that the explicit partition bound cannot extend outside the split
+-- partition's bound when a DEFAULT partition is specified.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_51 PARTITION OF t FOR VALUES FROM (0) TO (51);
+CREATE TABLE tp_51_100 PARTITION OF t FOR VALUES FROM (51) TO (100);
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_51 INTO
+ (PARTITION tp_0_51 FOR VALUES FROM (0) TO (53),
+ PARTITION tp_default DEFAULT);
+ERROR: upper bound of partition "tp_0_51" is greater than upper bound of split partition "tp_0_51"
+LINE 2: (PARTITION tp_0_51 FOR VALUES FROM (0) TO (53),
+ ^
+HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+DROP TABLE t;
+--
-- Try to SPLIT partition of another table.
--
CREATE TABLE t1(i int, t text) PARTITION BY LIST (t);
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index a110fc87867..d9821c5e2a3 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -834,6 +834,21 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
+--
+-- Test that the explicit partition bound cannot extend outside the split
+-- partition's bound when a DEFAULT partition is specified.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_51 PARTITION OF t FOR VALUES FROM (0) TO (51);
+CREATE TABLE tp_51_100 PARTITION OF t FOR VALUES FROM (51) TO (100);
+
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_51 INTO
+ (PARTITION tp_0_51 FOR VALUES FROM (0) TO (53),
+ PARTITION tp_default DEFAULT);
+
+DROP TABLE t;
+
--
-- Try to SPLIT partition of another table.
--
--
2.50.1 (Apple Git-155)
[application/octet-stream] v4-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch (10.1K, 3-v4-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch)
download | inline diff:
From 57dea552776e487dc3a10858238e33ed2aa94700 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 18 May 2026 00:36:44 +0300
Subject: [PATCH v4 2/4] Fix SPLIT PARTITION hint for DEFAULT partition bounds
When ALTER TABLE ... SPLIT PARTITION specifies a DEFAULT partition, the
explicit partitions do not need to cover the split partition's bound
exactly. They may cover only part of it, with the DEFAULT partition
covering the remaining range.
However, the existing hint said that the combined bounds of the new
partitions must exactly match the bound of the split partition, which is
misleading for this case and inconsistent with the code comment.
Fix the hint to state the actual requirement: explicit partition bounds
must stay within the bounds of the split partition when a DEFAULT
partition is specified.
Author: Chao Li <[email protected]>
Reviewed-by: Alexander Korotkov <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/partitioning/partbounds.c | 14 ++++++--------
src/test/regress/expected/partition_split.out | 14 +++++++-------
2 files changed, 13 insertions(+), 15 deletions(-)
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index 73dea0375be..7d3580cbc10 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -5405,7 +5405,7 @@ check_partition_bounds_for_split_range(Relation parent,
errmsg("lower bound of partition \"%s\" is not equal to lower bound of split partition \"%s\"",
relname,
get_rel_name(splitPartOid)),
- errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition.",
+ errhint("%s requires the combined bounds of the new partitions to exactly match the bound of the split partition.",
"ALTER TABLE ... SPLIT PARTITION"),
parser_errposition(pstate, exprLocation((Node *) datum)));
}
@@ -5415,8 +5415,7 @@ check_partition_bounds_for_split_range(Relation parent,
errmsg("lower bound of partition \"%s\" is less than lower bound of split partition \"%s\"",
relname,
get_rel_name(splitPartOid)),
- errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition.",
- "ALTER TABLE ... SPLIT PARTITION"),
+ errhint("Explicit partition bounds must be contained within the bounds of the split partition when a DEFAULT partition is specified."),
parser_errposition(pstate, exprLocation((Node *) datum)));
}
@@ -5448,7 +5447,7 @@ check_partition_bounds_for_split_range(Relation parent,
errmsg("upper bound of partition \"%s\" is not equal to upper bound of split partition \"%s\"",
relname,
get_rel_name(splitPartOid)),
- errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition.",
+ errhint("%s requires the combined bounds of the new partitions to exactly match the bound of the split partition.",
"ALTER TABLE ... SPLIT PARTITION"),
parser_errposition(pstate, exprLocation((Node *) datum)));
}
@@ -5458,8 +5457,7 @@ check_partition_bounds_for_split_range(Relation parent,
errmsg("upper bound of partition \"%s\" is greater than upper bound of split partition \"%s\"",
relname,
get_rel_name(splitPartOid)),
- errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition.",
- "ALTER TABLE ... SPLIT PARTITION"),
+ errhint("Explicit partition bounds must be contained within the bounds of the split partition when a DEFAULT partition is specified."),
parser_errposition(pstate, exprLocation((Node *) datum)));
}
}
@@ -5654,7 +5652,7 @@ check_parent_values_in_new_partitions(Relation parent,
errmsg("new partitions' combined partition bounds do not contain value (%s) but split partition \"%s\" does",
"NULL",
get_rel_name(partOid)),
- errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition.",
+ errhint("%s requires the combined bounds of the new partitions to exactly match the bound of the split partition.",
"ALTER TABLE ... SPLIT PARTITION"));
/*
@@ -5697,7 +5695,7 @@ check_parent_values_in_new_partitions(Relation parent,
errmsg("new partitions' combined partition bounds do not contain value (%s) but split partition \"%s\" does",
deparse_expression((Node *) notFoundVal, NIL, false, false),
get_rel_name(partOid)),
- errhint("%s require combined bounds of new partitions must exactly match the bound of the split partition.",
+ errhint("%s requires the combined bounds of the new partitions to exactly match the bound of the split partition.",
"ALTER TABLE ... SPLIT PARTITION"));
}
}
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index a2ccbe5138b..2b9a6aa50ed 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -56,7 +56,7 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
ERROR: lower bound of partition "sales_feb2022" is not equal to lower bound of split partition "sales_feb_mar_apr2022"
LINE 2: (PARTITION sales_feb2022 FOR VALUES FROM ('2022-01-01') TO...
^
-HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+HINT: ALTER TABLE ... SPLIT PARTITION requires the combined bounds of the new partitions to exactly match the bound of the split partition.
-- ERROR
-- (We can create partition with the same name as split partition, but can't create two partitions with the same name)
ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
@@ -97,7 +97,7 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
ERROR: upper bound of partition "sales_apr2022" is not equal to upper bound of split partition "sales_feb_mar_apr2022"
LINE 4: ... sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-0...
^
-HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+HINT: ALTER TABLE ... SPLIT PARTITION requires the combined bounds of the new partitions to exactly match the bound of the split partition.
-- ERROR
ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
(PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'),
@@ -118,7 +118,7 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
ERROR: lower bound of partition "sales_feb2022" is not equal to lower bound of split partition "sales_feb_mar_apr2022"
LINE 2: (PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-02') TO...
^
-HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+HINT: ALTER TABLE ... SPLIT PARTITION requires the combined bounds of the new partitions to exactly match the bound of the split partition.
-- Check the source partition not in the search path
SET search_path = partition_split_schema2, public;
ALTER TABLE partition_split_schema.sales_range
@@ -154,7 +154,7 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO
ERROR: upper bound of partition "sales_apr2022" is not equal to upper bound of split partition "sales_feb_mar_apr2022"
LINE 4: ... sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-06-0...
^
-HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+HINT: ALTER TABLE ... SPLIT PARTITION requires the combined bounds of the new partitions to exactly match the bound of the split partition.
DROP TABLE sales_range;
--
-- Add rows into partitioned table then split partition
@@ -917,14 +917,14 @@ ALTER TABLE sales_list SPLIT PARTITION sales_all INTO
PARTITION sales_east FOR VALUES IN ('Beijing', 'Delhi', 'Vladivostok'),
PARTITION sales_central FOR VALUES IN ('Warsaw', 'Berlin', 'Kyiv'));
ERROR: new partitions' combined partition bounds do not contain value (NULL) but split partition "sales_all" does
-HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+HINT: ALTER TABLE ... SPLIT PARTITION requires the combined bounds of the new partitions to exactly match the bound of the split partition.
-- ERROR
ALTER TABLE sales_list SPLIT PARTITION sales_all INTO
(PARTITION sales_west FOR VALUES IN ('Lisbon', 'New York', 'Madrid'),
PARTITION sales_east FOR VALUES IN ('Beijing', 'Delhi', 'Vladivostok'),
PARTITION sales_central FOR VALUES IN ('Warsaw', 'Berlin', NULL));
ERROR: new partitions' combined partition bounds do not contain value ('Kyiv'::character varying(20)) but split partition "sales_all" does
-HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+HINT: ALTER TABLE ... SPLIT PARTITION requires the combined bounds of the new partitions to exactly match the bound of the split partition.
-- ERROR
ALTER TABLE sales_list SPLIT PARTITION sales_all INTO
(PARTITION sales_west FOR VALUES IN ('Lisbon', 'New York', 'Madrid'),
@@ -1201,7 +1201,7 @@ ALTER TABLE t SPLIT PARTITION tp_0_51 INTO
ERROR: upper bound of partition "tp_0_51" is greater than upper bound of split partition "tp_0_51"
LINE 2: (PARTITION tp_0_51 FOR VALUES FROM (0) TO (53),
^
-HINT: ALTER TABLE ... SPLIT PARTITION require combined bounds of new partitions must exactly match the bound of the split partition.
+HINT: Explicit partition bounds must be contained within the bounds of the split partition when a DEFAULT partition is specified.
DROP TABLE t;
--
-- Try to SPLIT partition of another table.
--
2.50.1 (Apple Git-155)
[application/octet-stream] v4-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch (3.8K, 4-v4-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch)
download | inline diff:
From 4c1be7b104c4dc507384e7e63de8b491d6ae3f37 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 18 May 2026 00:37:06 +0300
Subject: [PATCH v4 3/4] Clarify SPLIT PARTITION bound requirements in docs
The documentation said that the bounds of new partitions should not
overlap and that their combined bounds should equal the bounds of the
split partition. That is misleading when a new DEFAULT partition is
specified, because the explicit partitions may cover only part of the
split partition while the DEFAULT partition covers the rest.
Clarify that new non-DEFAULT partition bounds must not overlap with
other new or existing partitions and must be contained within the bounds
of the split partition. Also state that the combined bounds must exactly
match the split partition only when no new DEFAULT partition is specified.
While here, improve nearby wording about hash-partitioned target tables
and splitting a DEFAULT partition with the same partition name.
Author: Chao Li <[email protected]>
Reviewed-by: Alexander Korotkov <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
doc/src/sgml/ref/alter_table.sgml | 23 ++++++++++++++---------
1 file changed, 14 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1f9a456fd33..dec34337d1a 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1293,7 +1293,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form splits a single partition of the target table into new
- partitions. Hash-partitioned target table is not supported.
+ partitions. Hash-partitioned target tables are not supported.
Only a simple, non-partitioned partition can be split.
If the split partition is the <literal>DEFAULT</literal> partition,
one of the new partitions must be <literal>DEFAULT</literal>.
@@ -1303,18 +1303,23 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- The bounds of new partitions should not overlap with those of new or
- existing partitions (except <replaceable class="parameter">partition_name</replaceable>).
- The combined bounds of new partitions <literal>
+ The bounds of new non-<literal>DEFAULT</literal> partitions must not
+ overlap with those of new or existing partitions, except
+ <replaceable class="parameter">partition_name</replaceable>, and must be
+ contained within the bounds of the split partition
+ <replaceable class="parameter">partition_name</replaceable>.
+ If no new <literal>DEFAULT</literal> partition is specified, the
+ combined bounds of the new partitions
+ <literal>
<replaceable class="parameter">partition_name1</replaceable>,
<replaceable class="parameter">partition_name2</replaceable>[, ...]
- </literal> should be equal to the bounds of the split partition
+ </literal> must exactly match the bounds of the split partition
<replaceable class="parameter">partition_name</replaceable>.
One of the new partitions can have the same name as the split partition
- <replaceable class="parameter">partition_name</replaceable>
- (this is suitable in case of splitting the <literal>DEFAULT</literal>
- partition: after the split, the <literal>DEFAULT</literal> partition
- remains with the same name, but its partition bound changes).
+ <replaceable class="parameter">partition_name</replaceable>.
+ This is useful when splitting the <literal>DEFAULT</literal> partition,
+ so that after the split, the <literal>DEFAULT</literal> partition
+ keeps the same name but its partition bound changes.
</para>
<para>
--
2.50.1 (Apple Git-155)
[application/octet-stream] v4-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch (11.9K, 5-v4-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch)
download | inline diff:
From 3c033ade957420ffcc5fcac8f925a6c9a7a9f009 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 18 May 2026 00:37:52 +0300
Subject: [PATCH v4 4/4] Reject degenerate SPLIT PARTITION with DEFAULT
partition
ALTER TABLE ... SPLIT PARTITION allows a DEFAULT partition to be created
as one of the replacement partitions when the parent table does not
already have one. However, it should not allow the degenerate case where
a non-DEFAULT partition keeps exactly the same bound as the split
partition and the command merely adds a DEFAULT partition through the
SPLIT PARTITION path.
Detect that case by comparing the bound of the split partition with the
bound of the only non-DEFAULT replacement partition, and raise an error
when they are the same. Users should add a DEFAULT partition directly
with CREATE TABLE ... PARTITION OF ... DEFAULT or ALTER TABLE ... ATTACH
PARTITION ... DEFAULT instead.
Author: Chao Li <[email protected]>
Reviewed-by: Alexander Korotkov <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/partitioning/partbounds.c | 145 ++++++++++++++++++
src/test/regress/expected/partition_split.out | 59 +++++++
src/test/regress/sql/partition_split.sql | 52 +++++++
3 files changed, 256 insertions(+)
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index 7d3580cbc10..126a9be15ba 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -5700,6 +5700,141 @@ check_parent_values_in_new_partitions(Relation parent,
}
}
+/*
+ * split_partition_values_contained_in_new_part
+ *
+ * (function for BY LIST partitioning)
+ *
+ * Returns true if all values in the LIST bound of the partition being split
+ * are contained in the specified non-DEFAULT replacement partition's bound.
+ *
+ * The caller must already have verified containment in the other direction,
+ * so this check is sufficient to prove that the two LIST bounds are equal.
+ */
+static bool
+split_partition_values_contained_in_new_part(Relation parent,
+ Oid splitPartOid,
+ SinglePartitionSpec *part)
+{
+ PartitionKey key = RelationGetPartitionKey(parent);
+ PartitionDesc partdesc = RelationGetPartitionDesc(parent, false);
+ PartitionBoundInfo boundinfo = partdesc->boundinfo;
+ SinglePartitionSpec *parts[1];
+ Datum datum = PointerGetDatum(NULL);
+
+ Assert(key->strategy == PARTITION_STRATEGY_LIST);
+
+ parts[0] = part;
+
+ /*
+ * Special processing for NULL value. Search for a NULL value if the
+ * split partition contains it.
+ */
+ if (partition_bound_accepts_nulls(boundinfo) &&
+ partdesc->oids[boundinfo->null_index] == splitPartOid)
+ {
+ if (!find_value_in_new_partitions_list(&key->partsupfunc[0],
+ key->partcollation, parts, 1,
+ datum, true))
+ return false;
+ }
+
+ /*
+ * Search all values of the split partition in the single non-DEFAULT
+ * replacement partition.
+ */
+ for (int i = 0; i < boundinfo->ndatums; i++)
+ {
+ if (partdesc->oids[boundinfo->indexes[i]] == splitPartOid)
+ {
+ datum = boundinfo->datums[i][0];
+
+ if (!find_value_in_new_partitions_list(&key->partsupfunc[0],
+ key->partcollation, parts, 1,
+ datum, false))
+ return false;
+ }
+ }
+
+ return true;
+}
+
+/*
+ * check_split_partition_not_same_bound
+ *
+ * Reject splitting a non-DEFAULT partition into one non-DEFAULT partition
+ * with the original bound plus a DEFAULT partition. That form does not
+ * perform a real split; it merely adds a DEFAULT partition to the parent
+ * table through the split-partition path. Users should use
+ * CREATE TABLE ... PARTITION OF ... DEFAULT or ALTER TABLE ... ATTACH
+ * PARTITION ... DEFAULT for that.
+ *
+ * Must be called after the per-partition bound validation in
+ * check_partitions_for_split() so that containment of new bounds within the
+ * split partition is already established. Given containment, RANGE bounds
+ * are equal iff their lower and upper rbounds match; LIST bound sets are
+ * equal iff the split partition's values are also contained in the new
+ * partition.
+ */
+static void
+check_split_partition_not_same_bound(Relation parent,
+ Oid splitPartOid,
+ SinglePartitionSpec **parts,
+ int nparts,
+ ParseState *pstate)
+{
+ PartitionKey key = RelationGetPartitionKey(parent);
+ PartitionBoundSpec *new_spec;
+ PartitionBoundSpec *split_spec;
+
+ if (nparts != 1)
+ return;
+
+ new_spec = parts[0]->bound;
+ split_spec = get_partition_bound_spec(splitPartOid);
+
+ Assert(new_spec->strategy == split_spec->strategy);
+
+ if (key->strategy == PARTITION_STRATEGY_RANGE)
+ {
+ PartitionRangeBound *new_lower;
+ PartitionRangeBound *new_upper;
+ PartitionRangeBound *split_lower;
+ PartitionRangeBound *split_upper;
+
+ new_lower = make_one_partition_rbound(key, -1, new_spec->lowerdatums, true);
+ new_upper = make_one_partition_rbound(key, -1, new_spec->upperdatums, false);
+ split_lower = make_one_partition_rbound(key, -1, split_spec->lowerdatums, true);
+ split_upper = make_one_partition_rbound(key, -1, split_spec->upperdatums, false);
+
+ if (partition_rbound_cmp(key->partnatts, key->partsupfunc,
+ key->partcollation,
+ new_lower->datums, new_lower->kind, true,
+ split_lower) != 0)
+ return;
+ if (partition_rbound_cmp(key->partnatts, key->partsupfunc,
+ key->partcollation,
+ new_upper->datums, new_upper->kind, false,
+ split_upper) != 0)
+ return;
+ }
+ else
+ {
+ Assert(key->strategy == PARTITION_STRATEGY_LIST);
+
+ if (!split_partition_values_contained_in_new_part(parent, splitPartOid, parts[0]))
+ return;
+ }
+
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("cannot split partition \"%s\" only to add a DEFAULT partition",
+ get_rel_name(splitPartOid)),
+ errdetail("The non-DEFAULT partition would keep the same partition bound."),
+ errhint("Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition."),
+ parser_errposition(pstate, parts[0]->name->location));
+}
+
/*
* check_partitions_for_split
*
@@ -5871,5 +6006,15 @@ check_partitions_for_split(Relation parent,
new_parts, nparts, pstate);
}
+ /*
+ * Reject the degenerate form where the single non-DEFAULT replacement
+ * partition keeps the bound of the split partition; the command then does
+ * nothing beyond adding a DEFAULT partition. Containment was established
+ * by the per-partition validation above, so an equality check is enough.
+ */
+ if (!isSplitPartDefault && createDefaultPart)
+ check_split_partition_not_same_bound(parent, splitPartOid, new_parts,
+ nparts, pstate);
+
pfree(new_parts);
}
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 2b9a6aa50ed..2fd9aee1dcc 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1188,6 +1188,65 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
--
+-- Test that SPLIT PARTITION rejects the degenerate case where the only
+-- non-DEFAULT replacement partition keeps the original bound and the command
+-- merely adds a DEFAULT partition.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_50 PARTITION OF t FOR VALUES FROM (0) TO (50);
+INSERT INTO t VALUES (1);
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_50 INTO
+ (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ PARTITION tp_default DEFAULT);
+ERROR: cannot split partition "tp_0_50" only to add a DEFAULT partition
+LINE 2: (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ ^
+DETAIL: The non-DEFAULT partition would keep the same partition bound.
+HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
+DROP TABLE t;
+--
+-- Test that a LIST split with DEFAULT is not considered degenerate when
+-- only NULL is removed from the explicit replacement partition.
+--
+CREATE TABLE t (i int) PARTITION BY LIST (i);
+CREATE TABLE tp_null_1 PARTITION OF t FOR VALUES IN (NULL, 1);
+ALTER TABLE t SPLIT PARTITION tp_null_1 INTO
+ (PARTITION tp_1 FOR VALUES IN (1),
+ PARTITION tp_default DEFAULT);
+INSERT INTO t VALUES (NULL), (1), (2);
+SELECT tableoid::regclass, i FROM t ORDER BY tableoid::regclass::text COLLATE "C", i NULLS FIRST;
+ tableoid | i
+------------+---
+ tp_1 | 1
+ tp_default |
+ tp_default | 2
+(3 rows)
+
+DROP TABLE t;
+--
+-- Test that the same-bound check for LIST partitioning uses partition
+-- comparison semantics, not raw list length. The case-insensitive collation
+-- treats 'a' and 'A' as equal, so the non-DEFAULT replacement partition
+-- covers only the 'a' group and the DEFAULT partition covers the rest.
+--
+CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
+CREATE TABLE t (b text COLLATE case_insensitive) PARTITION BY LIST (b);
+CREATE TABLE tp_ab PARTITION OF t FOR VALUES IN ('a', 'b');
+ALTER TABLE t SPLIT PARTITION tp_ab INTO
+ (PARTITION tp_a FOR VALUES IN ('a', 'A'),
+ PARTITION tp_default DEFAULT);
+INSERT INTO t VALUES ('a'), ('A'), ('b'), ('c');
+SELECT tableoid::regclass, count(*) FROM t GROUP BY 1 ORDER BY 1;
+ tableoid | count
+------------+-------
+ tp_a | 2
+ tp_default | 2
+(2 rows)
+
+DROP TABLE t;
+DROP COLLATION case_insensitive;
+--
-- Test that the explicit partition bound cannot extend outside the split
-- partition's bound when a DEFAULT partition is specified.
--
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index d9821c5e2a3..ede89ad0228 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -834,6 +834,58 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
+--
+-- Test that SPLIT PARTITION rejects the degenerate case where the only
+-- non-DEFAULT replacement partition keeps the original bound and the command
+-- merely adds a DEFAULT partition.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_50 PARTITION OF t FOR VALUES FROM (0) TO (50);
+INSERT INTO t VALUES (1);
+
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_50 INTO
+ (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ PARTITION tp_default DEFAULT);
+
+DROP TABLE t;
+
+--
+-- Test that a LIST split with DEFAULT is not considered degenerate when
+-- only NULL is removed from the explicit replacement partition.
+--
+CREATE TABLE t (i int) PARTITION BY LIST (i);
+CREATE TABLE tp_null_1 PARTITION OF t FOR VALUES IN (NULL, 1);
+
+ALTER TABLE t SPLIT PARTITION tp_null_1 INTO
+ (PARTITION tp_1 FOR VALUES IN (1),
+ PARTITION tp_default DEFAULT);
+
+INSERT INTO t VALUES (NULL), (1), (2);
+SELECT tableoid::regclass, i FROM t ORDER BY tableoid::regclass::text COLLATE "C", i NULLS FIRST;
+
+DROP TABLE t;
+
+--
+-- Test that the same-bound check for LIST partitioning uses partition
+-- comparison semantics, not raw list length. The case-insensitive collation
+-- treats 'a' and 'A' as equal, so the non-DEFAULT replacement partition
+-- covers only the 'a' group and the DEFAULT partition covers the rest.
+--
+CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
+CREATE TABLE t (b text COLLATE case_insensitive) PARTITION BY LIST (b);
+CREATE TABLE tp_ab PARTITION OF t FOR VALUES IN ('a', 'b');
+
+ALTER TABLE t SPLIT PARTITION tp_ab INTO
+ (PARTITION tp_a FOR VALUES IN ('a', 'A'),
+ PARTITION tp_default DEFAULT);
+
+INSERT INTO t VALUES ('a'), ('A'), ('b'), ('c');
+SELECT tableoid::regclass, count(*) FROM t GROUP BY 1 ORDER BY 1;
+
+DROP TABLE t;
+DROP COLLATION case_insensitive;
+
--
-- Test that the explicit partition bound cannot extend outside the split
-- partition's bound when a DEFAULT partition is specified.
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-13 20:47 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Dmitry Koval <[email protected]>
2026-05-14 06:58 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 09:16 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-18 11:56 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 12:04 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-19 02:50 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
@ 2026-05-19 11:00 ` Alexander Korotkov <[email protected]>
2026-05-19 23:36 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Alexander Korotkov @ 2026-05-19 11:00 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Dmitry Koval <[email protected]>; pgsql-hackers
Hi, Chao!
On Tue, May 19, 2026 at 5:50 AM Chao Li <[email protected]> wrote:
> > On May 18, 2026, at 20:04, Alexander Korotkov <[email protected]> wrote:
> >
> > On Mon, May 18, 2026 at 2:57 PM Chao Li <[email protected]> wrote:
> >>> <v3-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch><v3-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch><v3-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch><v3-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch>
> >>
> >> v3-0001 through v3-0003 look good to me.
> >>
> >> For v3-0004, I have a suspicion, but it's late here and my brain is getting slow, so I would like to study it more tomorrow.
> >
> > Sure, take your time.
> >
> > ------
> > Regards,
> > Alexander Korotkov
> > Supabase
>
> My suspicion was that check_split_partition_not_same_bound() now has two paths. The RANGE path honors collation, while the LIST path does not. So I spent some time creating a test that uses a case-insensitive collation:
> ```
> evantest=# create collation case_insensitive (provider=icu, locale='und-u-ks-level2', deterministic = false);
> CREATE COLLATION
> evantest=# create table t (b text collate case_insensitive) partition by list (b);
> CREATE TABLE
> evantest=# create table tp_ab partition of t for values in ('a', 'b');
> CREATE TABLE
> evantest=# alter table t split partition tp_ab into
> evantest-# (partition tp_a for values in ('a', 'A'),
> evantest(# partition tp_default default);
> ERROR: cannot split partition "tp_ab" only to add a DEFAULT partition
> LINE 2: (partition tp_a for values in ('a', 'A'),
> ^
> DETAIL: The non-DEFAULT partition would keep the same partition bound.
> HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
> ```
>
> In this test, the split partition’s bound is ('a', 'b'), and the new partition’s bound is ('a', 'A'). Their list lengths are both 2, but the two bounds are actually different, because 'a' and 'A' are considered equal by the collation.
>
> So, in the LIST path, since check_partition_bounds_for_split_list() has already ensured that the new partition’s bound is contained within the split partition’s bound, we need to check the reverse direction as well. Whether the split partition’s bound is also contained in the new partition’s bound. If yes, the two bounds are identical.
>
> See the attached v4 for my changes for 0004. 0001-0003 are unchanged. Since 0001 and 0003 are independent of 0004, maybe they can be pushed first.
I've pushed 0001-0003. Thank you for discovering the collation issue
in 0004. Note that original approach of using
partition_bounds_equal() can't handle different collations too (as it
internally uses datumIsEqual()). I've revised the remaining patch:
made function header comment a bit more detailed and added additional
regression tests. Please, check.
------
Regards,
Alexander Korotkov
Supabase
Attachments:
[application/octet-stream] v5-0001-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch (12.2K, 2-v5-0001-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch)
download | inline diff:
From eb03bd8d346c072af5024899acc79b4991cd45a4 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 18 May 2026 00:37:52 +0300
Subject: [PATCH v5] Reject degenerate SPLIT PARTITION with DEFAULT partition
ALTER TABLE ... SPLIT PARTITION allows a DEFAULT partition to be created
as one of the replacement partitions when the parent table does not
already have one. However, it should not allow the degenerate case where
a non-DEFAULT partition keeps exactly the same bound as the split
partition and the command merely adds a DEFAULT partition through the
SPLIT PARTITION path.
Detect that case by comparing the bound of the split partition with the
bound of the only non-DEFAULT replacement partition, and raise an error
when they are the same. Users should add a DEFAULT partition directly
with CREATE TABLE ... PARTITION OF ... DEFAULT or ALTER TABLE ... ATTACH
PARTITION ... DEFAULT instead.
Author: Chao Li <[email protected]>
Reviewed-by: Alexander Korotkov <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/partitioning/partbounds.c | 150 ++++++++++++++++++
src/test/regress/expected/partition_split.out | 59 +++++++
src/test/regress/sql/partition_split.sql | 52 ++++++
3 files changed, 261 insertions(+)
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index 7d3580cbc10..eac807ddb77 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -5700,6 +5700,146 @@ check_parent_values_in_new_partitions(Relation parent,
}
}
+/*
+ * split_partition_values_contained_in_new_part
+ *
+ * (function for BY LIST partitioning)
+ *
+ * Returns true if all values in the LIST bound of the partition being split
+ * are contained in the specified non-DEFAULT replacement partition's bound.
+ *
+ * The caller must already have verified containment in the other direction,
+ * so this check is sufficient to prove that the two LIST bounds are equal.
+ */
+static bool
+split_partition_values_contained_in_new_part(Relation parent,
+ Oid splitPartOid,
+ SinglePartitionSpec *part)
+{
+ PartitionKey key = RelationGetPartitionKey(parent);
+ PartitionDesc partdesc = RelationGetPartitionDesc(parent, false);
+ PartitionBoundInfo boundinfo = partdesc->boundinfo;
+ SinglePartitionSpec *parts[1];
+ Datum datum = PointerGetDatum(NULL);
+
+ Assert(key->strategy == PARTITION_STRATEGY_LIST);
+
+ parts[0] = part;
+
+ /*
+ * Special processing for NULL value. Search for a NULL value if the
+ * split partition contains it.
+ */
+ if (partition_bound_accepts_nulls(boundinfo) &&
+ partdesc->oids[boundinfo->null_index] == splitPartOid)
+ {
+ if (!find_value_in_new_partitions_list(&key->partsupfunc[0],
+ key->partcollation, parts, 1,
+ datum, true))
+ return false;
+ }
+
+ /*
+ * Search all values of the split partition in the single non-DEFAULT
+ * replacement partition.
+ */
+ for (int i = 0; i < boundinfo->ndatums; i++)
+ {
+ if (partdesc->oids[boundinfo->indexes[i]] == splitPartOid)
+ {
+ datum = boundinfo->datums[i][0];
+
+ if (!find_value_in_new_partitions_list(&key->partsupfunc[0],
+ key->partcollation, parts, 1,
+ datum, false))
+ return false;
+ }
+ }
+
+ return true;
+}
+
+/*
+ * check_split_partition_not_same_bound
+ *
+ * Reject splitting a non-DEFAULT partition into one non-DEFAULT partition
+ * with the original bound plus a DEFAULT partition. That form does not
+ * perform a real split; it merely adds a DEFAULT partition to the parent
+ * table through the split-partition path. Users should use
+ * CREATE TABLE ... PARTITION OF ... DEFAULT or ALTER TABLE ... ATTACH
+ * PARTITION ... DEFAULT for that.
+ *
+ * Must be called after the per-partition bound validation in
+ * check_partitions_for_split() so that containment of new bounds within the
+ * split partition is already established. Given containment, RANGE bounds
+ * are equal iff their lower and upper rbounds match; LIST bound sets are
+ * equal iff the split partition's values are also contained in the new
+ * partition (the containment is then bidirectional). Both checks honor
+ * the partition key collation via the operator-family comparators
+ * (partition_rbound_cmp / find_value_in_new_partitions_list), so e.g.
+ * ('a','b') and ('A','B') under a case-insensitive ICU collation are
+ * correctly recognised as the same bound.
+ */
+static void
+check_split_partition_not_same_bound(Relation parent,
+ Oid splitPartOid,
+ SinglePartitionSpec **parts,
+ int nparts,
+ ParseState *pstate)
+{
+ PartitionKey key = RelationGetPartitionKey(parent);
+ PartitionBoundSpec *new_spec;
+ PartitionBoundSpec *split_spec;
+
+ if (nparts != 1)
+ return;
+
+ new_spec = parts[0]->bound;
+ split_spec = get_partition_bound_spec(splitPartOid);
+
+ Assert(new_spec->strategy == split_spec->strategy);
+
+ if (key->strategy == PARTITION_STRATEGY_RANGE)
+ {
+ PartitionRangeBound *new_lower;
+ PartitionRangeBound *new_upper;
+ PartitionRangeBound *split_lower;
+ PartitionRangeBound *split_upper;
+
+ new_lower = make_one_partition_rbound(key, -1, new_spec->lowerdatums, true);
+ new_upper = make_one_partition_rbound(key, -1, new_spec->upperdatums, false);
+ split_lower = make_one_partition_rbound(key, -1, split_spec->lowerdatums, true);
+ split_upper = make_one_partition_rbound(key, -1, split_spec->upperdatums, false);
+
+ if (partition_rbound_cmp(key->partnatts, key->partsupfunc,
+ key->partcollation,
+ new_lower->datums, new_lower->kind, true,
+ split_lower) != 0)
+ return;
+ if (partition_rbound_cmp(key->partnatts, key->partsupfunc,
+ key->partcollation,
+ new_upper->datums, new_upper->kind, false,
+ split_upper) != 0)
+ return;
+ }
+ else
+ {
+ Assert(key->strategy == PARTITION_STRATEGY_LIST);
+
+ if (!split_partition_values_contained_in_new_part(parent, splitPartOid,
+ parts[0]))
+ return;
+ }
+
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("cannot split partition \"%s\" only to add a DEFAULT partition",
+ get_rel_name(splitPartOid)),
+ errdetail("The non-DEFAULT partition would keep the same partition bound."),
+ errhint("Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition."),
+ parser_errposition(pstate, parts[0]->name->location));
+}
+
/*
* check_partitions_for_split
*
@@ -5871,5 +6011,15 @@ check_partitions_for_split(Relation parent,
new_parts, nparts, pstate);
}
+ /*
+ * Reject the degenerate form where the single non-DEFAULT replacement
+ * partition keeps the bound of the split partition; the command then does
+ * nothing beyond adding a DEFAULT partition. Containment was established
+ * by the per-partition validation above, so an equality check is enough.
+ */
+ if (!isSplitPartDefault && createDefaultPart)
+ check_split_partition_not_same_bound(parent, splitPartOid, new_parts,
+ nparts, pstate);
+
pfree(new_parts);
}
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 2b9a6aa50ed..2fd9aee1dcc 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1188,6 +1188,65 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
--
+-- Test that SPLIT PARTITION rejects the degenerate case where the only
+-- non-DEFAULT replacement partition keeps the original bound and the command
+-- merely adds a DEFAULT partition.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_50 PARTITION OF t FOR VALUES FROM (0) TO (50);
+INSERT INTO t VALUES (1);
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_50 INTO
+ (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ PARTITION tp_default DEFAULT);
+ERROR: cannot split partition "tp_0_50" only to add a DEFAULT partition
+LINE 2: (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ ^
+DETAIL: The non-DEFAULT partition would keep the same partition bound.
+HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
+DROP TABLE t;
+--
+-- Test that a LIST split with DEFAULT is not considered degenerate when
+-- only NULL is removed from the explicit replacement partition.
+--
+CREATE TABLE t (i int) PARTITION BY LIST (i);
+CREATE TABLE tp_null_1 PARTITION OF t FOR VALUES IN (NULL, 1);
+ALTER TABLE t SPLIT PARTITION tp_null_1 INTO
+ (PARTITION tp_1 FOR VALUES IN (1),
+ PARTITION tp_default DEFAULT);
+INSERT INTO t VALUES (NULL), (1), (2);
+SELECT tableoid::regclass, i FROM t ORDER BY tableoid::regclass::text COLLATE "C", i NULLS FIRST;
+ tableoid | i
+------------+---
+ tp_1 | 1
+ tp_default |
+ tp_default | 2
+(3 rows)
+
+DROP TABLE t;
+--
+-- Test that the same-bound check for LIST partitioning uses partition
+-- comparison semantics, not raw list length. The case-insensitive collation
+-- treats 'a' and 'A' as equal, so the non-DEFAULT replacement partition
+-- covers only the 'a' group and the DEFAULT partition covers the rest.
+--
+CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
+CREATE TABLE t (b text COLLATE case_insensitive) PARTITION BY LIST (b);
+CREATE TABLE tp_ab PARTITION OF t FOR VALUES IN ('a', 'b');
+ALTER TABLE t SPLIT PARTITION tp_ab INTO
+ (PARTITION tp_a FOR VALUES IN ('a', 'A'),
+ PARTITION tp_default DEFAULT);
+INSERT INTO t VALUES ('a'), ('A'), ('b'), ('c');
+SELECT tableoid::regclass, count(*) FROM t GROUP BY 1 ORDER BY 1;
+ tableoid | count
+------------+-------
+ tp_a | 2
+ tp_default | 2
+(2 rows)
+
+DROP TABLE t;
+DROP COLLATION case_insensitive;
+--
-- Test that the explicit partition bound cannot extend outside the split
-- partition's bound when a DEFAULT partition is specified.
--
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index d9821c5e2a3..ede89ad0228 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -834,6 +834,58 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
+--
+-- Test that SPLIT PARTITION rejects the degenerate case where the only
+-- non-DEFAULT replacement partition keeps the original bound and the command
+-- merely adds a DEFAULT partition.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_50 PARTITION OF t FOR VALUES FROM (0) TO (50);
+INSERT INTO t VALUES (1);
+
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_50 INTO
+ (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ PARTITION tp_default DEFAULT);
+
+DROP TABLE t;
+
+--
+-- Test that a LIST split with DEFAULT is not considered degenerate when
+-- only NULL is removed from the explicit replacement partition.
+--
+CREATE TABLE t (i int) PARTITION BY LIST (i);
+CREATE TABLE tp_null_1 PARTITION OF t FOR VALUES IN (NULL, 1);
+
+ALTER TABLE t SPLIT PARTITION tp_null_1 INTO
+ (PARTITION tp_1 FOR VALUES IN (1),
+ PARTITION tp_default DEFAULT);
+
+INSERT INTO t VALUES (NULL), (1), (2);
+SELECT tableoid::regclass, i FROM t ORDER BY tableoid::regclass::text COLLATE "C", i NULLS FIRST;
+
+DROP TABLE t;
+
+--
+-- Test that the same-bound check for LIST partitioning uses partition
+-- comparison semantics, not raw list length. The case-insensitive collation
+-- treats 'a' and 'A' as equal, so the non-DEFAULT replacement partition
+-- covers only the 'a' group and the DEFAULT partition covers the rest.
+--
+CREATE COLLATION case_insensitive (provider = icu, locale = 'und-u-ks-level2', deterministic = false);
+CREATE TABLE t (b text COLLATE case_insensitive) PARTITION BY LIST (b);
+CREATE TABLE tp_ab PARTITION OF t FOR VALUES IN ('a', 'b');
+
+ALTER TABLE t SPLIT PARTITION tp_ab INTO
+ (PARTITION tp_a FOR VALUES IN ('a', 'A'),
+ PARTITION tp_default DEFAULT);
+
+INSERT INTO t VALUES ('a'), ('A'), ('b'), ('c');
+SELECT tableoid::regclass, count(*) FROM t GROUP BY 1 ORDER BY 1;
+
+DROP TABLE t;
+DROP COLLATION case_insensitive;
+
--
-- Test that the explicit partition bound cannot extend outside the split
-- partition's bound when a DEFAULT partition is specified.
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-13 20:47 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Dmitry Koval <[email protected]>
2026-05-14 06:58 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 09:16 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-18 11:56 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 12:04 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-19 02:50 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-19 11:00 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
@ 2026-05-19 23:36 ` Chao Li <[email protected]>
2026-05-20 06:19 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Chao Li @ 2026-05-19 23:36 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: Dmitry Koval <[email protected]>; pgsql-hackers
> On May 19, 2026, at 19:00, Alexander Korotkov <[email protected]> wrote:
>
> Hi, Chao!
>
> On Tue, May 19, 2026 at 5:50 AM Chao Li <[email protected]> wrote:
>>> On May 18, 2026, at 20:04, Alexander Korotkov <[email protected]> wrote:
>>>
>>> On Mon, May 18, 2026 at 2:57 PM Chao Li <[email protected]> wrote:
>>>>> <v3-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch><v3-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch><v3-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch><v3-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch>
>>>>
>>>> v3-0001 through v3-0003 look good to me.
>>>>
>>>> For v3-0004, I have a suspicion, but it's late here and my brain is getting slow, so I would like to study it more tomorrow.
>>>
>>> Sure, take your time.
>>>
>>> ------
>>> Regards,
>>> Alexander Korotkov
>>> Supabase
>>
>> My suspicion was that check_split_partition_not_same_bound() now has two paths. The RANGE path honors collation, while the LIST path does not. So I spent some time creating a test that uses a case-insensitive collation:
>> ```
>> evantest=# create collation case_insensitive (provider=icu, locale='und-u-ks-level2', deterministic = false);
>> CREATE COLLATION
>> evantest=# create table t (b text collate case_insensitive) partition by list (b);
>> CREATE TABLE
>> evantest=# create table tp_ab partition of t for values in ('a', 'b');
>> CREATE TABLE
>> evantest=# alter table t split partition tp_ab into
>> evantest-# (partition tp_a for values in ('a', 'A'),
>> evantest(# partition tp_default default);
>> ERROR: cannot split partition "tp_ab" only to add a DEFAULT partition
>> LINE 2: (partition tp_a for values in ('a', 'A'),
>> ^
>> DETAIL: The non-DEFAULT partition would keep the same partition bound.
>> HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
>> ```
>>
>> In this test, the split partition’s bound is ('a', 'b'), and the new partition’s bound is ('a', 'A'). Their list lengths are both 2, but the two bounds are actually different, because 'a' and 'A' are considered equal by the collation.
>>
>> So, in the LIST path, since check_partition_bounds_for_split_list() has already ensured that the new partition’s bound is contained within the split partition’s bound, we need to check the reverse direction as well. Whether the split partition’s bound is also contained in the new partition’s bound. If yes, the two bounds are identical.
>>
>> See the attached v4 for my changes for 0004. 0001-0003 are unchanged. Since 0001 and 0003 are independent of 0004, maybe they can be pushed first.
>
> I've pushed 0001-0003.
Thanks for pushing them.
> Thank you for discovering the collation issue
> in 0004. Note that original approach of using
> partition_bounds_equal() can't handle different collations too (as it
> internally uses datumIsEqual()).
Yes, I realized that while reviewing v3. That’s reason I didn’t get back v2 and only worked again based on v3.
> I've revised the remaining patch:
> made function header comment a bit more detailed
This part looks good to me.
> and added additional
> regression tests. Please, check.
>
But I don’t see any change for regression test between v4 and v5. Maybe you forgot to save your changes?
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-13 20:47 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Dmitry Koval <[email protected]>
2026-05-14 06:58 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 09:16 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-18 11:56 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 12:04 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-19 02:50 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-19 11:00 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-19 23:36 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
@ 2026-05-20 06:19 ` Alexander Korotkov <[email protected]>
2026-05-20 06:28 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Alexander Korotkov @ 2026-05-20 06:19 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Dmitry Koval <[email protected]>; pgsql-hackers
Hi, Chao!
On Wed, May 20, 2026 at 2:37 AM Chao Li <[email protected]> wrote:
> > On May 19, 2026, at 19:00, Alexander Korotkov <[email protected]> wrote:
> > On Tue, May 19, 2026 at 5:50 AM Chao Li <[email protected]> wrote:
> >>> On May 18, 2026, at 20:04, Alexander Korotkov <[email protected]> wrote:
> >>>
> >>> On Mon, May 18, 2026 at 2:57 PM Chao Li <[email protected]> wrote:
> >>>>> <v3-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch><v3-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch><v3-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch><v3-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch>
> >>>>
> >>>> v3-0001 through v3-0003 look good to me.
> >>>>
> >>>> For v3-0004, I have a suspicion, but it's late here and my brain is getting slow, so I would like to study it more tomorrow.
> >>>
> >>> Sure, take your time.
> >>>
> >>> ------
> >>> Regards,
> >>> Alexander Korotkov
> >>> Supabase
> >>
> >> My suspicion was that check_split_partition_not_same_bound() now has two paths. The RANGE path honors collation, while the LIST path does not. So I spent some time creating a test that uses a case-insensitive collation:
> >> ```
> >> evantest=# create collation case_insensitive (provider=icu, locale='und-u-ks-level2', deterministic = false);
> >> CREATE COLLATION
> >> evantest=# create table t (b text collate case_insensitive) partition by list (b);
> >> CREATE TABLE
> >> evantest=# create table tp_ab partition of t for values in ('a', 'b');
> >> CREATE TABLE
> >> evantest=# alter table t split partition tp_ab into
> >> evantest-# (partition tp_a for values in ('a', 'A'),
> >> evantest(# partition tp_default default);
> >> ERROR: cannot split partition "tp_ab" only to add a DEFAULT partition
> >> LINE 2: (partition tp_a for values in ('a', 'A'),
> >> ^
> >> DETAIL: The non-DEFAULT partition would keep the same partition bound.
> >> HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
> >> ```
> >>
> >> In this test, the split partition’s bound is ('a', 'b'), and the new partition’s bound is ('a', 'A'). Their list lengths are both 2, but the two bounds are actually different, because 'a' and 'A' are considered equal by the collation.
> >>
> >> So, in the LIST path, since check_partition_bounds_for_split_list() has already ensured that the new partition’s bound is contained within the split partition’s bound, we need to check the reverse direction as well. Whether the split partition’s bound is also contained in the new partition’s bound. If yes, the two bounds are identical.
> >>
> >> See the attached v4 for my changes for 0004. 0001-0003 are unchanged. Since 0001 and 0003 are independent of 0004, maybe they can be pushed first.
> >
> > I've pushed 0001-0003.
>
> Thanks for pushing them.
>
> > Thank you for discovering the collation issue
> > in 0004. Note that original approach of using
> > partition_bounds_equal() can't handle different collations too (as it
> > internally uses datumIsEqual()).
>
> Yes, I realized that while reviewing v3. That’s reason I didn’t get back v2 and only worked again based on v3.
>
> > I've revised the remaining patch:
> > made function header comment a bit more detailed
>
> This part looks good to me.
>
> > and added additional
> > regression tests. Please, check.
> >
>
> But I don’t see any change for regression test between v4 and v5. Maybe you forgot to save your changes?
Sorry, I just mess up, no changes in tests.
I'm going to push this if no objection.
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-13 20:47 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Dmitry Koval <[email protected]>
2026-05-14 06:58 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 09:16 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-18 11:56 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 12:04 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-19 02:50 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-19 11:00 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-19 23:36 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-20 06:19 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
@ 2026-05-20 06:28 ` Chao Li <[email protected]>
2026-05-20 11:46 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Chao Li @ 2026-05-20 06:28 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: Dmitry Koval <[email protected]>; pgsql-hackers
> On May 20, 2026, at 14:19, Alexander Korotkov <[email protected]> wrote:
>
> Hi, Chao!
>
> On Wed, May 20, 2026 at 2:37 AM Chao Li <[email protected]> wrote:
>>> On May 19, 2026, at 19:00, Alexander Korotkov <[email protected]> wrote:
>>> On Tue, May 19, 2026 at 5:50 AM Chao Li <[email protected]> wrote:
>>>>> On May 18, 2026, at 20:04, Alexander Korotkov <[email protected]> wrote:
>>>>>
>>>>> On Mon, May 18, 2026 at 2:57 PM Chao Li <[email protected]> wrote:
>>>>>>> <v3-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch><v3-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch><v3-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch><v3-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch>
>>>>>>
>>>>>> v3-0001 through v3-0003 look good to me.
>>>>>>
>>>>>> For v3-0004, I have a suspicion, but it's late here and my brain is getting slow, so I would like to study it more tomorrow.
>>>>>
>>>>> Sure, take your time.
>>>>>
>>>>> ------
>>>>> Regards,
>>>>> Alexander Korotkov
>>>>> Supabase
>>>>
>>>> My suspicion was that check_split_partition_not_same_bound() now has two paths. The RANGE path honors collation, while the LIST path does not. So I spent some time creating a test that uses a case-insensitive collation:
>>>> ```
>>>> evantest=# create collation case_insensitive (provider=icu, locale='und-u-ks-level2', deterministic = false);
>>>> CREATE COLLATION
>>>> evantest=# create table t (b text collate case_insensitive) partition by list (b);
>>>> CREATE TABLE
>>>> evantest=# create table tp_ab partition of t for values in ('a', 'b');
>>>> CREATE TABLE
>>>> evantest=# alter table t split partition tp_ab into
>>>> evantest-# (partition tp_a for values in ('a', 'A'),
>>>> evantest(# partition tp_default default);
>>>> ERROR: cannot split partition "tp_ab" only to add a DEFAULT partition
>>>> LINE 2: (partition tp_a for values in ('a', 'A'),
>>>> ^
>>>> DETAIL: The non-DEFAULT partition would keep the same partition bound.
>>>> HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
>>>> ```
>>>>
>>>> In this test, the split partition’s bound is ('a', 'b'), and the new partition’s bound is ('a', 'A'). Their list lengths are both 2, but the two bounds are actually different, because 'a' and 'A' are considered equal by the collation.
>>>>
>>>> So, in the LIST path, since check_partition_bounds_for_split_list() has already ensured that the new partition’s bound is contained within the split partition’s bound, we need to check the reverse direction as well. Whether the split partition’s bound is also contained in the new partition’s bound. If yes, the two bounds are identical.
>>>>
>>>> See the attached v4 for my changes for 0004. 0001-0003 are unchanged. Since 0001 and 0003 are independent of 0004, maybe they can be pushed first.
>>>
>>> I've pushed 0001-0003.
>>
>> Thanks for pushing them.
>>
>>> Thank you for discovering the collation issue
>>> in 0004. Note that original approach of using
>>> partition_bounds_equal() can't handle different collations too (as it
>>> internally uses datumIsEqual()).
>>
>> Yes, I realized that while reviewing v3. That’s reason I didn’t get back v2 and only worked again based on v3.
>>
>>> I've revised the remaining patch:
>>> made function header comment a bit more detailed
>>
>> This part looks good to me.
>>
>>> and added additional
>>> regression tests. Please, check.
>>>
>>
>> But I don’t see any change for regression test between v4 and v5. Maybe you forgot to save your changes?
>
> Sorry, I just mess up, no changes in tests.
> I'm going to push this if no objection.
>
No worries. Then v5 looks good to me.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-13 20:47 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Dmitry Koval <[email protected]>
2026-05-14 06:58 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 09:16 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-18 11:56 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 12:04 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-19 02:50 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-19 11:00 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-19 23:36 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-20 06:19 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-20 06:28 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
@ 2026-05-20 11:46 ` Alexander Korotkov <[email protected]>
2026-05-20 20:29 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Alexander Korotkov @ 2026-05-20 11:46 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Dmitry Koval <[email protected]>; pgsql-hackers
On Wed, May 20, 2026 at 9:29 AM Chao Li <[email protected]> wrote:
> > On May 20, 2026, at 14:19, Alexander Korotkov <[email protected]> wrote:
> >
> > Hi, Chao!
> >
> > On Wed, May 20, 2026 at 2:37 AM Chao Li <[email protected]> wrote:
> >>> On May 19, 2026, at 19:00, Alexander Korotkov <[email protected]> wrote:
> >>> On Tue, May 19, 2026 at 5:50 AM Chao Li <[email protected]> wrote:
> >>>>> On May 18, 2026, at 20:04, Alexander Korotkov <[email protected]> wrote:
> >>>>>
> >>>>> On Mon, May 18, 2026 at 2:57 PM Chao Li <[email protected]> wrote:
> >>>>>>> <v3-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch><v3-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch><v3-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch><v3-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch>
> >>>>>>
> >>>>>> v3-0001 through v3-0003 look good to me.
> >>>>>>
> >>>>>> For v3-0004, I have a suspicion, but it's late here and my brain is getting slow, so I would like to study it more tomorrow.
> >>>>>
> >>>>> Sure, take your time.
> >>>>>
> >>>>> ------
> >>>>> Regards,
> >>>>> Alexander Korotkov
> >>>>> Supabase
> >>>>
> >>>> My suspicion was that check_split_partition_not_same_bound() now has two paths. The RANGE path honors collation, while the LIST path does not. So I spent some time creating a test that uses a case-insensitive collation:
> >>>> ```
> >>>> evantest=# create collation case_insensitive (provider=icu, locale='und-u-ks-level2', deterministic = false);
> >>>> CREATE COLLATION
> >>>> evantest=# create table t (b text collate case_insensitive) partition by list (b);
> >>>> CREATE TABLE
> >>>> evantest=# create table tp_ab partition of t for values in ('a', 'b');
> >>>> CREATE TABLE
> >>>> evantest=# alter table t split partition tp_ab into
> >>>> evantest-# (partition tp_a for values in ('a', 'A'),
> >>>> evantest(# partition tp_default default);
> >>>> ERROR: cannot split partition "tp_ab" only to add a DEFAULT partition
> >>>> LINE 2: (partition tp_a for values in ('a', 'A'),
> >>>> ^
> >>>> DETAIL: The non-DEFAULT partition would keep the same partition bound.
> >>>> HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
> >>>> ```
> >>>>
> >>>> In this test, the split partition’s bound is ('a', 'b'), and the new partition’s bound is ('a', 'A'). Their list lengths are both 2, but the two bounds are actually different, because 'a' and 'A' are considered equal by the collation.
> >>>>
> >>>> So, in the LIST path, since check_partition_bounds_for_split_list() has already ensured that the new partition’s bound is contained within the split partition’s bound, we need to check the reverse direction as well. Whether the split partition’s bound is also contained in the new partition’s bound. If yes, the two bounds are identical.
> >>>>
> >>>> See the attached v4 for my changes for 0004. 0001-0003 are unchanged. Since 0001 and 0003 are independent of 0004, maybe they can be pushed first.
> >>>
> >>> I've pushed 0001-0003.
> >>
> >> Thanks for pushing them.
> >>
> >>> Thank you for discovering the collation issue
> >>> in 0004. Note that original approach of using
> >>> partition_bounds_equal() can't handle different collations too (as it
> >>> internally uses datumIsEqual()).
> >>
> >> Yes, I realized that while reviewing v3. That’s reason I didn’t get back v2 and only worked again based on v3.
> >>
> >>> I've revised the remaining patch:
> >>> made function header comment a bit more detailed
> >>
> >> This part looks good to me.
> >>
> >>> and added additional
> >>> regression tests. Please, check.
> >>>
> >>
> >> But I don’t see any change for regression test between v4 and v5. Maybe you forgot to save your changes?
> >
> > Sorry, I just mess up, no changes in tests.
> > I'm going to push this if no objection.
> >
>
> No worries. Then v5 looks good to me.
Thank you, pushed.
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-13 20:47 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Dmitry Koval <[email protected]>
2026-05-14 06:58 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 09:16 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-18 11:56 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 12:04 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-19 02:50 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-19 11:00 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-19 23:36 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-20 06:19 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-20 06:28 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-20 11:46 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
@ 2026-05-20 20:29 ` Alexander Korotkov <[email protected]>
2026-05-20 21:17 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Alexander Korotkov @ 2026-05-20 20:29 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Dmitry Koval <[email protected]>; pgsql-hackers
On Wed, May 20, 2026 at 2:46 PM Alexander Korotkov <[email protected]> wrote:
> On Wed, May 20, 2026 at 9:29 AM Chao Li <[email protected]> wrote:
> > > On May 20, 2026, at 14:19, Alexander Korotkov <[email protected]> wrote:
> > > On Wed, May 20, 2026 at 2:37 AM Chao Li <[email protected]> wrote:
> > >>> On May 19, 2026, at 19:00, Alexander Korotkov <[email protected]> wrote:
> > >>> On Tue, May 19, 2026 at 5:50 AM Chao Li <[email protected]> wrote:
> > >>>>> On May 18, 2026, at 20:04, Alexander Korotkov <[email protected]> wrote:
> > >>>>>
> > >>>>> On Mon, May 18, 2026 at 2:57 PM Chao Li <[email protected]> wrote:
> > >>>>>>> <v3-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch><v3-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch><v3-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch><v3-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch>
> > >>>>>>
> > >>>>>> v3-0001 through v3-0003 look good to me.
> > >>>>>>
> > >>>>>> For v3-0004, I have a suspicion, but it's late here and my brain is getting slow, so I would like to study it more tomorrow.
> > >>>>>
> > >>>>> Sure, take your time.
> > >>>>>
> > >>>>> ------
> > >>>>> Regards,
> > >>>>> Alexander Korotkov
> > >>>>> Supabase
> > >>>>
> > >>>> My suspicion was that check_split_partition_not_same_bound() now has two paths. The RANGE path honors collation, while the LIST path does not. So I spent some time creating a test that uses a case-insensitive collation:
> > >>>> ```
> > >>>> evantest=# create collation case_insensitive (provider=icu, locale='und-u-ks-level2', deterministic = false);
> > >>>> CREATE COLLATION
> > >>>> evantest=# create table t (b text collate case_insensitive) partition by list (b);
> > >>>> CREATE TABLE
> > >>>> evantest=# create table tp_ab partition of t for values in ('a', 'b');
> > >>>> CREATE TABLE
> > >>>> evantest=# alter table t split partition tp_ab into
> > >>>> evantest-# (partition tp_a for values in ('a', 'A'),
> > >>>> evantest(# partition tp_default default);
> > >>>> ERROR: cannot split partition "tp_ab" only to add a DEFAULT partition
> > >>>> LINE 2: (partition tp_a for values in ('a', 'A'),
> > >>>> ^
> > >>>> DETAIL: The non-DEFAULT partition would keep the same partition bound.
> > >>>> HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
> > >>>> ```
> > >>>>
> > >>>> In this test, the split partition’s bound is ('a', 'b'), and the new partition’s bound is ('a', 'A'). Their list lengths are both 2, but the two bounds are actually different, because 'a' and 'A' are considered equal by the collation.
> > >>>>
> > >>>> So, in the LIST path, since check_partition_bounds_for_split_list() has already ensured that the new partition’s bound is contained within the split partition’s bound, we need to check the reverse direction as well. Whether the split partition’s bound is also contained in the new partition’s bound. If yes, the two bounds are identical.
> > >>>>
> > >>>> See the attached v4 for my changes for 0004. 0001-0003 are unchanged. Since 0001 and 0003 are independent of 0004, maybe they can be pushed first.
> > >>>
> > >>> I've pushed 0001-0003.
> > >>
> > >> Thanks for pushing them.
> > >>
> > >>> Thank you for discovering the collation issue
> > >>> in 0004. Note that original approach of using
> > >>> partition_bounds_equal() can't handle different collations too (as it
> > >>> internally uses datumIsEqual()).
> > >>
> > >> Yes, I realized that while reviewing v3. That’s reason I didn’t get back v2 and only worked again based on v3.
> > >>
> > >>> I've revised the remaining patch:
> > >>> made function header comment a bit more detailed
> > >>
> > >> This part looks good to me.
> > >>
> > >>> and added additional
> > >>> regression tests. Please, check.
> > >>>
> > >>
> > >> But I don’t see any change for regression test between v4 and v5. Maybe you forgot to save your changes?
> > >
> > > Sorry, I just mess up, no changes in tests.
> > > I'm going to push this if no objection.
> > >
> >
> > No worries. Then v5 looks good to me.
>
> Thank you, pushed.
Uhhh, most of buildfarm animals don't support locales used in our
tests. I've to revert that,
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-13 20:47 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Dmitry Koval <[email protected]>
2026-05-14 06:58 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 09:16 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-18 11:56 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 12:04 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-19 02:50 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-19 11:00 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-19 23:36 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-20 06:19 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-20 06:28 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-20 11:46 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-20 20:29 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
@ 2026-05-20 21:17 ` Alexander Korotkov <[email protected]>
2026-05-20 23:22 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Alexander Korotkov @ 2026-05-20 21:17 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Dmitry Koval <[email protected]>; pgsql-hackers
On Wed, May 20, 2026 at 11:29 PM Alexander Korotkov
<[email protected]> wrote:
> On Wed, May 20, 2026 at 2:46 PM Alexander Korotkov <[email protected]> wrote:
> > On Wed, May 20, 2026 at 9:29 AM Chao Li <[email protected]> wrote:
> > > > On May 20, 2026, at 14:19, Alexander Korotkov <[email protected]> wrote:
> > > > On Wed, May 20, 2026 at 2:37 AM Chao Li <[email protected]> wrote:
> > > >>> On May 19, 2026, at 19:00, Alexander Korotkov <[email protected]> wrote:
> > > >>> On Tue, May 19, 2026 at 5:50 AM Chao Li <[email protected]> wrote:
> > > >>>>> On May 18, 2026, at 20:04, Alexander Korotkov <[email protected]> wrote:
> > > >>>>>
> > > >>>>> On Mon, May 18, 2026 at 2:57 PM Chao Li <[email protected]> wrote:
> > > >>>>>>> <v3-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch><v3-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch><v3-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch><v3-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch>
> > > >>>>>>
> > > >>>>>> v3-0001 through v3-0003 look good to me.
> > > >>>>>>
> > > >>>>>> For v3-0004, I have a suspicion, but it's late here and my brain is getting slow, so I would like to study it more tomorrow.
> > > >>>>>
> > > >>>>> Sure, take your time.
> > > >>>>>
> > > >>>>> ------
> > > >>>>> Regards,
> > > >>>>> Alexander Korotkov
> > > >>>>> Supabase
> > > >>>>
> > > >>>> My suspicion was that check_split_partition_not_same_bound() now has two paths. The RANGE path honors collation, while the LIST path does not. So I spent some time creating a test that uses a case-insensitive collation:
> > > >>>> ```
> > > >>>> evantest=# create collation case_insensitive (provider=icu, locale='und-u-ks-level2', deterministic = false);
> > > >>>> CREATE COLLATION
> > > >>>> evantest=# create table t (b text collate case_insensitive) partition by list (b);
> > > >>>> CREATE TABLE
> > > >>>> evantest=# create table tp_ab partition of t for values in ('a', 'b');
> > > >>>> CREATE TABLE
> > > >>>> evantest=# alter table t split partition tp_ab into
> > > >>>> evantest-# (partition tp_a for values in ('a', 'A'),
> > > >>>> evantest(# partition tp_default default);
> > > >>>> ERROR: cannot split partition "tp_ab" only to add a DEFAULT partition
> > > >>>> LINE 2: (partition tp_a for values in ('a', 'A'),
> > > >>>> ^
> > > >>>> DETAIL: The non-DEFAULT partition would keep the same partition bound.
> > > >>>> HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
> > > >>>> ```
> > > >>>>
> > > >>>> In this test, the split partition’s bound is ('a', 'b'), and the new partition’s bound is ('a', 'A'). Their list lengths are both 2, but the two bounds are actually different, because 'a' and 'A' are considered equal by the collation.
> > > >>>>
> > > >>>> So, in the LIST path, since check_partition_bounds_for_split_list() has already ensured that the new partition’s bound is contained within the split partition’s bound, we need to check the reverse direction as well. Whether the split partition’s bound is also contained in the new partition’s bound. If yes, the two bounds are identical.
> > > >>>>
> > > >>>> See the attached v4 for my changes for 0004. 0001-0003 are unchanged. Since 0001 and 0003 are independent of 0004, maybe they can be pushed first.
> > > >>>
> > > >>> I've pushed 0001-0003.
> > > >>
> > > >> Thanks for pushing them.
> > > >>
> > > >>> Thank you for discovering the collation issue
> > > >>> in 0004. Note that original approach of using
> > > >>> partition_bounds_equal() can't handle different collations too (as it
> > > >>> internally uses datumIsEqual()).
> > > >>
> > > >> Yes, I realized that while reviewing v3. That’s reason I didn’t get back v2 and only worked again based on v3.
> > > >>
> > > >>> I've revised the remaining patch:
> > > >>> made function header comment a bit more detailed
> > > >>
> > > >> This part looks good to me.
> > > >>
> > > >>> and added additional
> > > >>> regression tests. Please, check.
> > > >>>
> > > >>
> > > >> But I don’t see any change for regression test between v4 and v5. Maybe you forgot to save your changes?
> > > >
> > > > Sorry, I just mess up, no changes in tests.
> > > > I'm going to push this if no objection.
> > > >
> > >
> > > No worries. Then v5 looks good to me.
> >
> > Thank you, pushed.
>
> Uhhh, most of buildfarm animals don't support locales used in our
> tests. I've to revert that,
The another attempt is attached. Now use -0.0 and 0.0 as binary
different but logically equivalent values, no locale dependence.
------
Regards,
Alexander Korotkov
Supabase
Attachments:
[application/octet-stream] v6-0001-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch (12.7K, 2-v6-0001-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch)
download | inline diff:
From 7d8d3e2fe5150837db3af1b59bb7e6922ca30618 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <[email protected]>
Date: Mon, 18 May 2026 00:37:52 +0300
Subject: [PATCH v6] Reject degenerate SPLIT PARTITION with DEFAULT partition
ALTER TABLE ... SPLIT PARTITION allows a DEFAULT partition to be created
as one of the replacement partitions when the parent table does not
already have one. However, it should not allow the degenerate case where
a non-DEFAULT partition keeps exactly the same bound as the split
partition and the command merely adds a DEFAULT partition through the
SPLIT PARTITION path.
Detect that case by comparing the bound of the split partition with the
bound of the only non-DEFAULT replacement partition, and raise an error
when they are the same. Users should add a DEFAULT partition directly
with CREATE TABLE ... PARTITION OF ... DEFAULT or ALTER TABLE ... ATTACH
PARTITION ... DEFAULT instead.
The comparison goes through the partition operator family rather than
byte equality so that values which are binary-different but compare
equal under the partition key's comparator are treated as the same
bound. The corresponding regression test uses a float8 LIST partition
with -0.0 and 0.0 -- they have different bit patterns but are equal
under float8 -- to verify that a datumIsEqual()-based check would let
the degenerate split through while the partsupfunc-based check
correctly rejects it.
Author: Chao Li <[email protected]>
Reviewed-by: Alexander Korotkov <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/partitioning/partbounds.c | 150 ++++++++++++++++++
src/test/regress/expected/partition_split.out | 58 +++++++
src/test/regress/sql/partition_split.sql | 51 ++++++
3 files changed, 259 insertions(+)
diff --git a/src/backend/partitioning/partbounds.c b/src/backend/partitioning/partbounds.c
index 7d3580cbc10..6fb150a8763 100644
--- a/src/backend/partitioning/partbounds.c
+++ b/src/backend/partitioning/partbounds.c
@@ -5700,6 +5700,146 @@ check_parent_values_in_new_partitions(Relation parent,
}
}
+/*
+ * split_partition_values_contained_in_new_part
+ *
+ * (function for BY LIST partitioning)
+ *
+ * Returns true if all values in the LIST bound of the partition being split
+ * are contained in the specified non-DEFAULT replacement partition's bound.
+ *
+ * The caller must already have verified containment in the other direction,
+ * so this check is sufficient to prove that the two LIST bounds are equal.
+ */
+static bool
+split_partition_values_contained_in_new_part(Relation parent,
+ Oid splitPartOid,
+ SinglePartitionSpec *part)
+{
+ PartitionKey key = RelationGetPartitionKey(parent);
+ PartitionDesc partdesc = RelationGetPartitionDesc(parent, false);
+ PartitionBoundInfo boundinfo = partdesc->boundinfo;
+ SinglePartitionSpec *parts[1];
+ Datum datum = PointerGetDatum(NULL);
+
+ Assert(key->strategy == PARTITION_STRATEGY_LIST);
+
+ parts[0] = part;
+
+ /*
+ * Special processing for NULL value. Search for a NULL value if the
+ * split partition contains it.
+ */
+ if (partition_bound_accepts_nulls(boundinfo) &&
+ partdesc->oids[boundinfo->null_index] == splitPartOid)
+ {
+ if (!find_value_in_new_partitions_list(&key->partsupfunc[0],
+ key->partcollation, parts, 1,
+ datum, true))
+ return false;
+ }
+
+ /*
+ * Search all values of the split partition in the single non-DEFAULT
+ * replacement partition.
+ */
+ for (int i = 0; i < boundinfo->ndatums; i++)
+ {
+ if (partdesc->oids[boundinfo->indexes[i]] == splitPartOid)
+ {
+ datum = boundinfo->datums[i][0];
+
+ if (!find_value_in_new_partitions_list(&key->partsupfunc[0],
+ key->partcollation, parts, 1,
+ datum, false))
+ return false;
+ }
+ }
+
+ return true;
+}
+
+/*
+ * check_split_partition_not_same_bound
+ *
+ * Reject splitting a non-DEFAULT partition into one non-DEFAULT partition
+ * with the original bound plus a DEFAULT partition. That form does not
+ * perform a real split; it merely adds a DEFAULT partition to the parent
+ * table through the split-partition path. Users should use
+ * CREATE TABLE ... PARTITION OF ... DEFAULT or ALTER TABLE ... ATTACH
+ * PARTITION ... DEFAULT for that.
+ *
+ * Must be called after the per-partition bound validation in
+ * check_partitions_for_split() so that containment of new bounds within the
+ * split partition is already established. Given containment, RANGE bounds
+ * are equal iff their lower and upper rbounds match; LIST bound sets are
+ * equal iff the split partition's values are also contained in the new
+ * partition (the containment is then bidirectional). Both checks go
+ * through the partition operator family (partition_rbound_cmp /
+ * find_value_in_new_partitions_list) rather than byte equality, so e.g.
+ * -0.0 and 0.0 -- which have different bit patterns but compare equal
+ * under float8 -- are correctly recognised as the same bound.
+ */
+static void
+check_split_partition_not_same_bound(Relation parent,
+ Oid splitPartOid,
+ SinglePartitionSpec **parts,
+ int nparts,
+ ParseState *pstate)
+{
+ PartitionKey key = RelationGetPartitionKey(parent);
+ PartitionBoundSpec *new_spec;
+ PartitionBoundSpec *split_spec;
+
+ if (nparts != 1)
+ return;
+
+ new_spec = parts[0]->bound;
+ split_spec = get_partition_bound_spec(splitPartOid);
+
+ Assert(new_spec->strategy == split_spec->strategy);
+
+ if (key->strategy == PARTITION_STRATEGY_RANGE)
+ {
+ PartitionRangeBound *new_lower;
+ PartitionRangeBound *new_upper;
+ PartitionRangeBound *split_lower;
+ PartitionRangeBound *split_upper;
+
+ new_lower = make_one_partition_rbound(key, -1, new_spec->lowerdatums, true);
+ new_upper = make_one_partition_rbound(key, -1, new_spec->upperdatums, false);
+ split_lower = make_one_partition_rbound(key, -1, split_spec->lowerdatums, true);
+ split_upper = make_one_partition_rbound(key, -1, split_spec->upperdatums, false);
+
+ if (partition_rbound_cmp(key->partnatts, key->partsupfunc,
+ key->partcollation,
+ new_lower->datums, new_lower->kind, true,
+ split_lower) != 0)
+ return;
+ if (partition_rbound_cmp(key->partnatts, key->partsupfunc,
+ key->partcollation,
+ new_upper->datums, new_upper->kind, false,
+ split_upper) != 0)
+ return;
+ }
+ else
+ {
+ Assert(key->strategy == PARTITION_STRATEGY_LIST);
+
+ if (!split_partition_values_contained_in_new_part(parent, splitPartOid,
+ parts[0]))
+ return;
+ }
+
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("cannot split partition \"%s\" only to add a DEFAULT partition",
+ get_rel_name(splitPartOid)),
+ errdetail("The non-DEFAULT partition would keep the same partition bound."),
+ errhint("Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition."),
+ parser_errposition(pstate, parts[0]->name->location));
+}
+
/*
* check_partitions_for_split
*
@@ -5871,5 +6011,15 @@ check_partitions_for_split(Relation parent,
new_parts, nparts, pstate);
}
+ /*
+ * Reject the degenerate form where the single non-DEFAULT replacement
+ * partition keeps the bound of the split partition; the command then does
+ * nothing beyond adding a DEFAULT partition. Containment was established
+ * by the per-partition validation above, so an equality check is enough.
+ */
+ if (!isSplitPartDefault && createDefaultPart)
+ check_split_partition_not_same_bound(parent, splitPartOid, new_parts,
+ nparts, pstate);
+
pfree(new_parts);
}
diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out
index 2b9a6aa50ed..ff6027af658 100644
--- a/src/test/regress/expected/partition_split.out
+++ b/src/test/regress/expected/partition_split.out
@@ -1188,6 +1188,64 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
--
+-- Test that SPLIT PARTITION rejects the degenerate case where the only
+-- non-DEFAULT replacement partition keeps the original bound and the command
+-- merely adds a DEFAULT partition.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_50 PARTITION OF t FOR VALUES FROM (0) TO (50);
+INSERT INTO t VALUES (1);
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_50 INTO
+ (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ PARTITION tp_default DEFAULT);
+ERROR: cannot split partition "tp_0_50" only to add a DEFAULT partition
+LINE 2: (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ ^
+DETAIL: The non-DEFAULT partition would keep the same partition bound.
+HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
+DROP TABLE t;
+--
+-- Test that a LIST split with DEFAULT is not considered degenerate when
+-- only NULL is removed from the explicit replacement partition.
+--
+CREATE TABLE t (i int) PARTITION BY LIST (i);
+CREATE TABLE tp_null_1 PARTITION OF t FOR VALUES IN (NULL, 1);
+ALTER TABLE t SPLIT PARTITION tp_null_1 INTO
+ (PARTITION tp_1 FOR VALUES IN (1),
+ PARTITION tp_default DEFAULT);
+INSERT INTO t VALUES (NULL), (1), (2);
+SELECT tableoid::regclass, i FROM t ORDER BY tableoid::regclass::text COLLATE "C", i NULLS FIRST;
+ tableoid | i
+------------+---
+ tp_1 | 1
+ tp_default |
+ tp_default | 2
+(3 rows)
+
+DROP TABLE t;
+--
+-- Test that the same-bound check for LIST partitioning uses the
+-- partition operator family, not byte equality. -0.0 and 0.0 have
+-- different bit patterns but compare equal under float8, so the
+-- replacement bound (-0.0, 1.0) is the same set as the original
+-- (0.0, 1.0) and the SPLIT is degenerate. A datumIsEqual()-based
+-- check would let this through; the partsupfunc-based check correctly
+-- rejects it.
+--
+CREATE TABLE t (v float8) PARTITION BY LIST (v);
+CREATE TABLE tp_zero_one PARTITION OF t FOR VALUES IN (0.0, 1.0);
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_zero_one INTO
+ (PARTITION tp_zero_one FOR VALUES IN (-0.0, 1.0),
+ PARTITION tp_default DEFAULT);
+ERROR: cannot split partition "tp_zero_one" only to add a DEFAULT partition
+LINE 2: (PARTITION tp_zero_one FOR VALUES IN (-0.0, 1.0),
+ ^
+DETAIL: The non-DEFAULT partition would keep the same partition bound.
+HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
+DROP TABLE t;
+--
-- Test that the explicit partition bound cannot extend outside the split
-- partition's bound when a DEFAULT partition is specified.
--
diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql
index d9821c5e2a3..05de24152d1 100644
--- a/src/test/regress/sql/partition_split.sql
+++ b/src/test/regress/sql/partition_split.sql
@@ -834,6 +834,57 @@ SELECT tableoid::regclass, * FROM sales_range ORDER BY tableoid::regclass::text
DROP TABLE sales_range;
+--
+-- Test that SPLIT PARTITION rejects the degenerate case where the only
+-- non-DEFAULT replacement partition keeps the original bound and the command
+-- merely adds a DEFAULT partition.
+--
+CREATE TABLE t (i int) PARTITION BY RANGE (i);
+CREATE TABLE tp_0_50 PARTITION OF t FOR VALUES FROM (0) TO (50);
+INSERT INTO t VALUES (1);
+
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_0_50 INTO
+ (PARTITION tp_0_50 FOR VALUES FROM (0) TO (50),
+ PARTITION tp_default DEFAULT);
+
+DROP TABLE t;
+
+--
+-- Test that a LIST split with DEFAULT is not considered degenerate when
+-- only NULL is removed from the explicit replacement partition.
+--
+CREATE TABLE t (i int) PARTITION BY LIST (i);
+CREATE TABLE tp_null_1 PARTITION OF t FOR VALUES IN (NULL, 1);
+
+ALTER TABLE t SPLIT PARTITION tp_null_1 INTO
+ (PARTITION tp_1 FOR VALUES IN (1),
+ PARTITION tp_default DEFAULT);
+
+INSERT INTO t VALUES (NULL), (1), (2);
+SELECT tableoid::regclass, i FROM t ORDER BY tableoid::regclass::text COLLATE "C", i NULLS FIRST;
+
+DROP TABLE t;
+
+--
+-- Test that the same-bound check for LIST partitioning uses the
+-- partition operator family, not byte equality. -0.0 and 0.0 have
+-- different bit patterns but compare equal under float8, so the
+-- replacement bound (-0.0, 1.0) is the same set as the original
+-- (0.0, 1.0) and the SPLIT is degenerate. A datumIsEqual()-based
+-- check would let this through; the partsupfunc-based check correctly
+-- rejects it.
+--
+CREATE TABLE t (v float8) PARTITION BY LIST (v);
+CREATE TABLE tp_zero_one PARTITION OF t FOR VALUES IN (0.0, 1.0);
+
+-- ERROR
+ALTER TABLE t SPLIT PARTITION tp_zero_one INTO
+ (PARTITION tp_zero_one FOR VALUES IN (-0.0, 1.0),
+ PARTITION tp_default DEFAULT);
+
+DROP TABLE t;
+
--
-- Test that the explicit partition bound cannot extend outside the split
-- partition's bound when a DEFAULT partition is specified.
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-13 20:47 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Dmitry Koval <[email protected]>
2026-05-14 06:58 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 09:16 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-18 11:56 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 12:04 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-19 02:50 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-19 11:00 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-19 23:36 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-20 06:19 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-20 06:28 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-20 11:46 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-20 20:29 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-20 21:17 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
@ 2026-05-20 23:22 ` Chao Li <[email protected]>
2026-05-25 17:41 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Chao Li @ 2026-05-20 23:22 UTC (permalink / raw)
To: Alexander Korotkov <[email protected]>; +Cc: Dmitry Koval <[email protected]>; pgsql-hackers
> On May 21, 2026, at 05:17, Alexander Korotkov <[email protected]> wrote:
>
> On Wed, May 20, 2026 at 11:29 PM Alexander Korotkov
> <[email protected]> wrote:
>> On Wed, May 20, 2026 at 2:46 PM Alexander Korotkov <[email protected]> wrote:
>>> On Wed, May 20, 2026 at 9:29 AM Chao Li <[email protected]> wrote:
>>>>> On May 20, 2026, at 14:19, Alexander Korotkov <[email protected]> wrote:
>>>>> On Wed, May 20, 2026 at 2:37 AM Chao Li <[email protected]> wrote:
>>>>>>> On May 19, 2026, at 19:00, Alexander Korotkov <[email protected]> wrote:
>>>>>>> On Tue, May 19, 2026 at 5:50 AM Chao Li <[email protected]> wrote:
>>>>>>>>> On May 18, 2026, at 20:04, Alexander Korotkov <[email protected]> wrote:
>>>>>>>>>
>>>>>>>>> On Mon, May 18, 2026 at 2:57 PM Chao Li <[email protected]> wrote:
>>>>>>>>>>> <v3-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch><v3-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch><v3-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch><v3-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch>
>>>>>>>>>>
>>>>>>>>>> v3-0001 through v3-0003 look good to me.
>>>>>>>>>>
>>>>>>>>>> For v3-0004, I have a suspicion, but it's late here and my brain is getting slow, so I would like to study it more tomorrow.
>>>>>>>>>
>>>>>>>>> Sure, take your time.
>>>>>>>>>
>>>>>>>>> ------
>>>>>>>>> Regards,
>>>>>>>>> Alexander Korotkov
>>>>>>>>> Supabase
>>>>>>>>
>>>>>>>> My suspicion was that check_split_partition_not_same_bound() now has two paths. The RANGE path honors collation, while the LIST path does not. So I spent some time creating a test that uses a case-insensitive collation:
>>>>>>>> ```
>>>>>>>> evantest=# create collation case_insensitive (provider=icu, locale='und-u-ks-level2', deterministic = false);
>>>>>>>> CREATE COLLATION
>>>>>>>> evantest=# create table t (b text collate case_insensitive) partition by list (b);
>>>>>>>> CREATE TABLE
>>>>>>>> evantest=# create table tp_ab partition of t for values in ('a', 'b');
>>>>>>>> CREATE TABLE
>>>>>>>> evantest=# alter table t split partition tp_ab into
>>>>>>>> evantest-# (partition tp_a for values in ('a', 'A'),
>>>>>>>> evantest(# partition tp_default default);
>>>>>>>> ERROR: cannot split partition "tp_ab" only to add a DEFAULT partition
>>>>>>>> LINE 2: (partition tp_a for values in ('a', 'A'),
>>>>>>>> ^
>>>>>>>> DETAIL: The non-DEFAULT partition would keep the same partition bound.
>>>>>>>> HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
>>>>>>>> ```
>>>>>>>>
>>>>>>>> In this test, the split partition’s bound is ('a', 'b'), and the new partition’s bound is ('a', 'A'). Their list lengths are both 2, but the two bounds are actually different, because 'a' and 'A' are considered equal by the collation.
>>>>>>>>
>>>>>>>> So, in the LIST path, since check_partition_bounds_for_split_list() has already ensured that the new partition’s bound is contained within the split partition’s bound, we need to check the reverse direction as well. Whether the split partition’s bound is also contained in the new partition’s bound. If yes, the two bounds are identical.
>>>>>>>>
>>>>>>>> See the attached v4 for my changes for 0004. 0001-0003 are unchanged. Since 0001 and 0003 are independent of 0004, maybe they can be pushed first.
>>>>>>>
>>>>>>> I've pushed 0001-0003.
>>>>>>
>>>>>> Thanks for pushing them.
>>>>>>
>>>>>>> Thank you for discovering the collation issue
>>>>>>> in 0004. Note that original approach of using
>>>>>>> partition_bounds_equal() can't handle different collations too (as it
>>>>>>> internally uses datumIsEqual()).
>>>>>>
>>>>>> Yes, I realized that while reviewing v3. That’s reason I didn’t get back v2 and only worked again based on v3.
>>>>>>
>>>>>>> I've revised the remaining patch:
>>>>>>> made function header comment a bit more detailed
>>>>>>
>>>>>> This part looks good to me.
>>>>>>
>>>>>>> and added additional
>>>>>>> regression tests. Please, check.
>>>>>>>
>>>>>>
>>>>>> But I don’t see any change for regression test between v4 and v5. Maybe you forgot to save your changes?
>>>>>
>>>>> Sorry, I just mess up, no changes in tests.
>>>>> I'm going to push this if no objection.
>>>>>
>>>>
>>>> No worries. Then v5 looks good to me.
>>>
>>> Thank you, pushed.
>>
>> Uhhh, most of buildfarm animals don't support locales used in our
>> tests. I've to revert that,
>
> The another attempt is attached. Now use -0.0 and 0.0 as binary
> different but logically equivalent values, no locale dependence.
>
> ------
> Regards,
> Alexander Korotkov
> Supabase
> <v6-0001-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch>
Thank you very much for taking care of that. This was a lesson learned for me.
Actually, when I added the ICU test, I did think about whether regression tests support ICU. So I searched the existing tests for “icu” and found some examples. For instance, src/test/regress/sql/collate.icu.utf8.sql has tests like:
```
RESET icu_validation_level;
CREATE COLLATION testx (provider = icu, locale = '@colStrength=primary;nonsense=yes'); DROP COLLATION testx;
CREATE COLLATION testx (provider = icu, locale = 'nonsense-nowhere'); DROP COLLATION testx;
```
I added v6 to CF to get a CI run and check whether the buildfarm is happy with v6. The CI test has passed now. See [1]. The new test using “-0.0" also looks good to me.
[1] https://commitfest.postgresql.org/patch/6796/
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: Fix SPLIT PARTITION bound-overlap bug and other improvements
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-13 20:47 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Dmitry Koval <[email protected]>
2026-05-14 06:58 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 09:16 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-18 11:56 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-18 12:04 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-19 02:50 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-19 11:00 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-19 23:36 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-20 06:19 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-20 06:28 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-20 11:46 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-20 20:29 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-20 21:17 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Alexander Korotkov <[email protected]>
2026-05-20 23:22 ` Re: Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
@ 2026-05-25 17:41 ` Alexander Korotkov <[email protected]>
0 siblings, 0 replies; 18+ messages in thread
From: Alexander Korotkov @ 2026-05-25 17:41 UTC (permalink / raw)
To: Chao Li <[email protected]>; +Cc: Dmitry Koval <[email protected]>; pgsql-hackers
On Thu, May 21, 2026 at 2:23 AM Chao Li <[email protected]> wrote:
>
> > On May 21, 2026, at 05:17, Alexander Korotkov <[email protected]> wrote:
> > On Wed, May 20, 2026 at 11:29 PM Alexander Korotkov
> > <[email protected]> wrote:
> >> On Wed, May 20, 2026 at 2:46 PM Alexander Korotkov <[email protected]> wrote:
> >>> On Wed, May 20, 2026 at 9:29 AM Chao Li <[email protected]> wrote:
> >>>>> On May 20, 2026, at 14:19, Alexander Korotkov <[email protected]> wrote:
> >>>>> On Wed, May 20, 2026 at 2:37 AM Chao Li <[email protected]> wrote:
> >>>>>>> On May 19, 2026, at 19:00, Alexander Korotkov <[email protected]> wrote:
> >>>>>>> On Tue, May 19, 2026 at 5:50 AM Chao Li <[email protected]> wrote:
> >>>>>>>>> On May 18, 2026, at 20:04, Alexander Korotkov <[email protected]> wrote:
> >>>>>>>>>
> >>>>>>>>> On Mon, May 18, 2026 at 2:57 PM Chao Li <[email protected]> wrote:
> >>>>>>>>>>> <v3-0003-Clarify-SPLIT-PARTITION-bound-requirements-in-doc.patch><v3-0001-Fix-SPLIT-PARTITION-range-bound-validation-with-D.patch><v3-0002-Fix-SPLIT-PARTITION-hint-for-DEFAULT-partition-bo.patch><v3-0004-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch>
> >>>>>>>>>>
> >>>>>>>>>> v3-0001 through v3-0003 look good to me.
> >>>>>>>>>>
> >>>>>>>>>> For v3-0004, I have a suspicion, but it's late here and my brain is getting slow, so I would like to study it more tomorrow.
> >>>>>>>>>
> >>>>>>>>> Sure, take your time.
> >>>>>>>>>
> >>>>>>>>> ------
> >>>>>>>>> Regards,
> >>>>>>>>> Alexander Korotkov
> >>>>>>>>> Supabase
> >>>>>>>>
> >>>>>>>> My suspicion was that check_split_partition_not_same_bound() now has two paths. The RANGE path honors collation, while the LIST path does not. So I spent some time creating a test that uses a case-insensitive collation:
> >>>>>>>> ```
> >>>>>>>> evantest=# create collation case_insensitive (provider=icu, locale='und-u-ks-level2', deterministic = false);
> >>>>>>>> CREATE COLLATION
> >>>>>>>> evantest=# create table t (b text collate case_insensitive) partition by list (b);
> >>>>>>>> CREATE TABLE
> >>>>>>>> evantest=# create table tp_ab partition of t for values in ('a', 'b');
> >>>>>>>> CREATE TABLE
> >>>>>>>> evantest=# alter table t split partition tp_ab into
> >>>>>>>> evantest-# (partition tp_a for values in ('a', 'A'),
> >>>>>>>> evantest(# partition tp_default default);
> >>>>>>>> ERROR: cannot split partition "tp_ab" only to add a DEFAULT partition
> >>>>>>>> LINE 2: (partition tp_a for values in ('a', 'A'),
> >>>>>>>> ^
> >>>>>>>> DETAIL: The non-DEFAULT partition would keep the same partition bound.
> >>>>>>>> HINT: Use CREATE TABLE ... PARTITION OF ... DEFAULT to add a DEFAULT partition.
> >>>>>>>> ```
> >>>>>>>>
> >>>>>>>> In this test, the split partition’s bound is ('a', 'b'), and the new partition’s bound is ('a', 'A'). Their list lengths are both 2, but the two bounds are actually different, because 'a' and 'A' are considered equal by the collation.
> >>>>>>>>
> >>>>>>>> So, in the LIST path, since check_partition_bounds_for_split_list() has already ensured that the new partition’s bound is contained within the split partition’s bound, we need to check the reverse direction as well. Whether the split partition’s bound is also contained in the new partition’s bound. If yes, the two bounds are identical.
> >>>>>>>>
> >>>>>>>> See the attached v4 for my changes for 0004. 0001-0003 are unchanged. Since 0001 and 0003 are independent of 0004, maybe they can be pushed first.
> >>>>>>>
> >>>>>>> I've pushed 0001-0003.
> >>>>>>
> >>>>>> Thanks for pushing them.
> >>>>>>
> >>>>>>> Thank you for discovering the collation issue
> >>>>>>> in 0004. Note that original approach of using
> >>>>>>> partition_bounds_equal() can't handle different collations too (as it
> >>>>>>> internally uses datumIsEqual()).
> >>>>>>
> >>>>>> Yes, I realized that while reviewing v3. That’s reason I didn’t get back v2 and only worked again based on v3.
> >>>>>>
> >>>>>>> I've revised the remaining patch:
> >>>>>>> made function header comment a bit more detailed
> >>>>>>
> >>>>>> This part looks good to me.
> >>>>>>
> >>>>>>> and added additional
> >>>>>>> regression tests. Please, check.
> >>>>>>>
> >>>>>>
> >>>>>> But I don’t see any change for regression test between v4 and v5. Maybe you forgot to save your changes?
> >>>>>
> >>>>> Sorry, I just mess up, no changes in tests.
> >>>>> I'm going to push this if no objection.
> >>>>>
> >>>>
> >>>> No worries. Then v5 looks good to me.
> >>>
> >>> Thank you, pushed.
> >>
> >> Uhhh, most of buildfarm animals don't support locales used in our
> >> tests. I've to revert that,
> >
> > The another attempt is attached. Now use -0.0 and 0.0 as binary
> > different but logically equivalent values, no locale dependence.
> >
> > ------
> > Regards,
> > Alexander Korotkov
> > Supabase
> > <v6-0001-Reject-degenerate-SPLIT-PARTITION-with-DEFAULT-pa.patch>
>
> Thank you very much for taking care of that. This was a lesson learned for me.
>
> Actually, when I added the ICU test, I did think about whether regression tests support ICU. So I searched the existing tests for “icu” and found some examples. For instance, src/test/regress/sql/collate.icu.utf8.sql has tests like:
> ```
> RESET icu_validation_level;
> CREATE COLLATION testx (provider = icu, locale = '@colStrength=primary;nonsense=yes'); DROP COLLATION testx;
> CREATE COLLATION testx (provider = icu, locale = 'nonsense-nowhere'); DROP COLLATION testx;
> ```
>
> I added v6 to CF to get a CI run and check whether the buildfarm is happy with v6. The CI test has passed now. See [1]. The new test using “-0.0" also looks good to me.
>
> [1] https://commitfest.postgresql.org/patch/6796/
Pushed, thank you. AFAICS, it runs ok on buildfarm now.
------
Regards,
Alexander Korotkov
Supabase
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2026-05-25 17:41 UTC | newest]
Thread overview: 18+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-13 04:38 Fix SPLIT PARTITION bound-overlap bug and other improvements Chao Li <[email protected]>
2026-05-13 05:07 ` Kirill Reshke <[email protected]>
2026-05-13 09:32 ` Zhenwei Shang <[email protected]>
2026-05-13 20:47 ` Dmitry Koval <[email protected]>
2026-05-14 06:58 ` Chao Li <[email protected]>
2026-05-18 09:16 ` Alexander Korotkov <[email protected]>
2026-05-18 11:56 ` Chao Li <[email protected]>
2026-05-18 12:04 ` Alexander Korotkov <[email protected]>
2026-05-19 02:50 ` Chao Li <[email protected]>
2026-05-19 11:00 ` Alexander Korotkov <[email protected]>
2026-05-19 23:36 ` Chao Li <[email protected]>
2026-05-20 06:19 ` Alexander Korotkov <[email protected]>
2026-05-20 06:28 ` Chao Li <[email protected]>
2026-05-20 11:46 ` Alexander Korotkov <[email protected]>
2026-05-20 20:29 ` Alexander Korotkov <[email protected]>
2026-05-20 21:17 ` Alexander Korotkov <[email protected]>
2026-05-20 23:22 ` Chao Li <[email protected]>
2026-05-25 17:41 ` Alexander Korotkov <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox