GaussDB(DWS)在list分区添加新分区报错案例
【摘要】 用户创建list分区,之后又想增加新的分区,但使用alter table add partition语句报错,报错内容为:upper boundary of adding partition MUST overtop last existing partition.
问题背景
用户创建list分区,之后又想增加新的分区,但使用alter table add partition语句报错,报错内容为:upper boundary of adding partition MUST overtop last existing partition.
根据报错提示,不能新增分区的原因是:分区的上边界必须大于最后一个现有分区的上边界。
场景复现
- 新建测试表t1,执行分区为list分区,分区边界依次为为
(-∞,20221010)、[20221010,20221011)、[20221011,20221012)、[20221012,+∞);
postgres=# drop table if exists t1;
NOTICE: table "t1" does not exist, skipping
DROP TABLE
postgres=# create table t1(a int, b timestamp)
PARTITION BY RANGE (b) (
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)
);
NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using round-robin as the distribution mode by default.
HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
- 使用add partition增加边界为20221009或20221013的分区,执行失败,报错为
the boundary of partition “partition_name” is less than previous partition’s boundary,
老版本报错为
upper boundary of adding partition MUST overtop last existing partition; - 失败原因:p1的边界为(-∞,20221010),而新增的上界为20221009的分区落在p1内;20221013也是一样的道理;p4的边界为[20221012,+∞),而新增的上界为20221013的分区落在p4内;
postgres=# alter table t1 add partition p0 values less than ('2022-10-09 00:00:00+08');
ERROR: the boundary of partition "p0" is less than previous partition's boundary
postgres=# alter table t1 add partition p0 values less than ('2022-10-13 00:00:00+08');
ERROR: the boundary of partition "p0" is less than previous partition's boundary
- add partition增加分区时需满足:
1,分区名不能与已有分区名相同
2,边界值需大于最后一个分区的上边界
处理办法
- 因分区已存在,故改用split partition分割已有分区来达到效果,同样,新分区的名称不能与已有分区相同
postgres=# alter table t1 split partition p1 at('2022-10-09 00:00:00+08') INTO (PARTITION P1a,PARTITION P1b);
ALTER TABLE
postgres=# alter table t1 split partition p4 at('2022-10-13 00:00:00+08') INTO (PARTITION P4a,PARTITION P4b);
ALTER TABLE
postgres=# select pg_get_tabledef('t1');
pg_get_tabledef
---------------------------------------------------------------------------------------------------------------------
SET search_path = public; +
CREATE TABLE t1 ( +
a integer, +
b timestamp without time zone +
) +
WITH (orientation=row, compression=no) +
DISTRIBUTE BY ROUNDROBIN +
TO GROUP node_group +
PARTITION BY RANGE (b) +
( +
PARTITION p1a VALUES LESS THAN ('2022-10-09 00:00:00'::timestamp without time zone) TABLESPACE pg_default,+
PARTITION p1b VALUES LESS THAN ('2022-10-10 00:00:00'::timestamp without time zone) TABLESPACE pg_default,+
PARTITION p2 VALUES LESS THAN ('2022-10-11 00:00:00'::timestamp without time zone) TABLESPACE pg_default, +
PARTITION p3 VALUES LESS THAN ('2022-10-12 00:00:00'::timestamp without time zone) TABLESPACE pg_default, +
PARTITION p4a VALUES LESS THAN ('2022-10-13 00:00:00'::timestamp without time zone) TABLESPACE pg_default,+
PARTITION p4b VALUES LESS THAN (MAXVALUE) TABLESPACE pg_default +
);
(1 row)
- 对分区名称有要求的可以在分割后再使用rename partition统一分区名
postgres=# alter table t1 rename partition p1a to p0;
ALTER TABLE
postgres=# alter table t1 rename partition p1b to p1;
ALTER TABLE
postgres=# alter table t1 rename partition p4a to p4;
ALTER TABLE
postgres=# alter table t1 rename partition p4b to p5;
ALTER TABLE
postgres=# select pg_get_tabledef('t1');
pg_get_tabledef
--------------------------------------------------------------------------------------------------------------------
SET search_path = public; +
CREATE TABLE t1 ( +
a integer, +
b timestamp without time zone +
) +
WITH (orientation=row, compression=no) +
DISTRIBUTE BY ROUNDROBIN +
TO GROUP node_group +
PARTITION BY RANGE (b) +
( +
PARTITION p0 VALUES LESS THAN ('2022-10-09 00:00:00'::timestamp without time zone) TABLESPACE pg_default,+
PARTITION p1 VALUES LESS THAN ('2022-10-10 00:00:00'::timestamp without time zone) TABLESPACE pg_default,+
PARTITION p2 VALUES LESS THAN ('2022-10-11 00:00:00'::timestamp without time zone) TABLESPACE pg_default,+
PARTITION p3 VALUES LESS THAN ('2022-10-12 00:00:00'::timestamp without time zone) TABLESPACE pg_default,+
PARTITION p4 VALUES LESS THAN ('2022-10-13 00:00:00'::timestamp without time zone) TABLESPACE pg_default,+
PARTITION p5 VALUES LESS THAN (MAXVALUE) TABLESPACE pg_default +
);
(1 row)
知识小结
- 对于范围分区表,要添加的分区的边界值要和分区表的分区键的类型一致,且要大于分区表的最后一个分区的上边界;
- 当遇到不能直接增加分区的场景,可灵活应用split分割分区来达到同样的效果;
想了解GuassDB(DWS)更多信息,欢迎微信搜索“GaussDB DWS”关注微信公众号,和您分享最新最全的PB级数仓黑科技~
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)