Hibernate use of PostgreSQL sequence does not affect sequence table

Question

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.

Mekong

Answer 1

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.

Decimalize

thank you mind brother! I noticed allocationSize since the very beginning but thought it is about number of invocations and assumed that the sequence should be OK with any value. Thanks again! – Mekong

I assume this is done by Hibernate for performance reasons (less roundtrips to the database). If you experience contention on the sequence itself with a low allocationSize (slow nextval() calls) you might consider setting a cache for the sequence itself in PostgreSQL – Exequies

Doesn't setting the allocation size 1 affect jdbc batching? – Mejia

Answer 2

DO NOT USE GenerationType.SEQUENCE for Postgres sequences!

It's completely counter-intuitive, but the Hibernate folks completely messed up on this. You must use GenerationType.AUTO or Hibernate will demolish your sequences if you have to restart/rebuild your DB. It's almost criminally negligent that they would allow this code to go into a production build, but the Hibernate team is rather famous for their bull-headed stances towards flatly-wrong positions (check out their position on LEFT JOINs, for instance).

Panfish

Good point, Matt! but I think there is an excuse for using GenerationType.SEQUENCE: if you have a table inherited from another one with serial pk inherited, consequently sharing a sequence for it, GenerationType.AUTO will fail to assign a correct value for serial pk (it will run into a duplicated value). – Mekong

I strongly disagree. You must simply specify a SequenceGenerator to go with it, and (if you wish to interoperate with non-Hibernate applications) set the allocationSize to 1. In newer releases you can instead use hibernate.id.new_generator_mappings=true to restore sanity; that may not've been true when you wrote this, but the allocationSize option certainly was. – Prefabricate

Some external info that backs up your claims would be helpful, such as bug reports. – Mejia

Answer 3

First, you have to determine which version of Hibernate you are using. In terms of hibernate-core versions, 3.2 onwards introduced more consistent support for id generators especially in regards to defined in annotations. See http://in.relation.to/Bloggers/New323HibernateIdentifierGenerators for a discussion.

Next 3.6 introduced a setting ('hibernate.id.new_generator_mappings') which makes the generators discussed in that blog the default way JPA-annotations are handled. The setting is false by default because Hibernate has to maintain backwards compatibility with older versions. If you want the new behavior (which is completely recommended) then simply set that setting to true.

How GenerationType is handled depends on which version you are using and whether you have 'hibernate.id.new_generator_mappings' set to true. I will assume you are using 3.6+ (since anything older is, well, old) and do have 'hibernate.id.new_generator_mappings' set to true (since that is the recommendation for new apps):

1. GenerationType.AUTO -> treated as GenerationType.SEQUENCE
2. GenerationType.SEQUENCE -> maps to the org.hibernate.id.enhanced.SequenceStyleGenerator class discussed in the blog
3. GenerationType.TABLE -> maps to the org.hibernate.id.enhanced.TableGenerator class discussed in the blog

Cosette

Answer 4

In Postgres I would do this:

@Id 
@SequenceGenerator(name="pk_sequence",sequenceName="\"entity_id_seq\"")
@GeneratedValue(strategy=GenerationType.SEQUENCE,generator="\"pk_sequence\"")
@Column(name="\"id\"", unique=true)
private int id;

Mostly with uppercase names Hibernate need to be passed escaped quotes in order to understand Postgres and find the tables, columns or sequences names.

Abney