Weird behaviour of Postgres Id Generator

pierdeux · **Posted:** Wed Dec 19, 2007 2:00 am

Hibernate version: 3.2
Hibernate-Annotations: 3.3.0GA
PostgreSQL: 8.2.4

I have seen a number of posts on PostgreSQL and IDs, but nothing quite like what I experienced.

In an empty "Users" table, I added three users: one with ID=1, one with ID=2 and one with ID=63.

From then on, I delegated the rest of the job to my application, through Hibernate. I was expecting the following users to be numbered 64, 65, 66, etc. But the users seem to be numbered 200, 700, 1200, 1700, etc.

Has anyone ever seen anything like it?

Here is the User object with its Annotations:

Code:

@Entity @Table(name="users", schema="users")
@SequenceGenerator(name="USER_SEQ", sequenceName="users.users_id_seq")
public class User extends AbstractPersistentObject {
   private Long id;
   private String username;
   // etc.

   public User() { /* do nothing */ }
   
   @Id @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="USER_SEQ")
   public Long getId() { return id; }
   public void setId(Long id) { this.id = id; }
   // etc.
}

And here is the definition of the PostgreSQL sequence, according to pgAdmin:

Code:

CREATE SEQUENCE users.users_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 46
  CACHE 1;

andydale · **Posted:** Wed Dec 19, 2007 2:59 am

Hi,

In your SequenceGenrator annotation try adding the following to it

Code:

allocationSize = 1, initialValue= 1

(you should be alright with just the allocationSize).

Cheers,

Andy

csavolainen · **Joined:** Tue Dec 18, 2007 1:40 pm **Posts:** 6

I have noticed that by default ( I think ) Hibernate will use just 1 sequence for all of it's values. Is it possible that other Entities have been chewing up sequence values?

andydale · **Posted:** Thu Dec 20, 2007 3:35 am

Csavolainen,

I don't think this can be the cause as a specific sequence would be generated by Hibernate. This is because the @SequenceGenerator annotation is used, and in pierdeux's case it should of created a sequence called users_id_seq in the users schema. The entity User then uses this specific sequence.

I know from personal experience that if the allocationSize is not specified in the @SequenceGenerator annotation, then some kind of hi-lo algorithm for id generation.

Cheers,

Andy

pierdeux · **Posted:** Sat Feb 09, 2008 12:10 am

Thank you, Andydale. Sorry for the very long delay in coming back to this, but I am glad to confirm that your solution was 100% what I needed.

Csavolainen, my setup is such that, although I do have several entities generated with IDs, each has its own sequence, and Hibernate seems to distinguish between them and use each at the right place and the right time. Thank you nonetheless for your tip!

kk4hibernate · **Joined:** Tue May 13, 2008 5:22 am **Posts:** 7

I have a similar problem, though i have specified the increment should be by 1 it always increments by 4. I still don know y this happens..
I also did the above solution still it increments by 4.
it goes on like 109732, 109736, 109740

I also want to know y it started with 109732

Can anybody help me with this ,,,,
please..
Thanks in advance..

here is the code:

@Entity
@Table(name = "client")
@javax.persistence.SequenceGenerator(
name="PID_SEQ",
sequenceName="pid_sequence",

allocationSize=20
)

@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="PID_SEQ")
public Long getPtId() {
return pId;
}
public void setPId(Long pId) {
this.pId = pId;
}

definition of the PostgreSQL sequence follows:

CREATE SEQUENCE pid_sequence
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;

Regards,
kavya

andydale · **Posted:** Tue May 13, 2008 6:18 am

Hi,

accroding to the @SequenceGenerator annotation, the actual assigned value to the entity will be in gaps of 20, so sequence vaule 1 is actually Id 20.

With the gap of 4, is it possible that you have more than 1 entity using the pid_sequence ?

Cheers,

Andy