Gaps In the sequence values generated by JPA @GeneratedValue with PostgreSQL

Question

For my desktop application I use JavaFX, Spring, JPA + Hibernate and PostgreSQL. Currently I have faced several issues.

Issue one: Violation of PRIMARY KEY constraint SQL Error

When I create Entity classes according to following manner (GenerationType as AUTO) it works fine. But when I create a new data base and add some Test data with sql script (as showing following picture) and try to insert some data with my application I have got 'Violation of PRIMARY KEY constraint' SQL Error. That mean it seems Hibernate try to generated PK values which are already available (allocated with my test data ex 1, 2, 3 etc). But after 5 attempts (exceed the test data maximum pk value) it was fine and start the data inserting with PK key value with 6.

Entity Class - GenerationType as AUTO

@Entity
@Table(name = "devicetype")
public class Devicetype implements Serializable {

    @Id
    @Basic(optional = false)
    @Column(name = "id")
    private Integer id;

}

Table with initial test data

Table with initial test data

EntityManager factory

<bean id="entityManagerFactory"
      class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="jpaVendorAdapter" ref="hibernateJpaVendorAdapter" />
    <property name="packagesToScan" value="com.core.domain" />
    <property name="jpaProperties">
        <props>
            <prop key="hibernate.ejb.naming_strategy">org.hibernate.cfg.ImprovedNamingStrategy</prop>
            <prop key="hibernate.dialect">org.hibernate.dialect.PostgresPlusDialect</prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
            <prop key="hibernate.show_sql">false</prop>
            <prop key="hibernate.format_sql">false</prop>
            <prop key="hibernate.use_sql_comments">false</prop>
            <prop key="hibernate.temp.use_jdbc_metadata_defaults">false</prop>
        </props>
    </property>
</bean>

Issue Two: Gaps In the sequence value

For make resolve about error I have changed Entity class according to following manner (with GenerationType as SEQUENCE) and done the same steps (insert initial test data with sql script and try to insert data via application). Then data was inserted without any exception. Now my table contains records both inserted via script and application (as showing following picture). But newly add data (I have highlight on light blue color) via application had very higher PK value (started at 184 not from 6). That mean with "GenerationType as SEQUENCE" it seems hibernate not populate ID value in sequence manner (maintain some gaps). When I add further some data via application it seems it will stat to inserting data with another higher ID value (not start form 214). That mean is seems ID is not incrementing on sequence manner.

Entity Class - GenerationType as SEQUENCE

@Entity
@Table(name = "device_type")
public class DeviceType implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    @SequenceGenerator(name="my_seq", sequenceName="MY_SEQ", allocationSize=1, initialValue=1)
    @Basic(optional = false)
    @Column(name = "ID")
    private Integer id;
 }

Table data with gaps (for sequence ID) enter image description here

Possible duplicate of [Gaps between primary key id in sql table](http://stackoverflow.com/questions/39099905/gaps-between-primary-key-id-in-sql-table) — e4c5, Aug 24 '16 at 00:57

score 2 · Answer 1 · answered Apr 01 '14 at 13:34

2

This is how sequences (and so the PostgreSQL SERIAL type) behave.

If you are manually inserting values then you will need to update the sequence accordingly. Or, more usually, don't manually insert the values and let the sequence do it.

Gaps are inevitable unless you want to lock the table on each insert and kill off any hope of concurrency. I'd recommend not caring.

Remember - the numbers don't mean anything, they are just a convenient identifier.

Spend a few minutes reading up on how this all works: CREATE SEQUENCE, ALTER SEQUENCE

answered Apr 01 '14 at 13:34

Richard Huxton

21,516
3
39
51

Additionally, Hibernate by default allocates sequence values in chunks of 50, as an optimisation for insert performance. You can modify the `@SequenceGenerator` declaration to override that. – Craig Ringer Apr 01 '14 at 13:46
@CraigRinger Thanks for the comment. Yes I have done it with allocationSize=1 – Channa Apr 01 '14 at 13:57
@Richard Thanks for the feeb back. Sure I will refer those resources. I have noted even we do not enter any data manually it will start to insert data starting ID value with some haddock value (not from 1 it start from 10 like that) – Channa Apr 01 '14 at 14:03
1

@Channa: Postgres might also cache values if the sequence was created with the `CACHE` option. Those values are cached on a **per connection** basis. So if you have a `CACHE` on the sequence, connect to the DB, insert one row and then disconnect, those cached values are lost as well. – Apr 01 '14 at 15:59
2

@Channa If you have specific requirements for sequence values, you should create a custom `@SequenceGenerator` for each table, and use it in your `@GeneratedValue`. If you're rebuilding your DB during testing, you might want to reset the sequence start values with `SELECT setval(...)` if the start values are of concern to you. – Craig Ringer Apr 01 '14 at 21:03

Gaps In the sequence values generated by JPA @GeneratedValue with PostgreSQL

1 Answers1