PostgreSQL/pgsql自动添加分区子表

网上之前能找到的例子,都是按数量创建分区子表。

而实际业务中,按日期分区应该是主流,如果照搬按数量创建分区子表的话,感觉不太合用,毕竟实施运维人员也不想动脑子,你居然要求不能频繁执行某个函数,然后又要求多久必需执行它一次……

那么,还是应该搞一个按日期创建分区子表的函数,就显得很有必要了。

前段时间就抽空写了这样一个函数,以后项目上可以直接拿来用。

这个函数可以多次执行,而且不会创建过多的分区子表(估计我有精神洁癖),毕竟可以通过 _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的值,所以只好强行建表了,有知道的麻烦告诉我,谢谢!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值