Hibernate wrong column type 'geometry': Hibernate-Spatial + Multitenancy

[dominique-jansen]

I'm trying to run a Quarkus application with Postgis, Hibernate and Multitenancy. Without Multitenancy it's working fine, but as soon as I activate Multitenancy, Hibernate complains about the geometry column-type and I can't figure out why. This is a simplified version of my application:

application.properties (setup postgis database and let hibernate generate tables)

quarkus.datasource.db-kind=postgresql
quarkus.datasource.devservices.image-name=postgis/postgis:14-3.3

quarkus.hibernate-orm.database.generation=drop-and-create #after creation, set to 'validate'
quarkus.hibernate-orm.database.default-schema=spatial-test
quarkus.hibernate-orm.dialect=org.hibernate.dialect.PostgreSQLDialect
# quarkus.hibernate-orm.multitenant=SCHEMA <- Setting this property results in hibernate schema validation exception

MyEntity.java (Simple entity with a Geolatte geometry. (No difference with JTS geometry))

import org.geolatte.geom.G2D;
import org.geolatte.geom.Geometry;

import io.quarkus.hibernate.orm.panache.PanacheEntity;
import jakarta.persistence.Entity;

@Entity
public class MyEntity extends PanacheEntity {

    public Geometry<G2D> geometry;
}

When I start the application, everything is working fine. But as soon, as I use quarkus.hibernate-orm.multitenant=SCHEMA in my application.properties and provide a TenantResolver, hibernate complains about the geometry-datatype.: Schema-validation: wrong column type encountered in column [geometry] in table [MyEntity]; found ["public"."geometry" (Types#OTHER)], but expecting [geometry (Types#GEOMETRY)]

(The postgis extension comes with the postgis devservice image and installs the extension in my public-schema.)

Overview of schemas (left-side) and postgis extension (which is automatically installed for the public schema)

MyTenantResolver.java (Decides dynamically, what schema to use. Dummy version)

import io.quarkus.hibernate.orm.PersistenceUnitExtension;
import io.quarkus.hibernate.orm.runtime.tenant.TenantResolver;
import jakarta.enterprise.context.RequestScoped;

@RequestScoped
@PersistenceUnitExtension
public class DummyTenantResolver implements TenantResolver {

    @Override
    public String getDefaultTenantId() {
        return "spatial-test2";
    }

    @Override
    public String resolveTenantId() {
        return "spatial-test";
    }
}

Exception, thrown when multitenancy is activated:

ERROR [io.qua.run.Application] (Quarkus Main Thread) Failed to start application (with profile [dev]): java.lang.RuntimeException: Failed to start quarkus
        at io.quarkus.runner.ApplicationImpl.doStart(Unknown Source)
        at io.quarkus.runtime.Application.start(Application.java:101)
        at io.quarkus.runtime.ApplicationLifecycleManager.run(ApplicationLifecycleManager.java:111)
        at io.quarkus.runtime.Quarkus.run(Quarkus.java:71)
        at io.quarkus.runtime.Quarkus.run(Quarkus.java:44)
        at io.quarkus.runtime.Quarkus.run(Quarkus.java:124)
        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:113)
        at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: jakarta.persistence.PersistenceException: [PersistenceUnit: <default>] Unable to build Hibernate SessionFactory
        at io.quarkus.hibernate.orm.runtime.boot.FastBootEntityManagerFactoryBuilder.persistenceException(FastBootEntityManagerFactoryBuilder.java:129)
        at io.quarkus.hibernate.orm.runtime.boot.FastBootEntityManagerFactoryBuilder.build(FastBootEntityManagerFactoryBuilder.java:89)
        at io.quarkus.hibernate.orm.runtime.FastBootHibernatePersistenceProvider.createEntityManagerFactory(FastBootHibernatePersistenceProvider.java:72)
        at jakarta.persistence.Persistence.createEntityManagerFactory(Persistence.java:80)
        at jakarta.persistence.Persistence.createEntityManagerFactory(Persistence.java:55)
        at io.quarkus.hibernate.orm.runtime.JPAConfig$LazyPersistenceUnit.get(JPAConfig.java:154)
        at io.quarkus.hibernate.orm.runtime.JPAConfig$1.run(JPAConfig.java:61)
        ... 1 more
Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: wrong column type encountered in column [geometry] in table [MyEntity]; found ["public"."geometry" (Types#OTHER)], but expecting [geometry (Types#GEOMETRY)]
        at org.hibernate.tool.schema.internal.AbstractSchemaValidator.validateColumnType(AbstractSchemaValidator.java:165)
        at org.hibernate.tool.schema.internal.AbstractSchemaValidator.validateTable(AbstractSchemaValidator.java:152)
        at org.hibernate.tool.schema.internal.GroupedSchemaValidatorImpl.validateTables(GroupedSchemaValidatorImpl.java:46)
        at org.hibernate.tool.schema.internal.AbstractSchemaValidator.performValidation(AbstractSchemaValidator.java:97)
        at org.hibernate.tool.schema.internal.AbstractSchemaValidator.doValidation(AbstractSchemaValidator.java:75)
        at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:295)
        at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.lambda$process$5(SchemaManagementToolCoordinator.java:145)
        at java.base/java.util.HashMap.forEach(HashMap.java:1421)
        at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:142)
        at io.quarkus.hibernate.orm.runtime.observers.SessionFactoryObserverForSchemaExport.sessionFactoryCreated(SessionFactoryObserverForSchemaExport.java:21)
        at org.hibernate.internal.SessionFactoryObserverChain.sessionFactoryCreated(SessionFactoryObserverChain.java:35)
        at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:315)
        at io.quarkus.hibernate.orm.runtime.boot.FastBootEntityManagerFactoryBuilder.build(FastBootEntityManagerFactoryBuilder.java:87)
        ... 6 more

