up vote 1 down vote favorite
1
share [g+] share [fb]

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.

link|improve this question
90% accept rate
feedback

4 Answers

up vote 4 down vote accepted

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.

link|improve this answer
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! – forker Nov 26 '10 at 23:22
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 – a_horse_with_no_name Nov 26 '10 at 23:25
feedback
up vote 0 down vote

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
link|improve this answer
feedback
up vote 0 down vote

@ Nofate, @Matt and @forker - you guys have provided some conflicting information I'd like to know more about:

  • Using AUTO vs SEQUENCE for Postgres - demolishing sequences is a problem, but so is not assigning values. Are both of these issues with the 3.6 releases? Is there a source of your statements that I can look to for more information?

  • Changing allocationSize to 1 does seem to "correct" this issue, yet you indicate it is not "good practice" and that AUTO should probably be used instead. Again, is there somewhere I can look for more info on this?

link|improve this answer
Upon Matt's answer I've dropped all GenerationType.SEQUENCE and have set all to AUTO and it worked perfectly, i.e. it automatically detects sequence and allocates values one by one. ---- The reason I initially went with GenerationType.SEQUENCE is lack of knowledge - I simply didn't consider automatic generation type. And yes, the version I use is 3.6. – forker Jan 6 '11 at 16:30
I think GenerationType.SEQUENCE is for what we call flexibility. There must be a lot of twisted cases that may require specifying SEQUENCE explicitly. – forker Jan 6 '11 at 16:49
I'm very sorry for confusing. It is not AUTO that is working well, it is GenerationType.IDENTITY. GenerationType.AUTO makes hibernate rely on internal sequencing which I don't find good. – forker Jan 9 '11 at 19:12
That's strange, given that, per the Hibernate reference manual (3.6 release): "IDENTITY: supports identity columns in DB2, MySQL, MS SQL Server, Sybase and HypersonicSQL. The returned identifier is of type long, short or int." - I'm surprised IDENTITY works for PostgreSQL at all. – Jay Jan 19 '11 at 17:25
feedback
up vote 3 down vote

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

link|improve this answer
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). – forker Dec 21 '10 at 21:49
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.