postgresql定时建表_PostgreSQL中使用动态SQL实现自动按时间创建表分区Go语言中文社区...

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

尝试着通过在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(&＃39;gather_time&＃39;)

CREATE TRIGGER insert_tbl_partition_trigger

BEFORE INSERT

ON tbl_partition

FOR EACH ROW

EXECUTE PROCEDURE auto_insert_into_tbl_partition(&＃39;gather_time&＃39;);

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

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);-- &＃39;YYYYMM&＃39;字串,用做分区子表的后缀

isExist boolean;-- 分区子表,是否已存在

startTime text;

endTimetext;

strSQL text;

BEGIN

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

time_column_name :&＃61; TG_ARGV[0];

-- 判断对应分区表 是否已经存在?

EXECUTE &＃39;SELECT $1.&＃39;||time_column_name INTO strSQL USING NEW;

curMM :&＃61; to_char( strSQL::timestamp , &＃39;YYYYMM&＃39; );

select count(*) INTO isExist from pg_class where relname &＃61; (TG_RELNAME||&＃39;_&＃39;||curMM);

-- 若不存在, 则插入前需 先创建子分区

IF ( isExist &＃61; false ) THEN

-- 创建子分区表

startTime :&＃61; curMM||&＃39;01 00:00:00.000&＃39;;

endTime :&＃61; to_char( startTime::timestamp &＃43; interval &＃39;1 month&＃39;, &＃39;YYYY-MM-DD HH24:MI:SS.MS&＃39;);

strSQL :&＃61; &＃39;CREATE TABLE IF NOT EXISTS &＃39;||TG_RELNAME||&＃39;_&＃39;||curMM||

&＃39; ( CHECK(&＃39;||time_column_name||&＃39;>&＃61;&＃39;&＃39;&＃39;|| startTime ||&＃39;&＃39;&＃39; AND &＃39;

||time_column_name||&＃39;<&＃39;&＃39;&＃39;|| endTime ||&＃39;&＃39;&＃39; )

) INHERITS (&＃39;||TG_RELNAME||&＃39;) ;&＃39; ;

EXECUTE strSQL;

-- 创建索引

strSQL :&＃61; &＃39;CREATE INDEX &＃39;||TG_RELNAME||&＃39;_&＃39;||curMM||&＃39;_INDEX_&＃39;||time_column_name||&＃39; ON &＃39;

||TG_RELNAME||&＃39;_&＃39;||curMM||&＃39; (&＃39;||time_column_name||&＃39;);&＃39; ;

EXECUTE strSQL;

END IF;

-- 插入数据到子分区!

strSQL :&＃61; &＃39;INSERT INTO &＃39;||TG_RELNAME||&＃39;_&＃39;||curMM||&＃39; SELECT $1.*&＃39; ;

EXECUTE strSQL USING NEW;

RETURN NULL;

END

$BODY$

LANGUAGE plpgsql;

说明:

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

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

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

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

以上代码, 在PostgreSQL v9.4 中调试通过. 理论上, v8.4以上均支持.

参考:

[1] http://stackoverflow.com/questions/1997337/inserting-new-from-a-generic-trigger-using-execute-in-pl-pgsql

[2] http://blog.csdn.net/neo_liu0000/article/details/6255623

[3] http://www.enterprisedb.com/docs/en/9.4/pg/ddl-partitioning.html