pgsql表分区过程记录

分区步骤

TODO

1，创建主表

项目保密原因，主表结构略，该表由"trandt", "transq"两个字段作为联合主键，分区以"trandt"字段按天分区

2，创建分区子表

子表继承主表，使用INHERITS创建的新表会继承一个或多个父表，子表只会继承父表的表结构和NOT NULL，DEFAULT，CHECK三种约束，所以修改父表的结构（增删字段），NOT NULL，DEFAULT和CHECK约束会自动同步子表修改。

// 创建子表继承主表
CREATE TABLE ipp_tran_20210224 ( check (trandt >= '20210224' and trandt< '20210225') ) INHERITS (ipp_tran);
CREATE TABLE ipp_tran_20210225 ( check (trandt >= '20210225' and trandt< '20210226') ) INHERITS (ipp_tran);
CREATE TABLE ipp_tran_20210226 ( check (trandt >= '20210226' and trandt< '20210227') ) INHERITS (ipp_tran);
CREATE TABLE ipp_tran_20210227 ( check (trandt >= '20210227' and trandt< '20210228') ) INHERITS (ipp_tran);
CREATE TABLE ipp_tran_20210228 ( check (trandt >= '20210228' and trandt< '20210301') ) INHERITS (ipp_tran);
CREATE TABLE ipp_tran_20210301 ( check (trandt >= '20210301' and trandt< '20210302') ) INHERITS (ipp_tran);
CREATE TABLE ipp_tran_20210302 ( check (trandt >= '20210302' and trandt< '20210303') ) INHERITS (ipp_tran);

但是主键，外键和唯一键以及索引不会被继承，需要各子表单独创建，尤其是业务上需要使用唯一索引和主键进行唯一判定的，要谨慎使用，因为数据被插入主表时会被路由到子表，尤其是如果有其他字段是唯一索引，如果数据被路由到不同的子表，该唯一索引会失效 ！！！

// 分区表上和主表相同的主键，索引
CREATE UNIQUE INDEX uk_ipp_tran_20210224 on ipp_tran_20210224 ("trandt","transq");
CREATE UNIQUE INDEX uk_ipp_tran_20210225 on ipp_tran_20210225 ("trandt","transq");
CREATE UNIQUE INDEX uk_ipp_tran_20210226 on ipp_tran_20210226 ("trandt","transq");
CREATE UNIQUE INDEX uk_ipp_tran_20210227 on ipp_tran_20210227 ("trandt","transq");
CREATE UNIQUE INDEX uk_ipp_tran_20210228 on ipp_tran_20210228 ("trandt","transq");
CREATE UNIQUE INDEX uk_ipp_tran_20210301 on ipp_tran_20210301 ("trandt","transq");
CREATE UNIQUE INDEX uk_ipp_tran_20210302 on ipp_tran_20210302 ("trandt","transq");

CREATE INDEX idx_ipp_tran_20210224 ON ipp_tran_20210224 ("trandt");
CREATE INDEX idx_ipp_tran_20210225 ON ipp_tran_20210225 ("trandt");
CREATE INDEX idx_ipp_tran_20210226 ON ipp_tran_20210226 ("trandt");
CREATE INDEX idx_ipp_tran_20210227 ON ipp_tran_20210227 ("trandt");
CREATE INDEX idx_ipp_tran_20210228 ON ipp_tran_20210228 ("trandt");
CREATE INDEX idx_ipp_tran_20210301 ON ipp_tran_20210301 ("trandt");
CREATE INDEX idx_ipp_tran_20210302 ON ipp_tran_20210302 ("trandt");

3，创建触发器函数和触发器

开发人员肯定是希望只操作主表，数据可以自动被路由到子表中插入，但是pgsql不支持，需要使用触发器辅助。

// 触发器函数
CREATE OR REPLACE FUNCTION ipp_tran_insert_trigger()                      
RETURNS TRIGGER AS $$  
BEGIN  
    IF ( NEW.trandt >= '20210224' AND NEW.trandt < '20210225' ) THEN INSERT INTO ipp_tran_20210224 VALUES (NEW.*);  
    ELSIF ( NEW.trandt >= '20210225' AND NEW.trandt < '20210226' ) THEN INSERT INTO ipp_tran_20210225 VALUES (NEW.*);  
    ELSIF ( NEW.trandt >= '20210226' AND NEW.trandt < '20210227' ) THEN INSERT INTO ipp_tran_20210226 VALUES (NEW.*);  
    ELSIF ( NEW.trandt >= '20210227' AND NEW.trandt < '20210228' ) THEN INSERT INTO ipp_tran_20210227 VALUES (NEW.*);  
    ELSIF ( NEW.trandt >= '20210228' AND NEW.trandt < '20210301' ) THEN INSERT INTO ipp_tran_20210228 VALUES (NEW.*);  
    ELSIF ( NEW.trandt >= '20210301' AND NEW.trandt < '20210302' ) THEN INSERT INTO ipp_tran_20210301 VALUES (NEW.*);
    ELSIF ( NEW.trandt >= '20210302' AND NEW.trandt < '20210303' ) THEN INSERT INTO ipp_tran_20210302 VALUES (NEW.*); 
    ELSE RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';  
    END IF;  
    RETURN NULL;  
END;  
$$  
LANGUAGE plpgsql;

// 器函数
CREATE TRIGGER insert_ipp_tran_trigger
    BEFORE INSERT ON ipp_tran
    FOR EACH ROW EXECUTE PROCEDURE ipp_tran_insert_trigger();

最终结果

第一次插入数据，显示Affected rows: 0，这是因为数据是被路由到子表中进行insert操作，但是数据已经成功插入了

相同的语句第二次插入会报错，出发了子表的唯一索引，如果只继承主表，不单独创建子表的索引，插入多少次都会显示成功。