Geek Answers Handbook

JPA 2 @SequenceGenerator @GeneratedValue producing a unique constraint violation

Problem Overview

At seemingly random times, we get the exception "postgresql duplicate key violates a unique constraint." I really think I know what our problem is, "but I don’t want to make changes to the code without having a reproducible test case. But since we couldn’t play it in any environments other than random in production, I ask for help from SO.

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

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

We use these sequences to create primary keys for objects such as 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 that I recognize 2 problems with this configuration:

1) Both @SequenceGenerators indicate the same name attribute, even if they should be displayed in different database sequences.

2) The distribution attribute @SequenceGenerator allocS is 50 by default (we use hibernate as the JPA provider), so I think the create sequence syntax should indicate to what extent the sequence should increase, in particular, by 50 to match the distribution.

Based on this assumption, I think the code should be modified 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 experience this, and not ask a question about SO, but again, we could not reproduce this production problem in any other environments. And even in production, a unique violation of restrictions occurs only at seemingly random times.

Questions:

1) Am I correct in my analysis of what changes were supposed to correct this unique violation of restrictions?

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

+7
java postgresql hibernate jpa
source share
3 answers

  • Yes, your analysis is correct. You correctly identified the problem (we had a similar problem). And ... if you are going to put this into production, do not forget:

    • or manually create a sequence table for the generator of a new sequence with the correct initial value / initial identifier (otherwise the sleep mode will start from 1 and you will get it again).
    • or set this value in code (check initalValue in @SequenceGenerator ).

  • I can’t list the best practices, but I believe that you can reduce the limit by 50. I also have no experience with PostgreSQL, but in MySQL you have a simple table for seq. the generator and sleep mode make all the material.

+4
source share

I had a similar problem. In my case, I imported the data directly through SQL. This led to a problem with "hibernate_sequence". Hibernate_sequence was at id 123, but there were rows in my table where the id was greater than 123.

+1
source share

There was the same problem - for some reason, the correct number from the sequence was not selected in hibernate. I tried all the approaches unsuccessfully and finally came to this solution:

 @Entity @Table(name = "events") @SequenceGenerator(name = "events_id_seq", sequenceName = "events_id_seq", allocationSize = 1) public class Event { @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "events_id_seq") private BigInteger id;

I had to put @SequenceGenerator on top of the class, not the method, and the selection size was set to 1 (if you leave this default, it will start generating negative identifiers).

spring-data-jpa 2.1.2 , hibernate 5.3.7 , pg 42.2.5

0
source share

More articles:


All Articles