Weird id values in Postgresql with JPA 2 Sequence generation

Question

The entity has the following annotations on the id column:

@Id
@SequenceGenerator(name = "JOB_MISFIRE_ID_GENERATOR", sequenceName="job_misfire_sequence", allocationSize=10)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator = "JOB_MISFIRE_ID_GENERATOR")
@Column(unique = true, nullable = false)
private Long id;

In the database, I have the following:

CREATE SEQUENCE job_misfire_sequence
  INCREMENT 10
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

And the sequence is used to get the default value for the column.

ALTER TABLE job_misfires
ALTER COLUMN id SET DEFAULT nextval('job_misfire_sequence');

When I do manual inserts by hand into the db using nextval('job_misfire_sequence') , everything works nicely. When the sequence's current value was 1, the following id values were produced:

 SELECT nextval('job_misfire_sequence'); --> 1
 SELECT nextval('job_misfire_sequence'); --> 11
 SELECT nextval('job_misfire_sequence'); --> 21
 SELECT nextval('job_misfire_sequence'); --> 31

But what happens when hibernate inserts a row to this table is that it get's the next value from this sequence (being 41 in this scenario) and multiplies it by 10 and uses that as the id value. This means that the inserted row now has the id value 410.

What am I doing wrong? This situation is going to result in conflicts as hibernate is not using the value provided by the sequence. If I've understood correclty the combination of
allocationSize=10 in the annotation and INCREMENT 10 in the sequence should quarantee that hibernate only has to ask for a new value from the sequence every tenth value. Why is this not happening? Why is the value from the sequence multiplied by 10?

I'm using

• Postgresql 9.0.3
• Hibernate 3.5.5
• Hibernate JPA 2.0 api 1.0.0 final

---

Update 1:

As suggested around the internets, setting the allocationSize value to 1 in the annotation solves this problem. Now the id values are really taken from the sequence in db and I can safely insert rows manually in that table.

But:

• Does having allocationSize=1 cause performance problems?
• Isn't it a massively huge bug that the value from the sequence is not used as is in hibernate but multiplied by the allocationSize value?
• Who is to blame? Hibernate?
• Is there a fix available?

Answer 1

It seems that the correct way to do this is the following:

@Id
@SequenceGenerator(name = "JOB_MISFIRE_ID_GENERATOR", sequenceName="job_misfire_sequence", allocationSize=1)
@GeneratedValue(strategy=GenerationType.SEQUENCE, generator = "JOB_MISFIRE_ID_GENERATOR")
@Column(unique = true, nullable = false)
private Long id;

In the database, I have the following:

CREATE SEQUENCE job_misfire_sequence
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 10;

Now, when I do manual inserts by hand into the db using nextval('job_misfire_sequence') , everything works as expected. When the sequence's current value was 1, the following id values were produced:

 SELECT nextval('job_misfire_sequence'); --> 1
 SELECT nextval('job_misfire_sequence'); --> 2
 SELECT nextval('job_misfire_sequence'); --> 3
 SELECT nextval('job_misfire_sequence'); --> 4

Now hibernate also works as I expect it to. When it inserts a row after I've inserted those 4 rows in one session, the sequence value returned to hibernate is 11. Hibernate uses this as the id value for the first record, 11 for the next and so on. Because I set the CACHE setting to 10 in db, hibernate now only needs to call the sequence once and can then use 10 sequential id values. I confirmed that this is really the case and that the id values do not overlap.

So, the key points are:

• You must use allocationSize=1 in the annotation

If you want to optimize the performance of db inserts, then use

• Use CACHE setting in db with a value greater than 1, but DO NOT touch the allocationSize

To get nice sequential id values

• You must use INCREMENT 1