Answer a question

Problem Overview

At seemingly random times we get an exception "postgresql duplicate key violates unique constraint." I do think I know what our problem"s" are but I don't want to make changes to the code without having a reproducible test case. But since we haven't been able to reproduce it in any environment other than randomly in production, I'm asking assistance from SO.

In this project we have multiple postgres databases, and a primary key sequence configured for each table in each database. These sequences are created like this:

create sequence PERSONS_SEQ;
create sequence VISITS_SEQ;
etc...

We use these sequences to generate the primary keys for the entities like this:

@Entity
@Table(name = "visits")
public class Visit {
  @Id
  @Column(name = "id")
  @SequenceGenerator(name = "seq", sequenceName = "visits_seq")
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq")
  private int id;
  ...
}

@Entity
@Table(name = "person")
public class Person {
  @Id
  @Column(name = "id")
  @SequenceGenerator(name = "seq", sequenceName = "persons_seq")
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq")
  private int id;
  ...
}

Analysis

I think I recognize 2 problems with this configuration:

1. Both @SequenceGenerators specify the same name attribute even though they are supposed to map to different database sequences.

2. The @SequenceGenerator allocationSize attribute defaults to 50 (we're using hibernate as the JPA provider) so I think the create sequence syntax should specify how much the sequence should increment by, specifically by 50 to match the allocationSize.

Based on this guess, I think the code should be modified to something like this:

create sequence PERSONS_SEQ increment by 50;
create sequence VISITS_SEQ increment by 50;
etc...

@Entity
@Table(name = "visits")
public class Visit {
  @Id
  @Column(name = "id")
  @SequenceGenerator(name = "visits_seq", sequenceName = "visits_seq")
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "visits_seq")
  private int id;
  ...
}

@Entity
@Table(name = "person")
public class Person {
  @Id
  @Column(name = "id")
  @SequenceGenerator(name = "persons_seq", sequenceName = "persons_seq")
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "persons_seq")
  private int id;
  ...
}

I would just test this rather than asking the question on SO, but again, we have not been able to reproduce this production issue in any other environments. And even in production the unique constraint violation only occurs at seemingly random times.

Questions:

1. Am I correct in my analysis of what the changes should be to fix this unique constraint violation?

2. What are the best practices for using sequence generators when using hibernate as a JPA provider?

Answers

1. Yes, your analysis is correct. You identified correctly the problem (we had a similar problem). And... if you gonna put that in production, don't forget to:

   • either generate manually the sequence table for the new sequence generator WITH the correct initial value/initial ID (otherwise hibernate will begin from 1 and you will get again )
   • or set that value in Code (check initalValue in @SequenceGenerator).

2. I am not able to enumerate the best practices, but I suppose you could lower the limit of 50. Also I do not have experience with PostgreSQL, but in MySQL you have a simple table for the seq. generator and hibernate makes the entire stuff.