Hibernate: Selecting a Primary Key’s Generation Type

Recently we’ve been working on a new Application that we develop in using HSQLDB, but intend to run on PostgreSQL.

So today I ran into a problem with one of my entities but only when I run it onĀ  POSTGRESQL. Here’s the stack trace:

org.postgresql.util.PSQLException: ERROR: duplicate key violates unique constraint "pk_trade_item_ownership"

After digging I found out what the problem is:

There’s a sequence on the table that we defined with a current value of 1 but there’s test data already inserted into that database with an id of 1

Why?

  • First Liquibase:
    • I’m using Liquibase to create my schema, migrate my database, and create my test data.
    • In liquibase I create my table like this:
      <createTable tableName="organization">
      			<column name="id" type="bigint" autoIncrement="true">
      				<constraints primaryKey="true" />
      			</column>
    • Notice autoIncrement=”true”
      • In HSQLDB this defaults to increment the max id by one.
      • In Postgres this defaults to using a sequence
  • Second Hibernate:
    • In Hibernate I’m setting my entity PK id to GenerationType.IDENTITY
      @Id
      	@Column
      	@GeneratedValue(strategy=GenerationType.IDENTITY)
      	private Long id;
    • Like with Liquibase this defaults to:
      • In HSQLDB this defaults to increment the max id by one.
      • In Postgres this defaults to using a sequence
  • Third and most importantly importing of Test Data
    • In itself this what Liquibase and Hibernate is doing is NOT a problem. The real issue is that we’re importing quite a bit of TEST DATAwith set id’s to manage the relationships between tables.
      • For this reason we essentially circumvent the Postgres sequence which remains at 1, and causes the problem.
      • (No problem in HSQLDB with just increments whatever the max id is)

Solution:

  • Use negative numbers in test data. This is what we did and it worked like a charm and was easy.
  • Define a POSTGRESQL only changeset in Liquibase that sets the sequence number. We opted not to do this as I hate mucking up deployment more than is absolutely necessary.
  • Apparently you can also state a starting number when using autoIncrement in liquibase which may be another way around this.

It's only fair to share...
Share on FacebookGoogle+Tweet about this on TwitterShare on LinkedIn

Leave a Reply