GaussDB(DWS)在list分区添加新分区报错案例

举报
你是猴子请来的救兵吗 发表于 2022/11/15 14:41:18 2022/11/15
【摘要】 用户创建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.

image.png
image.png

根据报错提示,不能新增分区的原因是:分区的上边界必须大于最后一个现有分区的上边界。

场景复现

  • 新建测试表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

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。