POSTGRESQL Partition + Hibernate Insert

Unlike Oracle, PostgreSQL requires manual control of partition rule triggers.

Step one: Create a partition

CREATE table table_partition_1 (CHECK partition_column criteria) inhenrits (table)

Step Two: Create a PK and index for the partition table, use btree here

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);

Step three: Create a trigger manually

CREATE OR REPLACE FUNCTION before_insert_table ()  RETURNS trigger as$body$ DECLARE  beginif criteria  Thenexecute ' INSERT into appropriate table ... '  SELECT ($  ). * '  USING new;end if;return null; END; $BODY $  LANGUAGE plpgsql VOLATILE cost  100; ALTER FUNCTION before_insert_table ()  OWNER to DB;

Rule is more appropriate for bulk operations, but is expensive for individual operations.

CREATE RULE table_partition_1 _insert as (criteria ...) Do INSTEAD     INSERT into table_partition_1 VALUES (new.*)

Step four: Trigger mode when hibernate inserts data into a partition, the resulting result count is 0, which causes the failure of the judgment to be rolled back.The workaround is to use rule, or declare the partition to be inserted without a result check.

@SQLInsert (sql = "INSERT into" + "table (column,...)" + "VALUES (?,...)", check = Resultcheckstyle.none)

in a Java project, consider that partition creation is automatically created by job, and can be created by using the function.

CREATE OR REPLACE FUNCTION "public". " Function "()  RETURNS void as $BODY $  DECLARE    _tablename text;   Quarter Integer;record1 record;  Beginselect function (now ()) as quarter into  record1;  Quarter: = record1.quarter;_tablename: = ' partition_name '; PERFORM 1FROM   pg_catalog.pg_class cwhere  c.relname = _tablename;if FOUND <> TRUE thenexecute ' 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| | ' _indexon ' | |  _tablename | | ' USING btree (column) ';   END IF; end$body$  LANGUAGE ' plpgsql ' VOLATILE cost 100; ALTER FUNCTION "public". " Function "() OWNER to" DB ";

Resources:Entity-hibspec-customsql.html
postgresql-table-partitioning-hibernate.html