Schema-validation: missing sequence [users_seq], although sequence does exist

Hibernate ORM
1

Hello.
I start from afar. My project’s stack is
Spring Boot 3.1.5
Liquibase 4.20.0
Hibernate 6.2.13
PostgreSQL (ver. 12.13)
JDBC Driver (ver. 42.6.0, JDBC4.2)

I am trying to create table “users” with integer id by using liquibase script.
application.properties:

spring.jpa.database=postgresql
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=validate
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.datasource.url=~~~
spring.datasource.username=~~~
spring.datasource.password=~~~

Liquibase:

~~~
      changes:
        - createTable:
            tableName: users
            columns:
              - column:
                  name: id
                  type: SERIAL
                  autoIncrement: true
                  constraints:
                    nullable: false
                    primaryKey: true
~~~

User.class:

~~~
@Entity
@Table(name = "users")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class User implements UserDetails {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
~~~

Liquibase script creates table without exceptions and at the same time with it a sequence named “users_id_seq” (integer type) is being created.
Content of this sequence:

create sequence users_id_seq;

alter sequence users_id_seq owner to postgres;

alter sequence users_id_seq owned by users.id;

But when I try to run my application, I got an exception

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing sequence [users_seq]
	at org.hibernate.tool.schema.internal.AbstractSchemaValidator.validateSequence(AbstractSchemaValidator.java:182) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.tool.schema.internal.AbstractSchemaValidator.performValidation(AbstractSchemaValidator.java:113) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.tool.schema.internal.AbstractSchemaValidator.doValidation(AbstractSchemaValidator.java:75) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:293) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.lambda$process$5(SchemaManagementToolCoordinator.java:143) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at java.base/java.util.HashMap.forEach(HashMap.java:1420) ~[na:na]
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:140) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.boot.internal.SessionFactoryObserverForSchemaExport.sessionFactoryCreated(SessionFactoryObserverForSchemaExport.java:37) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.internal.SessionFactoryObserverChain.sessionFactoryCreated(SessionFactoryObserverChain.java:35) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:293) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:444) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1458) ~[hibernate-core-6.2.13.Final.jar:6.2.13.Final]
	at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:75) ~[spring-orm-6.0.13.jar:6.0.13]
	at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:376) ~[spring-orm-6.0.13.jar:6.0.13]
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:409) ~[spring-orm-6.0.13.jar:6.0.13]
	... 20 common frames omitted

I’ve tried to edit User.class like this:

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "userseq")
    @SequenceGenerator(name = "userseq", schema = "public", sequenceName = "users_id_seq")
    private Integer id;

but got exception Schema-validation: missing sequence [public.users_id_seq].
The only solution I’ve come to is to create a new sequence together with the table I need like this:

        - createSequence:
            sequenceName: users_id_seq
            schemaName: information_schema
            incrementBy: 1
            startValue: 1
            dataType: integer

and edit annotations in User.class like this:

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "userseq")
    @SequenceGenerator(name = "userseq", sequenceName = "users_id_seq", allocationSize = 1)
    private Integer id;

But this solution seems ugly for me: now I’ve got two sequences in two different schemas for one same purpose.
I’ve checked this behaviour on Hibernate version 6.2.5 (it repeated) and 5.6.15 (it did not, everything worked), so it seems like some new changes in workflow.

  1. Am I doing something wrong? How to get sequence validated without exceptions?
  2. org.hibernate.dialect.PostgreSQLDialect#getQuerySequencesString has return "select * from information_schema.sequences" hardcoded. Is this the source of the problem?
  3. Why even direct declaration of schema and sequence names does not work?
    And additional question, which does not directly relate to this particular problem:
  4. If set option spring.jpa.hibernate.ddl-auto=create, thien Hibernate create the sequence by its own, but this sequence will have type bigint (for integer type id) and increment size of 50. Why bigint and why so big increment?

Thanks up front.

2

With your latest changes you instruct Hibernate ORM to create/validate a sequence in the public schema. It seems like the default schema of the connection through which you created your schema with liquibase was pointing to a different schema though.

