Panache: How to registerFunctions to support PostgresSQL operators

[mqs24d]

Hey panache community,

is there a way to customise the used DB Dialect to enable support for special operators in hibernate panache queries. For example: register a SQLFunctionTemplate at PostgreSQL10Dialect for the @> range operator.

The quarkiverse hibernate-types plugin already supports the mapping of special types like ranges. So it would be nice to query those fields with the full postgresql power.

If I'm not mistaken the following is not possible out of the box:

CREATE TABLE booking (
  ...
  range daterange,
  ...
);

@TypeDef(
    typeClass = PostgreSQLRangeType.class,
    defaultForType = Range.class
)
@NamedQueries({
    @NamedQuery(name = "Booking.findBookingsForDate", query = "from Booking where range @> ?1")
})
public class Booking extends PanacheEntity {
   @Column(columnDefinition = "daterange")
   public Range<LocalDate> range;
}

[quarkus-bot[bot]]

/cc @FroMage, @loicmathieu

[mqs24d]

Adding a custom Dialect Implementation

public class CustomPostgresSQL10Dialect extends QuarkusPostgreSQL10Dialect {

  public CustomPostgresSQL10Dialect() {
    super();
    registerFunction("@>", new SQLFunctionTemplate(StringType.INSTANCE, "(?1 @> ?2)"));
  }
}

and registering it in Property quarkus.hibernate-orm.dialect=com.acme.hibernate.CustomPostgresSQL10Dialect results in a startup error (at least in dev mode... i have not tested if it works in prod)

2022-11-15 15:59:24,856 WARN  [io.qua.arc.impl] (Quarkus Main Thread) An error occurred during delivery of the @BeforeDestroyed(ApplicationScoped.class) event: javax.enterprise.inject.CreationException: Config root [io.quarkus.hibernate.orm.runtime.HibernateOrmRuntimeConfig] with config phase [RUN_TIME] not initialized yet.
	at io.quarkus.hibernate.orm.runtime.HibernateOrmRuntimeConfig_66229d6c2e25c4818b3ef00069aed3a5861e7464_Synthetic_Bean.create(Unknown Source)
	at io.quarkus.hibernate.orm.runtime.HibernateOrmRuntimeConfig_66229d6c2e25c4818b3ef00069aed3a5861e7464_Synthetic_Bean.get(Unknown Source)
	at io.quarkus.hibernate.orm.runtime.HibernateOrmRuntimeConfig_66229d6c2e25c4818b3ef00069aed3a5861e7464_Synthetic_Bean.get(Unknown Source)
	at io.quarkus.arc.impl.CurrentInjectionPointProvider.get(CurrentInjectionPointProvider.java:62)
	at io.quarkus.hibernate.orm.runtime.JPAConfig_Bean.create(Unknown Source)
	at io.quarkus.hibernate.orm.runtime.JPAConfig_Bean.create(Unknown Source)
	at io.quarkus.arc.impl.AbstractSharedContext.createInstanceHandle(AbstractSharedContext.java:113)
	at io.quarkus.arc.impl.AbstractSharedContext$1.get(AbstractSharedContext.java:37)
	at io.quarkus.arc.impl.AbstractSharedContext$1.get(AbstractSharedContext.java:34)
	at io.quarkus.arc.impl.LazyValue.get(LazyValue.java:26)
	at io.quarkus.arc.impl.ComputingCache.computeIfAbsent(ComputingCache.java:69)
	at io.quarkus.arc.impl.AbstractSharedContext.get(AbstractSharedContext.java:34)
	at io.quarkus.hibernate.orm.runtime.JPAConfig_Observer_destroy_b8200764d6ca5f0908a962ee9c3337f12042c096.notify(Unknown Source)
	at io.quarkus.arc.impl.EventImpl$Notifier.notifyObservers(EventImpl.java:326)
	at io.quarkus.arc.impl.EventImpl$Notifier.notify(EventImpl.java:314)
	at io.quarkus.arc.impl.EventImpl$Notifier.notify(EventImpl.java:244)
	at io.quarkus.arc.impl.ArcContainerImpl.shutdown(ArcContainerImpl.java:388)
	at io.quarkus.arc.Arc.shutdown(Arc.java:58)
	at io.quarkus.arc.runtime.ArcRecorder$1.run(ArcRecorder.java:47)
	at io.quarkus.runtime.StartupContext.runAllInReverseOrder(StartupContext.java:84)
	at io.quarkus.runtime.StartupContext.close(StartupContext.java:73)
	at io.quarkus.runner.ApplicationImpl.<clinit>(Unknown Source)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
	at java.base/java.lang.reflect.ReflectAccess.newInstance(ReflectAccess.java:128)
	at java.base/jdk.internal.reflect.ReflectionFactory.newInstance(ReflectionFactory.java:347)
	at java.base/java.lang.Class.newInstance(Class.java:645)
	at io.quarkus.runtime.Quarkus.run(Quarkus.java:69)
	at io.quarkus.runtime.Quarkus.run(Quarkus.java:43)
	at io.quarkus.runtime.Quarkus.run(Quarkus.java:123)
	at io.quarkus.runner.GeneratedMain.main(Unknown Source)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at io.quarkus.runner.bootstrap.StartupActionImpl$1.run(StartupActionImpl.java:104)
	at java.base/java.lang.Thread.run(Thread.java:833)

