Old Sequence Value for JPA Generated ID using Eclipselink

1
votes

The generated id for a JPA entity generates an "old", used number, when Eclipselink is used as ORM, but with Hibernate the id is the correct next value of the sequence. In the entity class I use these annotations with the id field

@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "s_customer")
@SequenceGenerator(name = "s_customer", sequenceName = "S_CUSTOMER")
private Long id;

I use flyway to setup and populate the db:

public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    jdbcTemplate.execute("create sequence S_CUSTOMER");
    jdbcTemplate.execute("create table CUSTOMER (" +
            "ID BIGINT PRIMARY KEY," +
            "DISPLAY_NAME VARCHAR(3000)," +
            "VERSION BIGINT" +
            ")");
}


public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    for (int i = 0; i < 10000; i++) {
        jdbcTemplate.update(
                "insert into CUSTOMER (ID, DISPLAY_NAME, VERSION) " + "select nextval('S_CUSTOMER'), ?, ? ",
                "SAP-SE", 0);
    }
}

As database I use PostgreSQL.

When my application is going to persist a new entity, using eclipselink, I get an error because the entity got an "old" id:

Call: INSERT INTO CUSTOMER (ID, DISPLAY_NAME, VERSION) VALUES (?, ?, ?)
bind => [3 parameters bound]
Query: InsertObjectQuery(Customer(id=9959, displayName=Bäckerei Brötchen, version=1)); nested exception is javax.persistence.RollbackException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.3.v20160428-59c81c5): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "customer_pkey"
Detail: Key (id)=(9959) already exists.

But when I manually select the current value of the sequence I get the value "10004". So why does eclipselink return a sequence value already used? The same thing with hibernate works just fine.

2
hibernatejpaeclipselinkspring-data-jpa
Your sequence allocation isn't specified, and EclipseLink is defaulting to 50. Try setting allocationSize=1 within the SequenceGenerator – Chris
With allocationSize=1 the sequence get's incremented properly. But does this imply that the ORM goes for each new sequence number to the db? I thought the allocationSize was meant for optimizing the performance by fetching more then just one sequence number. – Gregor
Yes, and it needs to match the allocation size you have configured for the sequence in the database. You will get duplicates or wasted numbers if they are off. 1 means the provider must go to the sequence each time it needs a number, while 50 means it can use the previous 50 numbers when it increments the sequence object. – Chris
Right. My problem is, that with an allocation size of 50 and 10000 initial entries created with flyway eclipselink starts to allocate beginning with 9051, which is already used! – Gregor
That seems to mean you are not setting the allocation size to match the sequence size. The next value in the sequence should give you 50 numbers more than the last value used by any other process, flyway included. Ie, if the last number used is 10000, the very next call to nextval should return 10050, allowing EclipseLink to use 10001-10050 before needing to obtain more numbers. Just setting the allocation size in JPA isn't enough to allow preallocation, you need to set it in the underlying sequence object. – Chris

2 Answers

0
votes

I'd already same allocationSize as defined in DB sequence, but still sporadically was getting old sequence value.

-1
votes

Although this is an old question, recently I had the same problem.

The thing is that the allocationSize (by default 50) should be the same with the clause INCREMENT (by default 1) when you created the sequence in the DB. So if you want to have a sequence that it will be incremented let's say by 100 you should write something like that:

CREATE SEQUENCE my_seq_id
  INCREMENT 100
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1;

And the JPA entity should be like that:

@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "my_seq_id")
@SequenceGenerator(name = "my_seq_id", sequenceName = "my_seq_id", allocationSize = 100)
private Long id;