Use postgres table sequence instead of sharing hibernate_sequence

Question

When I do anything with a table, it always show the error:

Hibernate: select nextval ('hibernate_sequence')
2019-07-20 16:15:44.877  WARN 58376 --- [nio-9000-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42P01
2019-07-20 16:15:44.877 ERROR 58376 --- [nio-9000-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: relation "hibernate_sequence" does not exist

I DON'T want to use hibernate_sequence to share id sequence between tables, but want to define id seq for each table and use them respectively.

I use Spring Boot 2.1.6.RELEASE, Spring Data JPA (Hibernate 5.3.10.Final), and Postgres 11.2, and define the id field with BigSerial type and hope to use the id sequence of each table in respective entity class.

The demo repo is here: https://github.com/Redogame/share_hibernate_sequence

Create user table (use identity as table name because user is a Postgres reserved keyword). By defining id with bigserial type, Postgres will create a identity_id_seq automatically, and I verified that identity_id_seq has been created successfully.

create table identity
(
    id                  bigserial    not null
        constraint identity_pkey
            primary key,
    name                varchar(255) not null
        constraint identity_name_key
            unique
        constraint identity_name_check
            check ((name)::text <> ''::text),
    created_date        timestamp    not null,
    created_by_id       bigint       not null
        constraint identity_identity_id_fk
            references identity,
    last_modified_date  timestamp    not null,
    last_modified_by_id bigint       not null
        constraint identity_identity_id_fk_2
            references identity,
    version             bigint       not null
);

Specify a sequence generator to use this id sequence:

@Table(name = "identity")
public class UserEntity extends Auditable<Long> {
    @Id
    @SequenceGenerator(name="identity_id_seq", sequenceName = "identity_id_seq", initialValue=1, allocationSize=1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="identity_id_seq")
    private Long id;

But it doesn't work. I also tried to config spring.jpa.hibernate.use-new-id-generator-mappings and spring.jpa.properties.hibernate.id.new_generator_mappings, but still not work.

spring:
  jpa:
    hibernate:
      use-new-id-generator-mappings: false
    properties:
      hibernate:
        id:
          new_generator_mappings: false

I expect not to use hibernate_sequence, that is: don't execute select nextval ('hibernate_sequence') before/after any SQL statement.

try creating a sequence manually and then using it in the mapping file. See it works. Then we can eliminate the issue. — dassum, Jul 20 '19 at 09:24
@dassum Would you like to teach me how? Please try to correct my source code in the above GitHub repo. — user2606091, Jul 20 '19 at 15:17
I have posted the answer. Please approve if it solves the problem — dassum, Jul 20 '19 at 17:00

dassum · Accepted Answer · 2019-07-20T16:57:58.667

Try the below steps

CREATE SEQUENCE IF NOT EXISTS manual_seq;
Change create table script

    create table identity
    (
        id  integer NOT NULL DEFAULT nextval('manual_seq'::regclass),
        name                varchar(255) not null
            constraint identity_name_key
                unique
            constraint identity_name_check
                check ((name)::text <> ''::text),
        created_date        timestamp    not null,
        created_by_id       bigint       not null,
        last_modified_date  timestamp    not null,
        last_modified_by_id bigint       not null,
        version             bigint       not null,
        CONSTRAINT manual_seq_pkey PRIMARY KEY (id)
    );

I removed the foreign key constraint for testing purposes.

Update the entity mapping

    @Entity
    @Table(name = "identity")
    @JsonIgnoreProperties(ignoreUnknown = true)
    public class UserEntity extends Auditable<Long> {
        @Id
        @SequenceGenerator(name="manual-seq", sequenceName = "manual_seq",allocationSize = 1)
        @GeneratedValue(generator="manual-seq")
        private Long id;

        @Basic
        @Column(name = "name", nullable = false)
        private String name;

@MappedSuperclass
@JsonIgnoreProperties({"new", "createdDate", "createdById", "lastModifiedDate", "lastModifiedById", "version"})
abstract class Auditable<PK extends Serializable>{

    @NotAudited
    @CreatedDate
    @Temporal(TemporalType.TIMESTAMP)
    private Date createdDate;

    @NotAudited
    @CreatedBy
    private Long createdById;

    @LastModifiedDate
    @Temporal(TemporalType.TIMESTAMP)
    private Date lastModifiedDate;

    @LastModifiedBy
    private Long lastModifiedById;

    @NotAudited
    @Version
    private Long version;

Revert the spring.jpa.hibernate.use-new-id-generator-mappings

The issue was extending AbstractPersistable because of which database sequence was not getting used. Also, note I have removed the audit for testing purposes.

Doesn't work. It still use hibernate_sequence at the end. ``` Hibernate: select userentity0_.id as id1_0_0_, ... Hibernate: select nextval ('identity_id_seq') Hibernate: insert into identity (created_by_id, created_date, last_modified_by_id, last_modified_date, version, name, id) values (?, ?, ?, ?, ?, ?, ?) Hibernate: select nextval ('hibernate_sequence') ``` Did you test with your modification? Can you paste your diff here or create a pull request? — user2606091, Jul 21 '19 at 14:52
@user2606091 I have tested with your code. change is in abstract class Auditable. Don't extend AbstractPersistable. — dassum, Jul 21 '19 at 16:58

score 0 · Answer 2 · answered Apr 26 '20 at 05:06

The same problem was happening to me. I explicitly set spring.jpa.properties.hibernate.id.new_generator_mappings=false but select nextval ('hibernate_sequence') was still run by Hibernate.

I found that when we use the @GeneratedValue annotation without setting a strategy, it defaults to AUTO, which means, Hibernate will try to generate the ID value using hibernate_sequence and then it wil fail because it doesn't exist in the database.

So, I made @GeneratedValue (strategy = GenerationType.IDENTITY) and tried again. In this case, the ID value was generated by my identity column in the database (the primary key that is automatically incremented) and not by hibernate_sequence.

create table users (id serial not null, name varchar(250), primary key (id));

Use postgres table sequence instead of sharing hibernate_sequence

2 Answers2

Linked