Help Center/ GaussDB(DWS)/ Troubleshooting/ Database Use/ Error upper boundary of adding partition MUST overtop last existing partition Is Reported When a New Partition Is Added to a Range Partitioned Table
Updated on 2024-03-08 GMT+08:00
View PDF

Error upper boundary of adding partition MUST overtop last existing partition Is Reported When a New Partition Is Added to a Range Partitioned Table

Symptom

Error upper boundary of adding partition MUST overtop last existing partition is reported when the ALTER TABLE ADD PARTITION statement is executed to add a range partition.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
 
- -- Create the range partitioned table studentinfo:
CREATE TABLE studentinfo (stuno smallint, sname varchar(20), score varchar(20), examate timestamp)
PARTITION BY RANGE (examate) (
   PARTITION p1 VALUES LESS THAN ('2022-10-10 00:00:00+08'),
   PARTITION p2 VALUES LESS THAN ('2022-10-11 00:00:00+08'),
   PARTITION p3 VALUES LESS THAN ('2022-10-12 00:00:00+08'),
   PARTITION p4 VALUES LESS THAN (maxvalue)
);
- -- Add partition p0 whose boundary value is 2022-10-9 00:00:00+08:
ALTER TABLE studentinfo ADD PARTITION p0 values less than ('2022-10-9 00:00:00+08');
ERROR:  the boundary of partition "p0" is less than previous partition's boundary
- -- Add partition p5 whose boundary value is 2022-10-13 00:00:00+08:
ALTER TABLE studentinfo ADD PARTITION p5 values less than ('2022-10-13 00:00:00+08');
ERROR:  the boundary of partition "p5" is equal to previous partition's boundary

Possible Causes

To add a partition, the following conditions must be met:

  • The name of a new partition must be different from that of an existing partition.
  • The boundary value of the new partition must be greater than the upper boundary value of the last partition.
  • The type of the boundary value of the new partition must be the same as that of the partition key.

The boundary of the existing partition p1 is (-∞, 20221010), and the upper boundary of the new partition p0 is 20221009, which falls within the partition p1. The boundary of the existing partition p4 is [20221012, +∞), and an upper boundary of the new partition p5 is 20221013, which falls within the partition p4. The new partitions p0 and p5 do not meet the conditions for running ADD PARTITION to add partitions. Therefore, an error is reported when the statement is executed.

Handling Procedure

You can also run the ALTER TABLE SPLIT PARTITION statement to split existing partitions to create more partitions. Similarly, the new partition name created by SPLIT PARTITION cannot be the same as that of an existing partition.

Use SPLIT PARTITION to split the partition p4 with the range of [20221012, +∞) into partition p4a with the range of [20221012, 20221013) and partition p4b with the range of [20221013, +∞).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
- - Partitions before splitting.
SELECT relname, boundaries FROM pg_partition p where p.parentid='studentinfo'::regclass ORDER BY 1;
   relname   |       boundaries
-------------+-------------------------
 p1          | {"2022-10-10 00:00:00"}
 p2          | {"2022-10-11 00:00:00"}
 p3          | {"2022-10-12 00:00:00"}
 p4          | {NULL}
 studentinfo |
(5 rows)

ALTER TABLE studentinfo SPLIT PARTITION p1 AT('2022-10-09 00:00:00+08') INTO (PARTITION P1a,PARTITION P1b);
ALTER TABLE studentinfo SPLIT PARTITION p4 AT('2022-10-13 00:00:00+08') INTO (PARTITION P4a,PARTITION P4b);

- -- Partitions after splitting.
SELECT relname, boundaries FROM pg_partition p where p.parentid='studentinfo'::regclass ORDER BY 1;
   relname   |       boundaries
-------------+-------------------------
 p1a         | {"2022-10-09 00:00:00"}
 p1b         | {"2022-10-10 00:00:00"}
 p2          | {"2022-10-11 00:00:00"}
 p3          | {"2022-10-12 00:00:00"}
 p4a         | {"2022-10-13 00:00:00"}
 p4b         | {NULL}
 studentinfo |
(7 rows)

If there are requirements on the partition name, you can run the rename partition command to rename all partitions after splitting.

ALTER TABLE studentinfo RENAME PARTITION p1a to p0;
Parent topic: Database Use