JPA and Postgres sequence pre-allocation size setup incorrectly

Question

I am not able to perist any Entity because of a problem with sequence. I use Glssfish 4, Postgres 9.3 + JPA + EJB3 and Netbeans 8. Below the excpeption:

    Finest:   persist() operation called on: MyUser{id=null, email=a@e.it, password=test,         firstname=test, lastname=test, company=Test}.
    Finest:   Execute query ValueReadQuery(sql="select nextval('mom_seq_id')")
    Finest:   Connection acquired from connection pool [read].
    Finest:   reconnecting to external connection pool
    Fine:   select nextval(mom_seq_id)
    Finest:   Connection released to connection pool [read].
    Warning:   Local Exception Stack: 
    Exception [EclipseLink-7027] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.ValidationException
    Exception Description: The sequence named [mom_seq_id] is setup incorrectly.  Its increment does not match its pre-allocation size.
at org.eclipse.persistence.exceptions.ValidationException.sequenceSetupIncorrectly(ValidationException.java:1604)
at org.eclipse.persistence.sequencing.StandardSequence.createVector(StandardSequence.java:96)
    ...

The sequence on Postgres:

CREATE SEQUENCE my_seq_id
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 27
  CACHE 1;
ALTER TABLE my_seq_id
  OWNER TO postgres;
COMMENT ON SEQUENCE my_seq_id
  IS 'Sequence for autoincrement id on MyClass';

And an extract of my Entity:

@Entity
@Table(name = "myuser")
@XmlRootElement
public class MyUser implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@SequenceGenerator(name="MYSEQ",
                   sequenceName="my_seq_id")
@GeneratedValue(strategy = GenerationType.SEQUENCE,
                generator="MYSEQ")
@Basic(optional = false)
@Column(name = "id")
private Integer id;

Can anyone explain what is wrong? Thanks

I resolved my issue but I don't know why! I saw that the default value of allocationSize() is 50:

package javax.persistence;

@Target(value = {ElementType.TYPE, ElementType.METHOD, ElementType.FIELD})
@Retention(value = RetentionPolicy.RUNTIME)
public @interface SequenceGenerator {

    public String name();

    public String sequenceName() default "";

    public String catalog() default "";

    public String schema() default "";

    public int initialValue() default 1;

    public int allocationSize() default 50;
}

And I have updated my Postgres sequence increment_by value from 1 to 50 and now it works!

If you solved the problem on your own you should delete the question or post and accept an answer. — unwichtich, Jun 01 '14 at 22:02

score 6 · Answer 1 · answered Jun 02 '14 at 06:28

For reasons beyond my understanding, the JPA spec picked 50 as the default increment for a sequence generator.

PostgreSQL defaults to 1.

If the two don't match, things get ugly, because JPA thinks it can use values that someone else also thinks they have assigned. At least EclipseLink detects this; Hibernate just goes on merrily trying to re-use already assigned keys.

If your sequence is:

CREATE SEQUENCE my_seq_id
  INCREMENT 1

then your mapping must reflect that:

@SequenceGenerator(name="MYSEQ",
                   sequenceName="my_seq_id", allocationSize=1)

I strongly suggest being explicit about the increment, even if you leave it at the default of 50 and alter the PostgreSQL sequence instead. It'll save your and others' sanity when debugging later.

Initially I specified the allocationSize=1 attribute into @SequenceGenerator annotation having INCREMENT 1 into my_seq_id Postgres sequence, but it did not work. I forgot to mention that I am using EclipseLink. — , Jun 03 '14 at 07:45

score 4 · Accepted Answer · answered Jun 03 '14 at 07:51

Changing the value of INCREMENT from 1 to 50 into my Postgres sequence resolved the issue. As suggested by @unwichtich it is a good idea to specify allocationSize=50 attribute through the @SequenceGenerator annotation.

JPA and Postgres sequence pre-allocation size setup incorrectly

2 Answers2