Geek Answers Handbook

Significant id values ​​in Postgresql with JPA 2 sequence generation

The object has the following annotations in 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 manually insert manually into db using nextval ('job_misfire_sequence'), everything works fine. When the sequence current value is 1, the following id values ​​were obtained:

  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 into this table is that it gets the next value from this sequence (in this scenario 41) and multiplies it by 10 and uses this as the id value. This means that the inserted row now has an id value of 410.

What am I doing wrong? This situation will lead to conflicts because sleep mode does not use the value provided by the sequence. If I realized that the combination of allocationSize = 10 in the annotation and INCREMENT 10 in the sequence should ensure that in sleep mode only the new value from the sequence is required for every tenth value. Why is this not happening? Why is the value from the sequence multiplied by 10?

I use

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

Update 1:

As shown in boarding schools, setting the allocize 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 into this table.

But:

  • Does allocSize = 1 have performance issues?
  • Is it not a massive mistake that the value from the sequence is not used, as indicated in sleep mode, but is multiplied by the allocize value?
  • Who's guilty? Hibernate?
  • Is there a fix available?
+4
source share
4 answers

It seems that the correct way to do this is:

 @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 manually insert manually into db using nextval ('job_misfire_sequence'), everything works as expected. When the sequence current value is 1, the following id values ​​were obtained:

  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. When it inserts a row after I inserted these 4 rows in one session, the sequence value returned to sleep mode is 11. Hibernate uses this as the id value for the first record, 11 for the next, and so on. Since I set the CACHE value to 10 in db, hibernate now only needs to call the sequence once and then use 10 consecutive id values. I have confirmed that this is true and that id values ​​do not overlap.

So, the key points:

  • You should use allocSize = 1 in the annotation

If you want to optimize db attachment performance, use

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

To get good sequential identifier values

  • You must use INCREMENT 1
+7
source

as I understand it, nextVal('job_misfire_sequence'); will return you the next SEQUENCE value, so the value you need. Hibernate can ignore this and will assume that the value returned from the database is correct. that way you do not need allocationSize=10 because the database is already returning the correct value.

0
source

I do not know Hibernate, but when a sequence is created with a cache value in PostgreSQL, the cache works based on each connection. This means that if you call nextval () from different sessions (= connections), you can also see this behavior.

Quote from the manual:

Unexpected results can be obtained if the cache setting is more than one, it is used for a sequence object that can be used simultaneously by several sessions. Each session will allocate and cache sequential sequence values ​​during a single access to the object sequence and increase the object sequence last_value accordingly. Then, the next cache-1 uses nextval during this session, just return the predefined values ​​without touching the sequence object. So, any numbers allocated but not used within the session will be lost when that session ends, which leads to “holes” in the sequence

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

0
source

How it works.

When using a sequence generator with allocationSize , Hibernate gets one number from the sequence to generate allocationSize identifiers. Thus, after receiving the value of N from the sequence, it generates identifiers from allocationSize * N to allocationSize * (N + 1) - 1 . Then it gets the next value from the sequence to generate the next group of identifiers. If this next value is N + 1 , the generated identifiers are continuous, so that it expects a seqeunce that produces subsequent numbers.

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

0
source

More articles:


All Articles