PostgreSql Partition + Hibernate Insert, postgresqlinsert

PostgreSql Partition + Hibernate Insert, postgresqlinsert

Unlike Oracle, PostgreSQL needs to manually control partition rule triggers.

Step 1: Create a partition

Create table table_partition_1 (CHECK partition_column criteria) INHENRITS (table)

Step 2: Create a primary key and an index for the partition table. btree is used 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 3: manually create a trigger

CREATE OR REPLACE FUNCTION before_insert_table()  RETURNS trigger AS$BODY$ DECLARE  BEGINif criteria  thenEXECUTE '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 is more suitable for batch operations, but it takes up a large amount of overhead for separate operations.

CREATE RULE table_partition_1 _insert AS(criteria ...)DO INSTEAD     INSERT INTO table_partition_1 VALUES (NEW.*)

Step 4: When hibernate inserts data into a partition in trigger mode, the result count obtained is 0, which leads to failure of judgment and rollback. The solution is to use rule or declare that the result check is not performed when the partition is inserted.

@SQLInsert(sql = "INSERT INTO "+ "table(column,...)"+ " VALUES(?,...)", check = ResultCheckStyle.NONE)

In Java projects, you can create partitions by using the function, because partitions are automatically created by job.

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

References: entity-hibspec-customsql.html
Postgresql-table-partitioning-hibernate.html



An error occurred while connecting hibernate to the postgresql database storage object.

Increment
Used to generate a unique identifier for long, short, or int type. Data can be used only when no other process inserts data into the same table. Do not use it in a cluster.

Identity
Supports built-in identity fields for DB2, MySQL, ms SQL Server, Sybase, and HypersonicSQL. The returned identifier is of the long, short, or int type.

Sequence
Use sequence in DB2, PostgreSQL, Oracle, sap db, and McKoi, and generator in Interbase ). The returned identifier is of the long, short, or int type.

Hilo
Use a high/low algorithm to efficiently generate long, short, or int type identifiers. Specify a table and field (hibernate_unique_key and next_hi by default) as the source of the high value. The identifier generated by the high/low algorithm is unique only in a specific database.

Seqhilo
Use a high/low level algorithm to efficiently generate long, short, or int type identifiers and give a database sequence name.

Uuid
Use a 128-bit UUID algorithm to generate string-type identifiers, which are unique in a network (using IP addresses ). UUID is encoded as a 32-bit hexadecimal number string.

Guid
Use the GUID string generated by the database in ms SQL Server and MySQL.

Native
Select one of identity, sequence, or hilo Based on the capabilities of the underlying database.

Assigned
Assign an identifier to the object before saving. This is the default generation policy when the <generator> element is not specified.

When hibernate connects to postgresql, there are special requirements for HQL to automatically convert to the statements required by postgresql.

Yes, as long as you write standard HQL statements.
If you are performing Database Type migration, such as changing from sqlserver to postgreSQL, some local code will not be converted, such as the dateadd function in sqlserver ..

For java database development, it is recommended that you know about JDiy, which is a lightweight database framework. It supports PostgreSql and is very easy to operate databases.