postgresql把日期作为表名的一部分动态建立分区表

一、前言

上一篇写了如何建立分区表，但是显然，如果想要真正使用分区表，肯定不可能手动一个个新建，比如按每天建表，得想办法让它每天自动新建下一天的表，那么表名的命名必然要以日期为后缀。也需要想办法自动当日零点和第二日零点的时间戳，来用于表的分区。

二、sql获取日期和时间的方式

select current_date; --2022-12-13
select now(); --2022-12-13 21:37:02
select date_trunc('day', now() + interval '1 day') --明天0点 2022-12-14 00:00:00
select EXTRACT(epoch FROM CAST(CURRENT_DATE AS TIMESTAMPTZ)) --今天0点时间戳
select EXTRACT(epoch FROM CAST(CURRENT_DATE+1 AS TIMESTAMPTZ)) --明天0点时间戳
select to_char(now(),'YYYYMMDD'); --20221213
select to_char(current_timestamp,'YYYYMMDD'); --20221213

那么我们的分区表名可以像这样生成：

select 'task_minute_v2_' || to_char(now(),'YYYYMMDD');
select 'task_minute_v2_' || to_char(current_timestamp,'YYYYMMDD');

三、根据自定义表名动态建表

在psql中 如果需要建表 需要用do $$：

do $$
BEGIN
execute 'CREATE TABLE ensv_s_image_task_minute_v2_' || to_char(now() - interval '15 day', 'YYYYMMDD') || ' PARTITION OF ensv_s_image_task_minute_v2 FOR VALUES FROM (1670688000) TO (1670699000);';
END;
$$ LANGUAGE plpgsql;

四、使用存储过程

由于每天都要建相同的表，所以其实使用存储过程（函数）会更合适一些：

CREATE OR REPLACE FUNCTION create_tomorrow_table() RETURNS void AS $$
BEGIN
  EXECUTE 'CREATE TABLE task_minute_v2_' || to_char(now() + interval '1 day', 'YYYYMMDD') ||' PARTITION OF task_minute_v2 FOR VALUES FROM (' || EXTRACT(epoch FROM CAST(CURRENT_DATE+1 AS TIMESTAMPTZ)) || ') TO (' || EXTRACT(epoch FROM CAST(CURRENT_DATE+2 AS TIMESTAMPTZ)) || ');';
END;
$$ LANGUAGE plpgsql;

然后通过以下语句调用它就行了。

SELECT create_tomorrow_table();

所以如果是detach七天前的表的存储过程呢，就是下面这样：

CREATE or replace FUNCTION detach_old_table() RETURNS void AS $$
BEGIN
  EXECUTE 'ALTER TABLE ensv_s_image_task_minute_v2 DETACH PARTITION ensv_s_image_task_minute_v2_' || to_char(now() - interval '7 day', 'YYYYMMDD') || ';';
END;
$$ LANGUAGE plpgsql;

在Spring data jpa中，使用EntityManager和定时器，就可以方便地每天建表了。

@Component
public class PartitionTableTrigger {

    @Autowired
    EntityManager entityManager;


    @Scheduled(cron = "${partition.table.detach.cron:0 0 1 * * ?}")
    public void detachTable() {
        //假设数据保留七天
        String sql = "SELECT detach_old_table();";
        Query query = entityManager.createNativeQuery(sql);
        query.executeUpdate();

    }

    @Scheduled(cron = "${partition.table.create.cron:0 0 23 * * ?}")
    public void createPartitionTable() {
        String sql = "SELECT create_tomorrow_table();";
        Query query = entityManager.createNativeQuery(sql);
        query.executeUpdate();
    }
}

五、通用的建表方法

在三中，我们建表的时候，还是写死了表名，当有很多表都需要做分区时，可以把表名作为函数的变量，然后给需要分区的表建个Enum，这样就可以很方便地把所有需要分区的表都管理起来啦。
示例：
data.sql:

CREATE or replace FUNCTION detach_old_table(varchar,int) RETURNS void AS $$
BEGIN
  EXECUTE 'ALTER TABLE IF EXISTS ' || $1 || ' DETACH PARTITION ' || $1 || '_' || to_char(CURRENT_DATE-$2, 'YYYYMMDD') || ';';
END;
$$ LANGUAGE plpgsql;

在detach前 需要先判断表是否存在 否则可能报错
目前我是这么写的：

CREATE or replace FUNCTION detach_old_table(varchar,int) RETURNS void AS $$
BEGIN
IF (select count(*) from information_schema.tables where table_name = $1 || '_' || to_char(CURRENT_DATE-$2, 'YYYYMMDD')) > 0
THEN
  EXECUTE 'ALTER TABLE IF EXISTS ' || $1 || ' DETACH PARTITION ' || $1 || '_' || to_char(CURRENT_DATE-$2, 'YYYYMMDD') || ';';
END IF;
END;
$$ LANGUAGE plpgsql;

但这样依旧是有问题的，在分区表已经是独立的表时，还是会报错。

主表枚举类：

package com.hikvision.pbg.ensv.most.common.constants;
import com.google.common.collect.Lists;
import java.util.List;


public enum PartitionTableEnum {

    /** 需要分区的主表 */
    TASK_MINUTE_V2("task_minute_v2"),
    TASK_MINUTE_V3("task_minute_v3");

    private String value;

    PartitionTableEnum(String value) {
        this.value = value;
    }

    public String getValue() {
        return value;
    }

    public static List<String> toValues() {
        List<String> list = Lists.newArrayList();
        for (PartitionTableEnum partitionTableEnum : PartitionTableEnum.values()) {
            list.add(partitionTableEnum.getValue());
        }
        return list;
    }
}

定时器：

@Component
public class PartitionTableTrigger {

    @Autowired
    EntityManager entityManager;
    @Value("${tables.remained.days:30}")
    private Integer tableRemainedDays;

    @Scheduled(cron = "${partition.table.detach.cron:0 0 1 * * ?}")
    public void detachTable() {
        for (PartitionTableEnum tableEnum : PartitionTableEnum.values()){
            String sql = "SELECT detach_old_table(?1,?2);";
            Query query = entityManager.createNativeQuery(sql);
            query.setParameter(1,tableEnum.getValue());
            query.setParameter(2,tableRemainedDays);
            query.executeUpdate();
        }
    }

}

但是我其实不太了解entityManager 如果在此时有某张表detach失败了 会怎么样？整体回滚吗 以及是否需要close entityManager需要等待后续验证