一、前言
上一篇写了如何建立分区表,但是显然,如果想要真正使用分区表,肯定不可能手动一个个新建,比如按每天建表,得想办法让它每天自动新建下一天的表,那么表名的命名必然要以日期为后缀。也需要想办法自动当日零点和第二日零点的时间戳,来用于表的分区。
二、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需要等待后续验证