2022-11-15 15:59:24,857 ERROR [io.qua.run.boo.StartupActionImpl] (Quarkus Main Thread) Error running Quarkus: java.lang.reflect.InvocationTargetException
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at io.quarkus.runner.bootstrap.StartupActionImpl$1.run(StartupActionImpl.java:104)
	at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.lang.ExceptionInInitializerError
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
	at java.base/java.lang.reflect.ReflectAccess.newInstance(ReflectAccess.java:128)
	at java.base/jdk.internal.reflect.ReflectionFactory.newInstance(ReflectionFactory.java:347)
	at java.base/java.lang.Class.newInstance(Class.java:645)
	at io.quarkus.runtime.Quarkus.run(Quarkus.java:69)
	at io.quarkus.runtime.Quarkus.run(Quarkus.java:43)
	at io.quarkus.runtime.Quarkus.run(Quarkus.java:123)
	at io.quarkus.runner.GeneratedMain.main(Unknown Source)
	... 6 more
Caused by: java.lang.RuntimeException: Failed to start quarkus
	at io.quarkus.runner.ApplicationImpl.<clinit>(Unknown Source)
	... 17 more
Caused by: org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:275)
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:237)
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214)
	at io.quarkus.hibernate.orm.runtime.service.QuarkusSimplifiedIdentifierGeneratorFactory.injectServices(QuarkusSimplifiedIdentifierGeneratorFactory.java:122)
	at io.quarkus.hibernate.orm.runtime.service.QuarkusMutableIdentifierGeneratorFactory.injectServices(QuarkusMutableIdentifierGeneratorFactory.java:79)
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.injectDependencies(AbstractServiceRegistryImpl.java:286)
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:243)
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:214)
	at org.hibernate.boot.internal.InFlightMetadataCollectorImpl.<init>(InFlightMetadataCollectorImpl.java:173)
	at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:127)
	at io.quarkus.hibernate.orm.runtime.boot.FastBootMetadataBuilder.build(FastBootMetadataBuilder.java:382)
	at io.quarkus.hibernate.orm.runtime.PersistenceUnitsHolder.createMetadata(PersistenceUnitsHolder.java:101)
	at io.quarkus.hibernate.orm.runtime.PersistenceUnitsHolder.constructMetadataAdvance(PersistenceUnitsHolder.java:73)
	at io.quarkus.hibernate.orm.runtime.PersistenceUnitsHolder.initializeJpa(PersistenceUnitsHolder.java:40)
	at io.quarkus.hibernate.orm.runtime.HibernateOrmRecorder$1.created(HibernateOrmRecorder.java:76)
	at io.quarkus.arc.runtime.ArcRecorder.initBeanContainer(ArcRecorder.java:73)
	at io.quarkus.deployment.steps.ArcProcessor$generateResources844392269.deploy_0(Unknown Source)
	at io.quarkus.deployment.steps.ArcProcessor$generateResources844392269.deploy(Unknown Source)
	... 18 more
Caused by: org.hibernate.boot.registry.selector.spi.StrategySelectionException: Unable to resolve name [com.acme.hibernate.CustomPostgresSQL10Dialect] as strategy [org.hibernate.dialect.Dialect]
	at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.selectStrategyImplementor(StrategySelectorImpl.java:156)
	at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.resolveStrategy(StrategySelectorImpl.java:239)
	at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.resolveDefaultableStrategy(StrategySelectorImpl.java:183)
	at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.resolveDefaultableStrategy(StrategySelectorImpl.java:170)
	at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.resolveStrategy(StrategySelectorImpl.java:164)
	at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.constructDialect(DialectFactoryImpl.java:74)
	at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.buildDialect(DialectFactoryImpl.java:51)
	at io.quarkus.hibernate.orm.runtime.recording.RecordingDialectFactory.buildDialect(RecordingDialectFactory.java:20)
	at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:138)
	at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35)
	at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:101)
	at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:263)
	... 35 more
Caused by: org.hibernate.boot.registry.classloading.spi.ClassLoadingException: Unable to load class [com.acme.hibernate.CustomPostgresSQL10Dialect]
	at io.quarkus.hibernate.orm.runtime.service.FlatClassLoaderService.classForName(FlatClassLoaderService.java:38)
	at org.hibernate.boot.registry.selector.internal.StrategySelectorImpl.selectStrategyImplementor(StrategySelectorImpl.java:152)
	... 46 more
Caused by: java.lang.ClassNotFoundException: com.acme.hibernate.CustomPostgresSQL10Dialect
	at java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:641)
	at java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:188)
	at java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:520)
	at io.quarkus.bootstrap.classloading.QuarkusClassLoader.loadClass(QuarkusClassLoader.java:505)
	at io.quarkus.bootstrap.classloading.QuarkusClassLoader.loadClass(QuarkusClassLoader.java:455)
	at io.quarkus.bootstrap.classloading.QuarkusClassLoader.loadClass(QuarkusClassLoader.java:505)
	at io.quarkus.bootstrap.classloading.QuarkusClassLoader.loadClass(QuarkusClassLoader.java:455)
	at java.base/java.lang.Class.forName0(Native Method)
	at java.base/java.lang.Class.forName(Class.java:467)
	at io.quarkus.hibernate.orm.runtime.service.FlatClassLoaderService.classForName(FlatClassLoaderService.java:36)
	... 47 more

[gsmet]

Are you sure you're pointing to the right class as Quarkus can't find the class?

If so, please open an issue with a reproducer.

[mqs24d]

Yeah i'm 100% sure that i pointing to the right class but for some reason in my project it does not work. In my reproducer example which i generated from code.quarkus.io it works. So i need to pinpoint the reason why it's not working in my real project

[mqs24d]

oh sorry for all the fuzz i'm dump... had a small typo

[mqs24d]

Here is my small example project where i use quarkiverse hibernate-types to map a jsonb column and a custom Hibernate Dialect implementation to use postgres jsonb ->> operator in a query.