网上之前能找到的例子,都是按数量创建分区子表。
而实际业务中,按日期分区应该是主流,如果照搬按数量创建分区子表的话,感觉不太合用,毕竟实施运维人员也不想动脑子,你居然要求不能频繁执行某个函数,然后又要求多久必需执行它一次……
那么,还是应该搞一个按日期创建分区子表的函数,就显得很有必要了。
前段时间就抽空写了这样一个函数,以后项目上可以直接拿来用。
这个函数可以多次执行,而且不会创建过多的分区子表(估计我有精神洁癖),毕竟可以通过 _end_date参数来规范子表的日期范围嘛,贴出来共享给大家。
注释写得比较啰嗦,我也怕时间久了记不清当初的意图,慢慢看哈。
CREATE OR REPLACE FUNCTION append_partition_table(
_partition_tablename VARCHAR,
_begin_date DATE,
_end_date DATE,
_partition_type INT,
_gaps INT)
RETURNS void
AS
$BODY$
DECLARE
-- 分区表子表名
_partition_child_name VARCHAR;
-- 循环递增变量i
i INT DEFAULT 0;
-- 计算得出的,假设的分区字段From的值,对应的日期
partition_from_date DATE;
-- 计算得出的,假设的分区字段To的值,对应的日期,用于判断是否可以退出循环
partition_to_date DATE;
BEGIN
-- 功能:不使用分区表插件,为PgSQL内置分区表中,为指定分区主表,按天、按月、按年添加分区
-- 子表,若不是按[天]分区时,添加的分区子表会比[截止日期]稍大些日期
-- 在PostgresSQL v14.1中调试通过
-- 说明:
-- 1、使用该存储过程时,只是按指定规则生成分区子表的表名并创建,并不能判断该子表名是否
-- 为分区主表的子表,即,若该子表名已存在则不建表,若该子表名不存在则创建成指定分区
-- 主表的分区子表
-- 2、分区子表名未加双引号,不区分大小写,默认为小写
-- 分区表子表名 = 主表名 + 分隔符 + 年月日(或者年月,或者年),如,log_all_202001或者log_all_20200201
-- 3、分区子表将与分区主表在同一个Schema中,本存储过程并不做特殊处理
--
-- 4、本存储过程自动创建的分区子表,是range分区表,请自行建好主表,建主表Demo如下:
-- 创建分区主表
-- CREATE TABLE queue (
-- qid varchar(50) NOT NULL,
-- pati_id varchar(50) NULL,
-- pati_name varchar(50) NULL,
-- serial_number varchar(100) NULL,
-- stateid int4,
-- validdate date,
-- PRIMARY KEY (qid, validdate)
-- ) PARTITION BY range(validdate);
-- 为分区主表的分区字段创建BRIN索引,分区子表会自动生成相应的索引
-- CREATE INDEX idx_queue_validdate ON queue USING BRIN(validdate);
-- 5、因为没找到如何查找现有分区子表的From和To的值,所以只好强行建表了,有知道的麻烦告诉我,谢谢
-- 调用Demo1:SELECT append_partition_table('operation_log', '2021-02-11', '2021-08-11', 0, 1);
-- 调用Demo2:SELECT append_partition_table('public.operation_log', '2021-02-11', '2021-08-11', 1, 1);
-- 参数:_partition_tablename:创建分区子表的主表的名称, 注意确认表名中是否需要包含Schema名,分区
-- 分区子表将与分区主表在同一个Schema中
-- 参数:_begin_date:创建分区表的开始日期
-- 参数:_end_date:创建分区表的截止日期
-- 参数:_partition_type:分区类型[0按天分区,1按月分区,2按年分区]
-- 参数:_gaps:分区间隔,如果分区类型为0,则表示每个分区的间隔为 gaps天
-- 如果分区类型为1,则表示每个分区的间隔为 gaps月
-- 如果分区类型为2,则表示每个分区的间隔为 gaps年
-- 输出调试信息
RAISE NOTICE '_partition_type=[%]', _partition_type;
RAISE NOTICE '_gaps=[%]', _gaps;
-- _partitiontype 只接收0/1/2三种值
IF _partition_type not in (0,1,2) THEN
RAISE EXCEPTION 'The Parameter [_partition_type] is incorrect. Only 0, 1, and 2 are supported!!! ';
END IF;
partition_from_date := _begin_date::DATE;
partition_to_date := _begin_date::DATE;
-- 开始循环,判断并自动分区
WHILE (partition_to_date <= _end_date) LOOP
-- 根据[类型入参],生成准备创建的分区子表的表名
IF (_partition_type = 0) THEN
-- 每个分区按天递增, 递增gaps天, 计算得到From和To的值,并按From的值生成分区子表名中日期的数字部分
partition_from_date = _begin_date::DATE+ CONCAT((i)*_gaps,' day')::INTERVAL;
partition_to_date = _begin_date::DATE+ CONCAT((i+1)*_gaps,' day')::INTERVAL;
_partition_child_name := to_char(partition_from_date::DATE, 'yyyymmdd');
ELSEIF (_partition_type = 1) THEN
-- 每个分区按月递增, 递增gaps月, 计算得到From和To的值,并按From的值生成分区子表名中日期的数字部分
partition_from_date := date_trunc('MONTH', _begin_date::DATE)+ CONCAT((i)*_gaps, ' MONTH')::INTERVAL;
partition_to_date := date_trunc('MONTH', _begin_date::DATE)+ CONCAT((i+1)*_gaps, ' MONTH')::INTERVAL;
_partition_child_name := to_char(partition_from_date::DATE, 'yyyymm');
ELSE
-- 每个分区按年递增, 递增gaps年, 计算得到From和To的值,并按From的值生成分区子表名中日期的数字部分
partition_from_date := date_trunc('YEAR', _begin_date::DATE) + CONCAT((i)*_gaps,' YEAR')::INTERVAL;
partition_to_date := date_trunc('YEAR', _begin_date::DATE) + CONCAT((i+1)*_gaps,' YEAR')::INTERVAL;
_partition_child_name := to_char(partition_from_date::DATE, 'yyyy');
END IF;
-- 拼接分区表子表名,分区表子表名 = 主表名 + 分隔符 + 年月日(或者年月,或者年)
_partition_child_name := _partition_tablename||'_'||_partition_child_name;
-- 输出调试信息
RAISE NOTICE 'partition_from_date=[%]', partition_from_date;
RAISE NOTICE 'partition_to_date=[%]', partition_to_date;
RAISE NOTICE '_partition_child_name=[%]', _partition_child_name;
-- CREATE TABLE IF NOT EXISTS _partition_child_name
-- PARTITION OF _partition_tablename
-- FOR VALUES FROM (partition_from_date) TO (partition_to_date);
-- 输出调试信息
RAISE NOTICE 'Run SQL: CREATE TABLE IF NOT EXISTS % PARTITION OF % FOR VALUES FROM (''%'') TO (''%'') WITH (fillfactor=90);',
_partition_child_name, _partition_tablename, partition_from_date, partition_to_date;
-- 执行SQL语句,判断指定的表名是否存在,若不存在则创建成指定分区主表的分区子表
-- [fillfactor=90]也是我的精神洁癖,会浪费10%的磁盘空间,感觉不爽的朋友请自行去掉
execute format('CREATE TABLE IF NOT EXISTS %s PARTITION OF %s FOR VALUES FROM (''%s'') TO (''%s'') WITH (fillfactor=90);',
_partition_child_name, _partition_tablename, partition_from_date, partition_to_date);
RAISE NOTICE 'Run SQL OK!';
-- 递增变量
i := i + 1;
END LOOP; -- End While
END;
$BODY$
LANGUAGE plpgsql VOLATILE
;
测试:
SELECT append_partition_table('kysoft.log_all', '2020-02-01', '2020-02-09', 0, 3);
执行结果(意思一下,不贴图了,中间三个表是上面SQL语句建出来的,另两个是之前建的):
Table Name|schema
log_all_202001 kysoft
log_all_20200201 kysoft
log_all_20200204 kysoft
log_all_20200207 kysoft
log_all_his kysoft
另外,因为没找到如何查找现有分区子表的From和To的值,所以只好强行建表了,有知道的麻烦告诉我,谢谢!