Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Hibernate use of PostgreSQL sequence does not affect sequence table

I've configured Hibernate to use PostgreSQL sequence (via annotations) to generate values for primary key id column as follows:

@Id  @SequenceGenerator(name="pk_sequence",sequenceName="entity_id_seq") @GeneratedValue(strategy=GenerationType.SEQUENCE,generator="pk_sequence") @Column(name="id", unique=true, nullable=false) public int getId() {     return this.id; } 

What I see with this configuration is that hibernate is already assigning id values > 3000 on persisting, whereas the query on used sequence shows the following:

database=# select last_value from entity_id_seq; last_value  ------------      69 

(1 row)

Questions:
Is there anything wrong or not?
Should hibernate sync with the sequence table?
If not, where does it store the last generated id?

Thank you.

like image 556
forker Avatar asked Nov 26 '10 21:11

forker


People also ask

Does hibernate work with PostgreSQL?

Out of the box, Hibernate works pretty well with PostgreSQL databases.

What is the use of Hibernate sequence table?

If you use strategy="AUTO" , Hibernate will generate a table called hibernate_sequence to provide the next number for the ID sequence. You may have forgotten to add the AutoIncrement feature to your table's PK.

Can Hibernate use database sequences to generate primary key?

IDENTITY: Hibernate relies on an auto-incremented database column to generate the primary key, SEQUENCE: Hibernate requests the primary key value from a database sequence, TABLE: Hibernate uses a database table to simulate a sequence.

How does sequence work in PostgreSQL?

A sequence in PostgreSQL is a user-defined schema-bound object that yields a sequence of integers based on a specified specification. The CREATE SEQUENCE statement is used to create sequences in PostgreSQL. Now let's analyze the above syntax: First, set the name of the sequence after the CREATE SEQUENCE clause.


1 Answers

I had the same problem. It is related to the id allocating strategies of Hibernate. Whe n you choose GenerationType.SEQUENCE, Hibernate uses HiLo strategy which allocates IDs in blocks of 50 by default. So you can explicitly set allocationSize value like this:

@Id  @SequenceGenerator(name="pk_sequence",sequenceName="entity_id_seq", allocationSize=1) @GeneratedValue(strategy=GenerationType.SEQUENCE,generator="pk_sequence") @Column(name="id", unique=true, nullable=false) public int getId() {     return this.id; } 

Though, I've also heard opinions that using HiLo strategy with allocationSize=1 is not a good practice. Some people recommend to use GenerationType.AUTO instead when you have to deal with database-managed sequences

Update: I did end up going with allocationSize=1, and things seem to work as I expect now. My application is such that I don't really need blocks of IDs anyway, so YMMV.

like image 140
Nofate Avatar answered Oct 12 '22 05:10

Nofate