Hibernate / JPA using wrong sequence values with PostgreSQL

tperrigo · **Posted:** Tue Nov 18, 2008 4:39 pm

I have two projects-- one a JBoss application, one a standalone Java application-- that both use the same JPA entities (Hibernate 3.3.1GA, Hibernate Entity Manager 3.4.0GA, JBoss 5.0.0.CR2). Both apps use the same orm.xml file (we use xml mapping instead of annotations because we have to support multiple databases). The entities are mapped as follows in the orm.xml file:

Code:

   <entity class="werner.opt.logos.config.Profile" metadata-complete="true" access="FIELD">
      <table name="profile" schema="config"/>
      <attributes>
         <id name="id">
            <column name="profile_id" />
            <generated-value strategy="SEQUENCE"
               generator="PROFILE_ID_SEQ_GEN"/>
            <sequence-generator name="PROFILE_ID_SEQ_GEN"
               sequence-name="config.profile_profile_id_seq"/>
         </id>
         <basic name="name" />
         <basic name="processPath">
            <column name="process_path"/>
         </basic>
      </attributes>
   </entity>

The peristence.xml files are similar; the only difference being that the JBoss app uses a datasource, so it does not contain the database connection info. When the JBoss application inserts entities, the primary keys are generated from the sequence associated to the table. However, when the standalone application inserts entities, it inserts primary keys that do not come from the table's sequence (and each subsequent insert seems to increment the primary key by 50). When I ran the standalone app with "show_sql" set to true, here's what I found in the log file:

Code:

DEBUG [main] (SQLStatementLogger.java:111) - 
    select
        nextval ('config.profile_profile_id_seq')
Hibernate: 
    select
        nextval ('config.profile_profile_id_seq')
DEBUG [main] (SequenceGenerator.java:105) - Sequence identifier generated: 10
DEBUG [main] (AbstractBatcher.java:418) - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
DEBUG [main] (SequenceHiLoGenerator.java:85) - new hi value: 10
DEBUG [main] (AbstractSaveEventListener.java:135) - generated identifier: 500, using strategy: org.hibernate.id.SequenceHiLoGenerator
DEBUG [main] (JDBCTransaction.java:134) - commit

It looks like nextval is being called on the appropriate sequence (and the correct sequence value returned), but then this value is ignored and instead the org.hibernate.id.SequenceHiLoGenerator is called, which returns an identifier in no way associated with the value returned by the sequence; it is this value which is inserted when the entity is persisted.

Why isn't the standalone app using the value returned from the Postgres sequence? Why the subsequent call to the HiLoGenerator? And why does it work as expected in the JBoss application?

My team has spent all day trying to figure out this issue. Since we will be inserting some records manually (not through the ORM layer), we need to make sure that the entities get their primary keys from the sequence associated to their table. Any help would be greatly appreciated!

Thanks,
Tim

tperrigo · **Posted:** Thu Nov 20, 2008 11:04 am

Does anyone have any thoughts / suggestions on this issue? I am completely stumped here...I have no idea why the standalone app is using ids from the HiLo generator, while the same entities (mapped with the same orm.xml file) use the values from the postgres sequence (which is the expected behavior). If there is any way to change my orm.xml mappping to guarantee that the primary keys are generated using the Postgres sequence associated to the table, please let me know (I thought it was set up that way currently, but somehow the standalone app is using a different generator, despite the specification in the orm.xml).

Any help would be very much appreciated...Thank you!

avasey · **Joined:** Wed Feb 25, 2009 2:42 am **Posts:** 2

Any luck resolving this? I have started to get the same issue with the sequence returning a correct id but the hi lo generator then uses a different id (that actually already exists in the database so the data can not be successfully inserted). Why is the hi lo sequence called at all?

Postgresql 8.1/8.3
Hibernate 3.3.1

Code:

Hibernate: 
    select
        nextval ('productprice_productpriceid_seq')
2009-02-25 08:00:39 DEBUG org.hibernate.id.SequenceGenerator - Sequence identifier generated: 1250208644
2009-02-25 08:00:39 DEBUG org.hibernate.id.SequenceHiLoGenerator - new hi value: 1250208644
2009-02-25 08:00:39 DEBUG org.hibernate.event.def.AbstractSaveEventListener - generated identifier: -1914077240, using strategy: org.hibernate.id.SequenceHiLoGenerator
2009-02-25 08:00:39 ERROR za.co.pnp.product.model.ProductPrice - Set the productPriceId:-1914077240
.....
2009-02-25 08:00:39 ERROR org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update

Code:

 
@Entity
@Table(name = "productprice")
@SequenceGenerator(name = "productprice_productpriceid_seq", sequenceName = "productprice_productpriceid_seq")
public class ProductPrice implements Comparable<ProductPrice>, Serializable {

@Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "productprice_productpriceid_seq")
  @Column(name = "productpriceid", nullable = false)
  public int getProductPriceId() {
    return productPriceId;
  }

Thanks

avasey · **Joined:** Wed Feb 25, 2009 2:42 am **Posts:** 2

To answer my own question, add the allocation size to the SequenceGenerator seems to get rid of the incorrect use of hi lo generator:

@SequenceGenerator(name = "productprice_productpriceid_seq", sequenceName = "productprice_productpriceid_seq", allocationSize=1)

tsa236 · **Joined:** Sat Feb 28, 2009 4:25 pm **Posts:** 1

I think by default hibernate maps javax.persistence strategy SEQUENCE to org.hibernate strategy "seqhilo".

The simple solution for annotations is:

@Id
@GeneratedValue(generator = "SeqName")
@org.hibernate.annotations.GenericGenerator(name = "SeqName", strategy = "sequence", parameters = { @Parameter(name = "sequence", value = "productprice_productpriceid_seq") })
private Long id;