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

问题背景

用户创建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级数仓黑科技~