"hibernate_sequence" does not exist"

Question

I am getting "ERROR: relation "hibernate_sequence" does not exist" exception while doing insert operation.

Technical Stack

-> Springboot
-> Hibernate
-> PostgreSQL


Approaches tried so far.

-> Verified all entity classes in project, generation strategy is used as "@GeneratedValue(strategy = GenerationType.IDENTITY)".

-> Verified database tables, pk is either Serial or Int with proper sequence generated value.

-> Tried with use-new-id-generator-mappings property as false, didn't worked.

-> Verified sequence with name "hibernate_sequence" is available in Database.

Analysis so far

-> Entities those are annotated with @Audited having this issue as hibernate envers expect global "hibernate_sequence". But not able to find the exact solution.

Note : This was working few days back without any issue, Since last week started getting this issue.

Answer 1

As you said, hibernate-envers is looking for the hibernate_sequence.

Its used to insert records into the REVINFO table

Assuming spring.jpa.hibernate.ddl-auto is not set to create

either

1. create a hibernate_sequence manually
2. create a sequence with the name you want. e.g rev_id_seq. Then override the REVINFO definition to change the sequence name by adding your definition of the RevisionEntity

@Entity
@RevisionEntity
public class MyRevision implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "rev_id_generator")
    @SequenceGenerator(name = "rev_id_generator", sequenceName = "rev_id_seq", allocationSize = 1)
    @RevisionNumber
    private int id;

    @RevisionTimestamp
    private long timestamp;

    // Getters, setters, equals, hashCode ...
}

https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html#envers-tracking-modified-entities-revchanges

https://thorben-janssen.com/hibernate-envers-extend-standard-revision/

Answer 2

Initially set the spring.jpa.hibernate.ddl-auto to create for the first time and run the application. It will create the hibernate sequence. after that change spring.jpa.hibernate.ddl-auto to none. It will prevent any further data loss from tables. Or you can set to update if necessary.

Answer 3

Because you are using @GeneratedValue()

It will look for how the database that you are using generates ids. For MySql or HSQSL, there are increment fields that automatically increment. In Postgres or Oracle, they use sequence tables. Since you didn't specify a sequence table name, it will look for a sequence table named hibernate_sequence and use it for default. So you probably don't have such a sequence table in your database and now you get that error.

I Also got this working using;

@GeneratedValue(strategy = GenerationType.IDENTITY)

Answer 4

I faced the exact same issue when I migrated from Maria to Postgres. Either/both problems one may have:

Schema name is not in the connection URL

If the schema name isn't passed flyway_schema_history table and sequences were created under the public schema. And application tables were made under the custom schema.
So make sure you have the required schema configured.

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/platform?currentSchema=product1
    username: admin
    password: admin
    driver-class-name: org.postgresql.Driver
  flyway:
    schemas:
      - product1

Sequence got created but with another name

This was the problem for me. Sequence got created with the name revinfo_rev_seq. However, while inserting the records it was looking for hibernate_sequence. I added another revision under flyway migration to rename the already created sequence.

-- This is not required for MySQL/MariaDB. However, while using PostgresSQL getting the error -> ERROR: relation "hibernate_sequence" does not exist
ALTER SEQUENCE revinfo_rev_seq RENAME TO hibernate_sequence;