Primary key generator best practices? (Postgres specific)

[asack]

Guys, we are using postgres 8.1.3 for our application and running into a lot of problems - mainly dealing with primary key management. I have tracked down one issue in that our current configuration does not use the autovacuum stuff in postgres to cleanup the database over a lot of transactions.

But the other issue which is semi related is that EJB3/Hibernate is not dealing with the primary keys correctly? We have instances where EJB3/Hibernate tries to insert a new entity over an already existing primary key? i.e. EJB3/Hibernate does not get the next key properly. All of our tables are using AUTO for primary key generation.

Question, should we be specifing our own generator function? Should we be using @SequenceGenerator annotatoin instead of just relying on AUTO?

(on a side note, how do I specify a generic generator function across multiple tables using annotations?)

Any advice would come much appreciated!

Thanks!

[mazz]

FWIW, our team is successfully using:

@SequenceGenerator
@GeneratedValue (with strategy = GenerationType.SEQUENCE)

without any problems whatsoever with primary key generation on Postgres 8. Our data model has probably over 60 tables with this.

[kpiis]

I use almost the same as [e-mail]

@Entity
@SequenceGenerator(name = "SEQ_STORE", sequenceName = "name_seq")
....
@Id
@Column
@GeneratedValue(generator = "SEQ_STORE")
public Long getId() {
return id;
}

[andydale]

don't forget to add allocationSize = 1 to the @SequenceGenerator annotation, or what is stored migth not match the sequence values.

Cheers,

Andy

[asack]

Thanks guys. Let me ask you this, can lack of VACUUMing cause primary key generation to screw up? Also why isn't AUTO appropriate (which should be database agnostic)?

SEQUENCE stuff isn't really right for MySQL...or I thought?

[asack]

The other thing I find odd is that Hibernate attempts to make it a integer during auto creation of the table instead of a SERIAL type. Why is that?

[kpiis]

"andydale" wrote:
don't forget to add allocationSize = 1 to the @SequenceGenerator annotation, or what is stored migth not match the sequence values.

Cheers,

Andy

You are right, the default value is 50, but if you look in property of an sequence in DB you will see :

CREATE SEQUENCE address_seq
 INCREMENT 1
 MINVALUE 1
 MAXVALUE 9223372036854775807
 START 1
 CACHE 1;
ALTER TABLE address_seq OWNER TO postgres;

and I suppose it's mistake of JPA realization or DB.

[kpiis]

"asack" wrote:
The other thing I find odd is that Hibernate attempts to make it a integer during auto creation of the table instead of a SERIAL type. Why is that?

Can you provide the analogue of serial type in Java?

[asack]

"kpiis" wrote:

"asack" wrote:
The other thing I find odd is that Hibernate attempts to make it a integer during auto creation of the table instead of a SERIAL type. Why is that?

Can you provide the analogue of serial type in Java?

"The type names serial and serial4 are equivalent: both create integer columns."

http://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-SERIAL

Right now we are getting several instances of Hibernate not picking the right primary key during insertion using the AUTO strategy for tables (which I'm doing purposely to be database agnostic). I'm trying to understand why.

[asack]

"asack" wrote:

"kpiis" wrote:

"asack" wrote:
The other thing I find odd is that Hibernate attempts to make it a integer during auto creation of the table instead of a SERIAL type. Why is that?

Can you provide the analogue of serial type in Java?

"The type names serial and serial4 are equivalent: both create integer columns."

http://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-SERIAL

Right now we are getting several instances of Hibernate not picking the right primary key during insertion using the AUTO strategy for tables (which I'm doing purposely to be database agnostic). I'm trying to understand why.

I should mention this is a UNIX system with 404GA-ejb3 profile, java 1.5.0_09 runtime

[asack]

Has anyone ran into this before? What has happened is that after the 45th entry, the 4th one got deleted and then reused on the ensuing merge() call. Now Hibernate is picking the 5th entry which is already taken and as such I get an EntityExists exception.

Also, the AUTO strategy for Postgres devolves into the SequenceGenerator.class for primary key generation so I'm trying to understand why SEQUENCE is any different (perhaps its not)?

Is there a way I can debug this. What I'm trying to do is figure out where exactly Hibernate executes the save - I think I almost have it but debugging is a real pain in the Eclipse...

Any freebees or pointers would be most appreciated as we've seen this across multiple tables.

[asack]

Has anyone ran into this before? What has happened is that after the 45th entry, the 4th one got deleted and then reused on the ensuing merge() call. Now Hibernate is picking the 5th entry which is already taken and as such I get an EntityExists exception.

Also, the AUTO strategy for Postgres devolves into the SequenceGenerator.class for primary key generation so I'm trying to understand why SEQUENCE is any different (perhaps its not)?

Is there a way I can debug this. What I'm trying to do is figure out where exactly Hibernate executes the save - I think I almost have it but debugging is a real pain in the Eclipse...

Any freebees or pointers would be most appreciated as we've seen this across multiple tables.

[asack]

Appologizes for the double post!!

[asack]

Sorry, one more question, the default strategy for the PostgresDialect is Sequence yet the table as I see it so far does not contain a table_column_sequence attribute. I noticed that sometimes Hibernate uses a hibernate_sequence variable in postgres to manage a table (but no others)....so I'm still confused right now how this really works....

Cheers!