6

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?
kosoant
  • 11,619
  • 7
  • 31
  • 37
  • find the right solution at http://stackoverflow.com/questions/12745751/hibernate-sequencegenerator-and-allocationsize – Iker Jimenez Jan 18 '13 at 11:23

4 Answers4

8

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
kosoant
  • 11,619
  • 7
  • 31
  • 37
  • Thanks, changing `allocationSize` to 1 helped me! Before id was getting multiplied by 50. lol – Jaanus Sep 23 '11 at 11:52
  • This is not right. With allocationSize=1 you will hit the DB every time. What you need is hibernate.id.new_generator_mappings=true . See http://stackoverflow.com/questions/12745751/hibernate-sequencegenerator-and-allocationsize – Iker Jimenez Jan 18 '13 at 11:22
0

the way I understand it, nextVal('job_misfire_sequence'); will return you the next SEQUENCE value, thus the value you need. Hibernate can abstract from this, and will assume that the value returned from the DB is correct. thus, you don't need allocationSize=10, because the database already returns the right value.

iliaden
  • 3,791
  • 8
  • 38
  • 50
  • yes, and hibernate absolutely should expect the value from db to be correct. But the documentation of allocationSize says: "The amount to increment by when allocating sequence numbers from the sequence." It says nothing about multiplying. Using allocationSize 100 together with INCREMENT 100 should make hibernate behave so that it only must request the sequence value once and then it can safely allocate id values to the next 100 record. That's at least how I understand this. – kosoant Jun 15 '11 at 16:00
  • I know this may be a bit chatty, but do you have an issue with hibernate sending queries to fetch the next sequence number at the creation of each new object? This is how my code currently works... For a benchmark: fetching ~650000 sequence numbers in separate queries took about 1 minute. – iliaden Jun 15 '11 at 16:11
  • That's good to know. So this "optimization" is most likely completely unnecessary. – kosoant Jun 15 '11 at 16:28
0

I don't know Hibernate, but when the sequence is created with a cache value in PostgreSQL that cache operates on a per connection basis. Which means that if you call the nextval() from different sessions (= connections) you might see this behaviour as well.

Quote from the manual:

Unexpected results might be obtained if a cache setting greater than one is used for a sequence object that will be used concurrently by multiple sessions. Each session will allocate and cache successive sequence values during one access to the sequence object and increase the sequence object's last_value accordingly. Then, the next cache-1 uses of nextval within that session simply return the preallocated values without touching the sequence object. So, any numbers allocated but not used within a session will be lost when that session ends, resulting in "holes" in the sequence

Make sure you read the "Notes" section in the manual: http://www.postgresql.org/docs/current/static/sql-createsequence.html

  • The cache setting is effectively not used in my sequence – kosoant Jun 15 '11 at 15:58
  • The unexpected results they mention in the manual are about gaps in the generated id values. This is in no way a problem. Overlapping id values would be but using CACHE>1 does not cause that. – kosoant Jun 15 '11 at 16:14
0

That's how it works.

When using sequence generator with allocationSize, Hibernate obtains a single number from the sequence to generate allocationSize identifiers. Thus, after obtaining value N from the sequence, it generates identifiers from allocationSize * N to allocationSize * (N + 1) - 1. Then it obtains the next value from the sequence to generate next bunch of identifiers. If that next value is N + 1, generated identifiers are continuous, so that it expects seqeunce which produces consequent numbers.

So, there is no need to specify increment in the sequence definition.

axtavt
  • 239,438
  • 41
  • 511
  • 482
  • The documentation of allocationSize says: "The amount to increment by when allocating sequence numbers from the sequence." It says nothing about multiplying. But this is not the problem here. The problem is that hibernate or I will eventually try to insert rows to that table with conflicting id values. This cannot be how this is supposed to work. And how would changing the db sequence's INCREMENT value help? – kosoant Jun 15 '11 at 16:02
  • @kosoant: If you want to produce several identifier from a single sequence value you have to use multiplication, do you understand it? – axtavt Jun 15 '11 at 16:11
  • No, I do not understand why multiplication would have to be used to get multiple id values *from a single nextval('sequence') query* With a single value you have to use multiplication, but that's not the point in my question. And as you can see from my own answer to this question, non-multiplication based "produce several identifiers from a single sequence query" is in fact possible – kosoant Jun 15 '11 at 16:30