PostgreSql Partition + Hibernate Insert

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

与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";