postgresql 自动创建分区表

PostgreSQL中通过继承，可以支持基本的表分区功能，比如按时间，每月创建一个表分区，数据记录到对应分区中。按照官方文档的操作，创建子表和index、修改trigger等工作都必须DBA定期去手动执行，不能实现自动化，非常不方便。

尝试着通过在plpgsql代码中使用动态SQL, 将大表分区的运维操作实现自动化, 并且可以重用.

假设某个表 tbl_partition 中有很多记录, 每一条记录中采集时间的字段名为: gather_time, 需要按照这个时间, 每个月的数据自动记录到一个子表中, 子分区表的名称定义为: tbl_partition_201510之类. 实现方法记录如下:

1. 创建主表结构, 表名称 tbl_partition, 其中的时间字段名: gather_time

CREATE TABLE tbl_partition
(
  id integer,
  name text,
  data numeric,
  gather_time timestamp
);

2. 为主表创建触发器, 其中,调用了触发器函数 auto_insert_into_tbl_partition(‘gather_time’)

CREATE TRIGGER insert_tbl_partition_trigger
  BEFORE INSERT
  ON tbl_partition
  FOR EACH ROW
  EXECUTE PROCEDURE auto_insert_into_tbl_partition('gather_time');
 

注: 虽然触发器函数缺省不带参数, 此处调用仍然必须传入时间字段名称作为参数. 否则, 函数将不知道以何字段来对主表分区!

3. 创建可重用的触发器函数: auto_insert_into_tbl_partition( time_column_name )

CREATE OR REPLACE FUNCTION auto_insert_into_tbl_partition()
  RETURNS trigger AS
$BODY$
DECLARE
    time_column_name     text ;            -- 父表中用于分区的时间字段的名称[必须首先初始化!!]
    curMM         varchar(6);        -- 'YYYYMM'字串,用做分区子表的后缀
    isExist         boolean;        -- 分区子表,是否已存在
    startTime         text;
    endTime        text;
    strSQL          text;
    
BEGIN
    -- 调用前,必须首先初始化(时间字段名):time_column_name [直接从调用参数中获取!!]
    time_column_name := TG_ARGV[0];
   
    -- 判断对应分区表 是否已经存在?
    EXECUTE 'SELECT $1.'||time_column_name INTO strSQL USING NEW;
    curMM := to_char( strSQL::timestamp , 'YYYYMM' );
    select count(*) INTO isExist from pg_class where relname = (TG_RELNAME||'_'||curMM);
 
    -- 若不存在, 则插入前需 先创建子分区
    IF ( isExist = false ) THEN  
        -- 创建子分区表
        startTime := curMM||'01 00:00:00.000';
        endTime := to_char( startTime::timestamp + interval '1 month', 'YYYY-MM-DD HH24:MI:SS.MS');
        strSQL := 'CREATE TABLE IF NOT EXISTS '||TG_RELNAME||'_'||curMM||
                  ' ( CHECK('||time_column_name||'>='''|| startTime ||''' AND '
                             ||time_column_name||'< '''|| endTime ||''' )
                          ) INHERITS ('||TG_RELNAME||') ;'  ;  
        EXECUTE strSQL;
 
        -- 创建索引
        strSQL := 'CREATE INDEX '||TG_RELNAME||'_'||curMM||'_INDEX_'||time_column_name||' ON '
                  ||TG_RELNAME||'_'||curMM||' ('||time_column_name||');' ;
        EXECUTE strSQL;
       
    END IF;
 
    -- 插入数据到子分区!
    strSQL := 'INSERT INTO '||TG_RELNAME||'_'||curMM||' SELECT $1.*' ;
    EXECUTE strSQL USING NEW;
    RETURN NULL; 
END
$BODY$
  LANGUAGE plpgsql;

说明:
(1) 代码中使用了 TG_ARGV[0] 来获取调用时传入的参数: 用于分区的时间字段名.

(2) 代码中,通过内置参数 TG_RELNAME 获得了父表的表名称.

(3) 首先根据插入时间, 判断对应分区表是否存在? 若存在, 直接插入对应分区子表

(4) 若分区表还不存在, 先创建分区子表和索引, 然后插入数据到所建的子表中.

 

方案二

自动分区
以create_time字段为分区规则，所有分区表继承主表，插入数据将分配到相应分区中，若分区表不存在会触发调用函数自动创建
创建主表

CREATE TABLE "t_user" (
  "uuid" varchar(36) COLLATE "pg_catalog"."default" NOT NULL,
  "dept_id" varchar(32) COLLATE "pg_catalog"."default",
  "user_name" varchar(32) COLLATE "pg_catalog"."default",
  "phone" varchar(32) COLLATE "pg_catalog"."default",
  "create_time" timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "part_time" varchar(6) COLLATE "pg_catalog"."default",
) PARTITION BY list(part_time);
COMMENT ON COLUMN "t_user"."uuid" IS '主键id';
COMMENT ON COLUMN "t_user"."dept_id" IS '部门id';
COMMENT ON COLUMN "t_user"."user_name" IS '姓名;
COMMENT ON COLUMN "t_user"."phone" IS '手机号';
COMMENT ON COLUMN "t_user"."create_time" IS '创建时间';

　　定义自动插入函数

CREATE OR REPLACE FUNCTION auto_insert_t_user_fun()
  RETURNS trigger AS
$BODY$
DECLARE
    time_column_name   text ;      -- 父表中用于分区的时间字段的名称[必须首先初始化!!]
    curMM             varchar(6);    -- 'YYYYMM'字串,用做分区子表的后缀
    isExist         boolean;    -- 分区子表,是否已存在
    startTime         text;
    endTime            text;
    strSQL          text;

BEGIN
    -- 调用前,必须首先初始化(时间字段名):time_column_name [直接从调用参数中获取!!]
    time_column_name := TG_ARGV[0];

    -- 判断对应分区表是否已经存在
    EXECUTE 'SELECT $1.'||time_column_name INTO strSQL USING NEW;
    curMM := to_char( strSQL::timestamp , 'YYYYMM' );
    select count(*) INTO isExist from pg_class where relname = (TG_RELNAME||'_'||curMM);

    -- 若不存在, 则插入前需先创建子分区
    IF ( isExist = false ) THEN  
        -- 创建子分区表
        startTime := curMM||'01 00:00:00.000';
        endTime := to_char( startTime::timestamp + interval '1 month', 'YYYY-MM-DD HH24:MI:SS.MS');
        strSQL := 'CREATE TABLE IF NOT EXISTS '||TG_RELNAME||'_'||curMM||
                  ' ( CHECK('||time_column_name||'>='''|| startTime ||''' AND '
                             ||time_column_name||'< '''|| endTime ||''' )
                          ) INHERITS ('||TG_RELNAME||') ;'  ;  
        EXECUTE strSQL;

        -- 建立创建时间为索引
    strSQL := 'CREATE INDEX idx_tu_ctime_'||curMM||' ON '
              ||TG_RELNAME||'_'||curMM||' ('||time_column_name||');' ;
        EXECUTE strSQL;
         -- 建立部门ID为索引
    strSQL := 'CREATE INDEX idx_tu_deptid_'||curMM||' ON '
              ||TG_RELNAME||'_'||curMM||' (dept_id);' ;
        EXECUTE strSQL;
    -- 定义uuid为主键    
    strSQL := 'ALTER TABLE '||TG_RELNAME||'_'||curMM||' add constraint 
            pk_hhc_'||curMM||' PRIMARY KEY(uuid);' ;
        EXECUTE strSQL;   
    -- 定义手机号为唯一约束    
    strSQL := 'ALTER TABLE '||TG_RELNAME||'_'||curMM||' add constraint 
            uk_tu_phone_'||curMM||' unique(phone);' ;
        EXECUTE strSQL;          
    END IF;

    -- 插入数据到子分区表
    strSQL := 'INSERT INTO '||TG_RELNAME||'_'||curMM||' SELECT $1.*' ;
    EXECUTE strSQL USING NEW;
    RETURN NULL; 
END
$BODY$
  LANGUAGE plpgsql;

定义触发器

CREATE TRIGGER insert_t_user_trigger
BEFORE INSERT
ON t_user
FOR EACH ROW
EXECUTE PROCEDURE auto_insert_t_user_fun('create_time');