与Oracle不同,PostgreSQL需要手动控制分区规则触发器。
步骤一:创建分区
CREATE TABLE table_partition_1( CHECK partition_column criteria) INHENRITS (table)
步骤二:为分区表创建PK跟index,这里使用btree
ALTER TABLE ONLY table_partition_1
ADD CONSTRAINT table_partition_1_pkey PRIMARY KEY (key_column);
CREATE INDEX index_table_partition_1 ON table_partition_1 USING btree(column);
步骤三:手动创建触发器
CREATE OR REPLACE FUNCTION before_insert_table()
RETURNS trigger AS
$BODY$
DECLARE
BEGIN
if criteria then
EXECUTE 'insert into appropriate table ...' SELECT ($1).* ' USING NEW;
end if;
return null;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION before_insert_table()
OWNER TO db;
rule在批量操作时更合适,但是对于单独操作会占用较大的开销。
CREATE RULE table_partition_1 _insert AS
(criteria ...)
DO INSTEAD
INSERT INTO table_partition_1 VALUES (NEW.*)
步骤四:触发器方式hibernate向分区插入数据时,获得的result count为0,会导致判断失败而回滚。
解决方法是使用rule,或者声明分区插入时不进行result检查。
@SQLInsert(sql = "INSERT INTO "
+ "table(column,...)"
+ " VALUES(?,...)", check = ResultCheckStyle.NONE)
在Java项目中,考虑到分区创建会采用job方式自动创建,可以通过function完成创建。
CREATE OR REPLACE FUNCTION "public"."function"()
RETURNS void AS $BODY$
DECLARE
_tablename text ;
quarter integer;
record1 record;
BEGIN
select function(now()) as quarter into record1;
quarter := record1.quarter;
_tablename := 'partition_name';
PERFORM 1
FROM pg_catalog.pg_class c
WHERE c.relname = _tablename;
IF FOUND <> TRUE THEN
EXECUTE 'CREATE TABLE ' || _tablename || ' (
CHECK ( criteria)
) INHERITS (table)';
EXECUTE 'ALTER TABLE ' || _tablename || ' OWNER TO db ';
EXECUTE ' alter table ' || _tablename || ' add CONSTRAINT ' || _tablename||'_pkey PRIMARY key (column) ' ;
EXECUTE ' CREATE INDEX ' || _tablename|| '_index
ON '|| _tablename ||'
USING btree
(column)';
END IF;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100;
ALTER FUNCTION "public"."function"() OWNER TO "db";
参考资料: