quarkus: Manually created sequence in postgresql is not working correctly

Describe the bug Given an Entity with a Long type @Id column, when manually creating the sequence for the id column CREATE SEQUENCE giftSeq the behaviour of assigning is broken.

Expected behavior id’s assignment follow the natural series development 1,2,3,…

Actual behavior The id’s assigned to follow the path 1,2,-46,-45,… until a collision happens

To Reproduce

docker run --ulimit memlock=-1:-1 -d --rm=true --memory-swappiness=0
--name postgres-quarkus-hibernate -e POSTGRES_USER=hibernate
-e POSTGRES_PASSWORD=hibernate
-p 5432:5432 postgres:10.5

docker exec -ti postgres-quarkus-hibernate psql -U hibernate -c "CREATE DATABASE hibernate_db"

docker exec -ti postgres-quarkus-hibernate psql -U hibernate -c "CREATE TABLE Gift(id bigint primary key, name text); CREATE sequence giftSeq;" hibernate_db

Reproducer example here, with test case https://github.com/svinther/quarkus-problem-messysequence

Configuration

%test.quarkus.hibernate-orm.log.sql=true
%dev.quarkus.hibernate-orm.log.sql=true
%dev.quarkus.hibernate-orm.log.bind-param=true

quarkus.hibernate-orm.database.generation=none

# datasource configuration
quarkus.datasource.db-kind=postgresql
quarkus.datasource.username=hibernate
quarkus.datasource.password=hibernate
quarkus.datasource.jdbc.url=jdbc:postgresql://localhost:5432/hibernate_db

Environment (please complete the following information):

Openjdk 11
Quarkus 1.7.0.Final

Additional context If creating the sequence as “CREATE sequence giftSeq start 1 increment 50;” as seems to be hibernates default behavior, then everything seems to work correctly

About this issue

Original URL
State: open
Created 4 years ago
Comments: 25 (14 by maintainers)

Most upvoted comments

It would be tempting to switch the Quarkus default to use the non-global definitions.

I think we did in ORM 6.0, at least to some extent?

https://github.com/hibernate/hibernate-orm/blob/6.0/migration-guide.adoc#implicit-identifier-sequence-and-table-name

Implicit Identifier Sequence and Table Name

The way in which Hibernate determines implicit names for sequences and tables associated with identifier generation has changed in 6.0 which may affect migrating applications.

To help with backwards compatibility, or to apply any general naming strategy, 6.0 introduces the org.hibernate.id.enhanced.ImplicitDatabaseObjectNamingStrategy contract which can be specified using the hibernate.id.db_structure_naming_strategy setting. See discussion at link:https://docs.jboss.org/hibernate/orm/6.0/javadocs/org/hibernate/cfg/AvailableSettings.html#ID_DB_STRUCTURE_NAMING_STRATEGY

For backwards compatibility, use either hibernate.id.db_structure_naming_strategy=single or hibernate.id.db_structure_naming_strategy=legacy depending on needs

By the way, heads-up to @Sanne: we may need to expose this configuration property in Quarkus 3 to provide a migration path for users who created their schema with Hibernate ORM 5? Maybe that should be part of a more general effort to provide a migration guide to users of the Hibernate ORM extension for Quarkus, because I suspect there are other breaking changes.

We got sidetracked, though. I must admit I’m not sure I understand the exact problem we need to solve in order to close this issue.

I think the conclusion was there:

(Sanne)

But in conclusion this is only a problem in combination with self-defined schemas… and this is an area in which people need to take care for “manually”: I’d say it’s always going to be error prone, no matter what we do. Not sure about this being a bug.

Ideally, wondering if we can test the sequence state at initialization.

(Gavin)

Yup. I think we should go with your suggestion above and emit a warning, at least.

And if we had some sort of “strict” mode I would upgrade it to an error in H6.

Do I understand correctly that you would like to log a warning on startup (upon validating the schema?) when there is a @GeneratedValue(name = "gitSeq") without a corresponding @SequenceGenerator, and we cannot find the corresponding sequence in the schema?

yrodiere on Jan 30, 2023

I’m no JPA expert but shouldn’t be @GeneratedValue#generator() used together with @SequenceGenerator or @TableGenerator?

I think you are right. I followed this quickstart guide: https://quarkus.io/guides/hibernate-orm without thinking too much, I guess the guide should be corrected ?

svinther on Aug 25, 2020