pg_pathman 的 range 分区，删除某个分区，再插入时，提示 ERROR: cannot spawn a partition DETAIL: there is a gap
翁建弼
os: centos 7.6.1810
db: postgresql 10
pg_pathman 1.5.12
版本
# cat /etc/centos-release
CentOS Linux release 7.6.1810 (Core)
# yum list installed |grep -i postgre
postgresql10.x86_64 10.18-1PGDG.rhel7 @pgdg10
postgresql10-contrib.x86_64 10.18-1PGDG.rhel7 @pgdg10
postgresql10-devel.x86_64 10.18-1PGDG.rhel7 @pgdg10
postgresql10-docs.x86_64 10.18-1PGDG.rhel7 @pgdg10
postgresql10-libs.x86_64 10.18-1PGDG.rhel7 @pgdg10
postgresql10-odbc.x86_64 13.00.0000-1PGDG.rhel7 @pgdg10
postgresql10-plperl.x86_64 10.18-1PGDG.rhel7 @pgdg10
postgresql10-plpython.x86_64 10.18-1PGDG.rhel7 @pgdg10
postgresql10-plpython3.x86_64 10.18-1PGDG.rhel7 @pgdg10
postgresql10-pltcl.x86_64 10.18-1PGDG.rhel7 @pgdg10
postgresql10-server.x86_64 10.18-1PGDG.rhel7 @pgdg10
postgresql10-tcl.x86_64 2.7.5-1.rhel7 @pgdg10
postgresql10-test.x86_64 10.18-1PGDG.rhel7 @pgdg10
# yum list installed |grep -i pg_pathman
pg_pathman_10.x86_64 1.5.12-1.rhel7 @pgdg10
基础表准备
postgres=# drop table if exists tmp_range cascade;
create table tmp_range(
id int,
name text,
insert_timestamp timestamp with time zone not null
);
select create_range_partitions(
'tmp_range'::regclass,
'insert_timestamp',
'2019-01-01 00:00:00'::timestamp with time zone,
interval '1 year',
2,
false
);
select set_enable_parent('tmp_range'::regclass, false);
insert into tmp_range
select id,
md5(id::text),
current_date - mod(id,1000)
from generate_series(1,10000) as id
;
select * from pathman_partition_list where parent='tmp_range'::regclass order by range_min;
parent | partition | parttype | expr | range_min | range_max
-----------+-------------+----------+------------------+------------------------+------------------------
tmp_range | tmp_range_4 | 2 | insert_timestamp | 2018-01-01 00:00:00+08 | 2019-01-01 00:00:00+08
tmp_range | tmp_range_1 | 2 | insert_timestamp | 2019-01-01 00:00:00+08 | 2020-01-01 00:00:00+08
tmp_range | tmp_range_2 | 2 | insert_timestamp | 2020-01-01 00:00:00+08 | 2021-01-01 00:00:00+08
tmp_range | tmp_range_3 | 2 | insert_timestamp | 2021-01-01 00:00:00+08 | 2022-01-01 00:00:00+08
(4 rows)
drop table 某个分区，出现 gap
postgres=# drop table if exists tmp_range_2;
postgres=# select * from pathman_partition_list where parent='tmp_range'::regclass order by range_min;
parent | partition | parttype | expr | range_min | range_max
-----------+-------------+----------+------------------+------------------------+------------------------
tmp_range | tmp_range_4 | 2 | insert_timestamp | 2018-01-01 00:00:00+08 | 2019-01-01 00:00:00+08
tmp_range | tmp_range_1 | 2 | insert_timestamp | 2019-01-01 00:00:00+08 | 2020-01-01 00:00:00+08
tmp_range | tmp_range_3 | 2 | insert_timestamp | 2021-01-01 00:00:00+08 | 2022-01-01 00:00:00+08
(3 rows)
查看分区信息，观察 tmp_range_1的range_max， tmp_range_3的 range_min 不相同，出现 gap。
postgres=# insert into tmp_range
select id,
md5(id::text),
current_date - mod(id,1000)
from generate_series(1,10000) as id;
ERROR: cannot spawn a partition
DETAIL: there is a gap
插入数据报错。
怎么调整 tmp_range_3 的 range_min = 2020-01-01 00:00:00+08， 消除 gap ？
postgres=# select create_single_range_partition(
'tmp_range'::regclass,
'2020-01-01 00:00:00+08'::timestamp with time zone,
'2021-01-01 00:00:00+08'::timestamp with time zone,
'tmp_range_2',
null
);
postgres=# select * from pathman_partition_list where parent='tmp_range'::regclass order by range_min;
parent | partition | parttype | expr | range_min | range_max
-----------+-------------+----------+------------------+------------------------+------------------------
tmp_range | tmp_range_4 | 2 | insert_timestamp | 2018-01-01 00:00:00+08 | 2019-01-01 00:00:00+08
tmp_range | tmp_range_1 | 2 | insert_timestamp | 2019-01-01 00:00:00+08 | 2020-01-01 00:00:00+08
tmp_range | tmp_range_2 | 2 | insert_timestamp | 2020-01-01 00:00:00+08 | 2021-01-01 00:00:00+08
tmp_range | tmp_range_3 | 2 | insert_timestamp | 2021-01-01 00:00:00+08 | 2022-01-01 00:00:00+08
(4 rows)
再次插入，成功。
postgres=# insert into tmp_range
select id,
md5(id::text),
current_date - mod(id,1000)
from generate_series(1,10000) as id;
drop_range_partition_expand_next 删除分区
postgres=# select * from pathman_partition_list where parent='tmp_range'::regclass order by range_min;
parent | partition | parttype | expr | range_min | range_max
-----------+-------------+----------+------------------+------------------------+------------------------
tmp_range | tmp_range_4 | 2 | insert_timestamp | 2018-01-01 00:00:00+08 | 2019-01-01 00:00:00+08
tmp_range | tmp_range_1 | 2 | insert_timestamp | 2019-01-01 00:00:00+08 | 2020-01-01 00:00:00+08
tmp_range | tmp_range_2 | 2 | insert_timestamp | 2020-01-01 00:00:00+08 | 2021-01-01 00:00:00+08
tmp_range | tmp_range_3 | 2 | insert_timestamp | 2021-01-01 00:00:00+08 | 2022-01-01 00:00:00+08
(4 rows)
执行
postgres=# select drop_range_partition_expand_next('tmp_range_2'::regclass);
查看
postgres=# select * from pathman_partition_list where parent='tmp_range'::regclass order by range_min;
parent | partition | parttype | expr | range_min | range_max
-----------+-------------+----------+------------------+------------------------+------------------------
tmp_range | tmp_range_4 | 2 | insert_timestamp | 2018-01-01 00:00:00+08 | 2019-01-01 00:00:00+08
tmp_range | tmp_range_1 | 2 | insert_timestamp | 2019-01-01 00:00:00+08 | 2020-01-01 00:00:00+08
tmp_range | tmp_range_3 | 2 | insert_timestamp | 2020-01-01 00:00:00+08 | 2022-01-01 00:00:00+08
(3 rows)
可以看到 tmp_range_3 的 rang_min 自动调整为 2020-01-01 00:00:00+08，消除了gap
postgres=# insert into tmp_range
select id,
md5(id::text),
current_date - mod(id,1000)
from generate_series(1,10000) as id
;
插入数据成功。
类似资料：