数据仓库服务 GAUSSDB(DWS)-范围分区表添加新分区报错upper boundary of adding partition MUST overtop last existing partition:处理方法

时间:2024-11-02 18:44:30

处理方法

使用ALTER TABLE SPLIT PARTITION分割已有分区,也能达到新增分区的目的。同样, SPLIT PARTITION的新分区名称也不能与已有分区相同。

使用split子句分割p4分区[20221012,+∞)为p4a分区范围为[20221012,20221013)和p4b分区范围为[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
——SPLIT PARTITION分割前的分区
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);

——执行SPLIT PARTITION分割后的分区
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)

如果对分区名称有要求,可以在分割后再使用rename partition统一分区名。

ALTER TABLE studentinfo RENAME PARTITION p1a to p0;
support.huaweicloud.com/trouble-dws/dws_09_0127.html