PanacheEntity do not set schema to sequence from @Table schema

[rodrigopim]

Describe the bug

Creating tables and sequences in another schema and using PanachEntity as an ID generation strategy, always seeks sequence in the default database schema.

Caused by: org.hibernate.exception.SQLGrammarException: error executing SQL statement [ERROR: relation "test_seq" does not exist (42P01)] [select nextval('TEST_SEQ')]

To be able to use the sequence in another schema, I have to use PanacheEntityBase and set manually.

@SequenceGenerator(name = "test_id_generator", schema = "MYSCHEMA", sequenceName = "test_seq")

It can take from @table annotation.

@Table(name = "MYTABLE", schema = "MYSHCEMA")

Expected behavior

Sequences have the same scheme defined in @table

Actual behavior

Sequence always looks for the standard scheme

How to Reproduce?

1. Implement a class model extending PanacheEntity, defining the scheme created with script for the table
2. Create your database using Flyway or Script executed on the database client in the specific scheme.
3. Try to execute Quarkus and create a record in this table.

Output of uname -a or ver

Linux 6.11.6-200.fc40.x86_64 #1 SMP PREEMPT_DYNAMIC Fri Nov 1 16:09:34 UTC 2024 x86_64 GNU/Linux

Output of java -version

openjdk version "21.0.5" 2024-10-15 OpenJDK Runtime Environment (Red_Hat-21.0.5.0.11-1) (build 21.0.5+11) OpenJDK 64-Bit Server VM (Red_Hat-21.0.5.0.11-1) (build 21.0.5+11, mixed mode, sharing)

Quarkus version or git rev

<quarkus.platform.version>3.15.1</quarkus.platform.version>⏎

Build tool (ie. output of mvnw --version or gradlew --version)

CLI - 3.16.3

Additional information

No response

[quarkus-bot]

/cc @FroMage (panache), @loicmathieu (panache)

[FroMage]

So, if I understand correctly, you'd like for id sequence generators to use the same schema as the table, unless specified explicitly?

If I get Panache out of the equation, that'd be:

@MappedSuperClass
public class Top {
 @Id
 @GeneratedValue
 public Long id;
}

@Table(schema = "other")
@Entity
public class Entity extends Top {
 // here, implicitely, id generator should be in "other" schema
}

[rodrigopim]

So, if I understand correctly, you'd like for id sequence generators to use the same schema as the table, unless specified explicitly?

If I get Panache out of the equation, that'd be:

@MappedSuperClass
public class Top {
@id
@GeneratedValue
public Long id;
}

@table(schema = "other")
@entity
public class Entity extends Top {
// here, implicitely, id generator should be in "other" schema
}

Thanks for the return. Yes, I think this should be the behavior when using implicit ID.

[FroMage]

@gavinking WDYT of this ask? Something that makes sense in Hibernate itself?

[geoand]

What should we do with this?

[FroMage]

Probably we should ask the question upstream first.

Let's try that: https://hibernate.atlassian.net/browse/HHH-19019

[rodrigopim]

I used SequenceGenerator, exchanging my PanacheEntity entities to PanacheEntityBase. This is interesting to take advantage of the use of PanacheEntity where Sequence is automatically generated and you decide to write down your entity in another Schema.

[FroMage]

OK, so that's one alternative. The spec mentions that generators default their schema to the default user schema. Perhaps you could set your default schema, that would set it for every entity and related DB object. Would that work for you?

[rodrigopim]

In projects that will use only one Schema yes.

I understand that if it is a change that can bring collateral damage, such as compatibility, better maintain simple projects to benefit from PanacheEntity, and where more specific configurations are required to use PanachEntityBase.

I am grateful to everyone for the attention and for me this issue it can be closed.

[FroMage]

OK. Well, thanks for raising this, it was a good remark, and it's a bit sad that the result is counter-intuitive, but it is consistent with what the spec and documentation say, and there's a decent workaround, so… 🤷