"ERROR:start value of partition 'XX' NOT EQUAL up-boundary of last partition." Is Displayed When Operations Related to Table Partitions Are Performed

Symptom

When ALTER TABLE PARTITION is performed, the following error message is displayed:

ERROR:start value of partition "XX" NOT EQUAL up-boundary of last partition.

Cause Analysis

If the ALTER TABLE PARTITION statement involves both the DROP PARTITION operation and the ADD PARTITION operation, GaussDB(DWS) always performs the DROP PARTITION operation before the ADD PARTITION operation regardless of their orders. However, performing DROP PARTITION before ADD PARTITION causes a partition gap. As a result, an error is reported.

Solution

To prevent partition gaps, set END in DROP PARTITION to the value of START in ADD PARTITION.

Example: Create the partitioned table partitiontest.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE TABLE partitiontest 
( 
 c_int integer,
 c_time TIMESTAMP WITHOUT TIME ZONE
) 
PARTITION BY range (c_int) 
(
 partition p1 start(100)end(108), 
 partition p2 start(108)end(120) 
);  

An error is reported when the following statements are used:

1
2

ALTER TABLE partitiontest ADD PARTITION p3 start(120)end(130), DROP PARTITION p2;
ERROR:  start value of partition "p3" NOT EQUAL up-boundary of last partition.

1
2

ALTER TABLE partitiontest DROP PARTITION p2,ADD PARTITION p3 start(120)end(130) ;
ERROR:  start value of partition "p3" NOT EQUAL up-boundary of last partition.

Change them as follows:

1

ALTER TABLE partitiontest ADD PARTITION p3 start(108)end(130), DROP PARTITION p2;

1

ALTER TABLE partitiontest DROP PARTITION p2,ADD PARTITION p3 start(108)end(130) ;