Why is JPA entity manager resulting in duplicate key value errors using glassfish?

Question

I currently have about 3 messages from a JMS client sending to a MDB every few seconds. The MDB is using a JPA entity manager to persist the data to PostgreSQL. This works fine for about a minute or so.

Then the problem becomes this message ...

Caused by: javax.ejb.EJBException: Exception [EclipseLink-4002] (Eclipse Persistence
Services - 2.3.2.v20111125-r10461):org.eclipse.persistence.exceptions.DatabaseExcep
tion Internal Exception: org.postgresql.util.PSQLException: ERROR: duplicate key value
violates unique constraint "scan_pkey"
Detail: Key (id)=(984) already exists.
Error Code: 0
Call: INSERT INTO Scan (id, th, ag, dl) VALUES (?, ?, ?, ?
)
    bind => [11 parameters bound]

Heres my Scan entity.

@Entity
@SequenceGenerator(name="scanIdGen", initialValue=1000, sequenceName="scan_seq")
@Table(name="Scan")
public class Scan implements Serializable {
    @Id
    @Column(name="id")
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="scanIdGen")
    private Integer id;

    @Column(name="th")
    private Integer th;

    @Column(name="ag")
    private Integer ag;

    @Column(name="dl")
    private Integer dl;

    // ...
}

And here's the database schema.

CREATE SEQUENCE scan_seq START 1000;
CREATE TABLE Scan (
    id       INTEGER DEFAULT NEXTVAL('scan_seq') PRIMARY KEY    
    th       INTEGER DEFAULT NULL,
    ag       INTEGER DEFAULT NULL,
    dl       INTEGER DEFAULT NULL
);

Another seemingly not so big problem is that my primary keys don't really start at 1000, but usually at 950. Don't know why.

Any help much appreciated.

Answer 1

At a guess Glassfish/EclipseLink expects the default sequence increment of 50 specified (for some insane reason) by the JPA standard. Your PostgreSQL sequence defaults to an increment of 1.

Specify an allocationSize of 1 in your @SequenceGenerator, eg:

@SequenceGenerator(name="scanIdGen", sequenceName="scan_seq", allocationSize=1)

or use the IDENTITY generation strategy.