3

I'm using spring data JPA's sequence generator to assign primary keys to entities.

Model contains:

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_post")
@SequenceGenerator(name = "seq_post", allocationSize = 5)
private Long id;

The corresponding sequence definition(for SQL Server DB):

CREATE SEQUENCE dbo.seq_post START WITH 1 INCREMENT BY 5;

Since I wanted to start the ids from 100 instead of 1, so I updated the sql script to

CREATE SEQUENCE dbo.seq_post START WITH 100 INCREMENT BY 5;

And then I encountered the problem as mentioned here. I fixed it by the solution mentioned there.

This made me wonder, when I want the DB sequence to start from 1 then why does this issue does not happen? Based on the answer mentioned here I would expect the ids to not start from 1, but that does not happen. Why is that the case?

JavaLearner
  • 527
  • 1
  • 5
  • 16
  • Because Hibernate is smart enough to not give negative sequence numbers. It is an edge case from this point of view, when you start from 1. – Tamás Pollák May 22 '21 at 12:49
  • No, that's not the case. I had a situation where there were negative ids assigned as PK. – JavaLearner May 22 '21 at 12:56
  • 1
    Well the `start with 1` seems to be a unique exception, for all other values the `allocationSize` is *subtracted*, so you are right, if you set the sequence to e.g. `start with 3`, you'll get the first `ID` negative `(3-allocationSize)` – Marmite Bomber Jun 11 '21 at 15:13
  • Actually `Hibernate` is *open source*, so you should be able to find the source code of the `new_generator_mappings`. You'll find there so line with `if (nextval == 1) ... else ... subtract allocationSize` – Marmite Bomber Jun 11 '21 at 15:19

1 Answers1

2

Well first check if you set the property hibernate.id.new_generator_mappings to true as recomended

Than you are right in adjusting the allocationSize with the sequence INCREMENT BY.

If you want to start the ID with a specific value it seems to obey the following rules:

  • to start with one set the sequence to START WITH 1 (this seems to be an exception)

  • to start with X > 1 set the sequence START WITH X + 50 (accually the same is true for X < 1)

e.g. to start with 5000 with the default allocationSize of 50 define the sequence as follows

create sequence sub_seq
       START WITH 5050
       INCREMENT BY 50
       NOCACHE;

Note that I'm using the NOCACHE option, because I assume Hibernate is the only user of this sequence, so caching is not realy meningfull (and actually replaced with the allocation size.

You also loose between the sessions approx. 1/2 of the allocationSizeof the IDs and you do not want to increase it with additional loss of the cached IDs.

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53
  • Could you also add this https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#identifiers-generators to your answer, so that future readers are aware of it? – JavaLearner Jun 13 '21 at 09:05
  • I had my application up and running and I manually reseted the DB sequence to 1 and my next id started from `1-allocationSize`, hence the confusion. Also I was not able to find the source code where this logic `if (nextval == 1) ... else ... subtract allocationSize` is being performed. It would be great if you could share it. – JavaLearner Jun 13 '21 at 09:11
  • Thanks, for the detailed answer. I didn't knew about the `NOCACHE` option. Other readers might also be interested in https://stackoverflow.com/a/44988410 – JavaLearner Jun 13 '21 at 09:23