I'm using:

• Quarkus Version 3.9.3
• Hibernate-Spatial Version 6.4.4.Final
• Java 17

Does anyone know what I'm doing wrong?

[quarkus-bot[bot]]

/cc @gsmet (hibernate-orm), @yrodiere (hibernate-orm)

[yrodiere]

So, first, you should remove this:

quarkus.hibernate-orm.dialect=org.hibernate.dialect.PostgreSQLDialect

It's not useful in your case, and can be harmful. See https://quarkus.io/guides/hibernate-orm#hibernate-dialect-supported-databases

Now regarding your problem... I see your default schema is not public, but spacial-test:

quarkus.hibernate-orm.database.default-schema=spatial-test

Which I'd expect not to work, since you say the postgis extension is installed only in the public schema.

But... the error mentions a public schema... So, does your error really match your configuration, or is it some old stacktrace from an earlier attempt with different configuration?

Regardless, there maybe one, or even two bugs here. Can you please provide a reproducer?

[dominique-jansen]

Thanks for your reply,

I removed quarkus.hibernate-orm.dialect=org.hibernate.dialect.PostgreSQLDialect. I guess it really wasn't necessary, thanks!

My plan is to use multitenancy. When leaving out the default-schema-property, hibernate will create tables inside the public schema and will also validate this one. But I only want my data to be stored in my tenant-schemas, e.g. spatial-test, spatial-test2, ... and not the public schema. By setting the default-schema and leaving out the multitenancy-property (quarkus.hibernate-orm.multitenant=SCHEMA) everything is working fine. The postgis extension is installed for the public schema and im using my default-schema spatial-test. All tables are generated and the validation succeeds. When activating multitenancy though, the exception is thrown (it's not an old stacktrace).

I need every tenant to use the postgis extension and I didn't find a way to install the postgis extension to multiple schemas. The postgis image adds it to the public schema automatically, so I thought this was the way to go?

By reproducer you mean a small sample-project?

[yrodiere]

I need every tenant to use the postgis extension and I didn't find a way to install the postgis extension to multiple schemas. The postgis image adds it to the public schema automatically, so I thought this was the way to go?

I'm not familiar with postgis but I suspect installing only on the public schema is not enough. Maybe try using flyway with an SQL script that will automatically install the extension on all relevant schemas?

By reproducer you mean a small sample-project?

Yes please.

[dominique-jansen]

In my real project, I'm actually using flyway. When calling "create extension postgis", it only works for the first schema. For the second schema, the postgis extension can't be installed because it already exists. It seems like the name "postgis" is already taken and can't be used multiple times.

Here is my sample-project: https://github.com/dominique-jansen/spatial-test

[gsmet]

This is actually expected, I think. You need to make sure the extension can be found and for that, you either need to install it in the schema or you need to make the schema visible by adjusting the search_path.

See this SO answer for more information: https://stackoverflow.com/questions/12986368/installing-postgresql-extension-to-all-schemas

[dominique-jansen]

But why does it work without the multitenancy-property?

I'm using the exact same setup: public schema containing the postgis extension and my default-schema 'spatial-test' for which hibernate creates tables and executes the validation without any problems. What is the differenence after activating multitenancy?

--

I can adjust the search_path, as you mentioned, by calling:

set schema 'spatial-test';
set search_path = 'spatial-test', public; // default is only 'spatial-test'

But this doesn't persist. It only changes the search_path for the moment and it seems like when the application starts, it still uses the old search_path. My postgres knowledge only covers the basics. Is there a way to save this setting?

[gsmet]

From memory, you can alter the search path at the database or user level with ALTER DATABASE/USER <whatever> SET SEARCH_PATH TO <search path>;.

[gsmet]

Now, I'm not exactly sure how multitenancy with schemas is managed at the Hibernate ORM level and if it alters the search path or qualify each table.

[yrodiere]

Last time I checked it qualified each table.

So, looks like @dominique-jansen needs to use this:

From memory, you can alter the search path at the database or user level with ALTER DATABASE/USER <whatever> SET SEARCH_PATH TO <search path>;.