CODEWITHSUNDEEP
javasqlhibernatepostgresqljpa
Channa
Channa

Reputation: 5233

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

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

Upvotes: 0

Views: 3509

Answers (1)

Richard Huxton
Richard Huxton

Reputation: 22893

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

Upvotes: 2

Related Questions