I would suggest you use a specific schema with Hibernate and Liquibase to avoid such issues. Hibernate has a configuration property hibernate.default_schema which you should be able to set in your applicatoin properties via spring.pa.prpoerties.hibernate.default_schema=myschema which will then put/expect all objects in that particular schema. Not sure about liquibase, but I suppose you can specify schemaName: myschema in the YAML file on the createTable entry.

3

Thanks, but spring.jpa.properties.hibernate.default_schema=public didn’t help. missing sequence [users_id_seq] exception is still there, and users_id_seq is still on its place in schema public.

4

It should work though, so I guess your environment is the reason for the problem. Are you connecting to the right database?
Try setting a break point where the exception is thrown and step back stack frames to understand why Hibernate ORM ends up trying to throw this exception in the first place by looking at local variables etc.

5

Tried to step back and look at how Hibernate works.
sessionFactoryOptions and sqlStringGenerationContext has default schema as it stated in appliction.properties, so it is correct.
In validation process (org.hibernate.tool.schema.internal.AbstractSchemaValidator#doValidation) SqlStringGenerationContext has default schema as public so it is also correct.
Implicit schema and catalog names are not resolved and is null in namespace, if it matters.
org.hibernate.tool.schema.extract.internal.DatabaseInformationImpl#locateSequenceInformation is aware about schema and catalog fields and its content, but nullifies its in next call, so Hibernate tries to validate sequence without considering schema name in any case.
But even it was otherwise, the next line in this method calls sequenceInformationMap, which is empty in my project, because this map is filled by method org.hibernate.tool.schema.extract.internal.SequenceInformationExtractorLegacyImpl#extractMetadata.
And this method executes sql request select * from information_schema.sequences, hardcoded in org.hibernate.dialect.PostgreSQLDialect, ignoring all default schema settings.
So, what should I do with PostgreSQLDialect? It stated in my properties as spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect, and I always thought it should be this way. Did I miss some obvious settings? Like annotations on Main class? Or dialect class setting should be replaced?

6

Please try to create a reproducer with our test case template (https://github.com/hibernate/hibernate-test-case-templates/blob/master/orm/hibernate-orm-6/src/test/java/org/hibernate/bugs/JPAUnitTestCase.java) and if you are able to reproduce the issue, create a bug ticket in our issue tracker(https://hibernate.atlassian.net) and attach that reproducer.

And this method executes sql request select * from information_schema.sequences , hardcoded in org.hibernate.dialect.PostgreSQLDialect , ignoring all default schema settings.

information_schema is a special schema in SQL that contains information about all objects in the current catalog/database. So that is correct.

7

Checked the behaviour of this view. It appears information_schema.sequences shows only those sequences, which not dependent to their tables by identity type.
So, if Liquibase sees autoIncrement: true in script, it generates integer generated by default as identity in table (my case), and Postgres creates a sequence for that dependency right after that. Because of Identity type, this sequence is omitted from information_schema.sequences view, and when Hibernate tries to validate DB schema, it finds nothing in the place of the expectable sequence, and throws exception.
That’s how the things are. The rest is to find out what I can do with all of this.
Sorry if my takes looks awkward, I’m not a DB expert.

I’ll try to do that next week.

8

So, if Liquibase sees autoIncrement: true in script, it generates integer generated by default as identity in table (my case), and Postgres creates a sequence for that dependency right after that. Because of Identity type, this sequence is omitted from information_schema.sequences view, and when Hibernate tries to validate DB schema, it finds nothing in the place of the expectable sequence, and throws exception.

Well, that’s a problem of your liquibase script then. You should not use autoIncrement but rather use the createSequence block.

9

Hi, i’ve pinpointed the issue.
It’s not related to Liquibase. It’s just
org.hibernate.dialect.PostgreSQLDialect

@Override
	public String getQuerySequencesString() {
		return "select * from information_schema.sequences";
	}

which doesn’t return sequences, created by Postgre IDENTITY declaration embedded sequences

create table if not exists public.my_table
(
    id       bigint generated by default as identity primary key,
)

it implicitly creates sequence my_table_id_seq which is filtered out by query

more info on that:

10

which doesn’t return sequences, created by Postgre IDENTITY declaration embedded sequences

This is on purpose.

The problem is the DDL that is created. The DDL does not correspond to what Hibernate ORM expects. Hibernate ORM requires that there is an explicit sequence with that particular name.