Hibernate generated id cause error message : "key already exists"

Question

I'm using spring app with hibernate and postgres to store data. The configuration for the product entity is as follow :

/**
 * A Product.
 */
@Entity
@Table(name = "product")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
@Document(indexName = "product")
public class Product implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
    @SequenceGenerator(name = "sequenceGenerator")
    private Long id;

    ...

}

When I want to create a product using the web app I get the following error duplicate key value violates unique constraint. Detail : the key (id)=(35018) already exists.

From my understanding hibernate use a sequence in db to generate the next id value. So I did SELECT c.relname FROM pg_class c WHERE c.relkind = 'S'; in the psql shell to get all sequence in my db. The output is :

hibernate_sequence
 jhi_user_id_seq
 key_value_id_seq
 jhi_persistent_audit_event_event_id_seq
 unit_id_seq
 generic_user_id_seq
 currency_id_seq
 customer_type_id_seq
 customer_exploitation_type_id_seq
 legal_entity_id_seq
 deposit_id_seq
 machine_id_seq
 tank_id_seq
 address_id_seq
 product_id_seq
 rewarded_file_id_seq
 bar_code_type_id_seq
 quality_label_id_seq
 shop_pdv_id_seq
 brand_id_seq
 category_id_seq
 material_id_seq
 ws_call_id_seq
 postal_code_id_seq
 commune_id_seq
 country_id_seq
 event_id_seq
 event_type_id_seq
 key_blob_id_seq
 card_id_seq

So I thought nice I have a product_id_seq and I only have to update the value in it for things to work. But when I request the value with SELECT * FROM product_id_seq; I get :

 last_value | log_cnt | is_called
------------+---------+-----------
     100616 |       0 | t

So here I think that the id generated for the product id is not coming from this product_id_sequence since it tries to insert a product with id = 35018 and the product_id_seq last value is 100616.

So I wonder where does this id 35018 comes from? Which sequence is used to generate it? My guess is I have to update this mysterious sequence to get things to work. For info the hibernate sequence has a value of 36400.

Any idea that could get me going? Thanks in advance.

Answer 1

You do not map your sequence with postgre sequence so Hibernate creates the sequence hibernate_sequence (the one you got 35018 from) for itself.

To use your existing sequence:

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "sequenceGenerator")
@SequenceGenerator(name = "sequenceGenerator",  sequenceName = "product_id_seq")
private Long id;