I’m facing an issue with PostGIS when running integration tests in a Quarkus-based application. The application includes geospatial data (e.g., airports with GeoFence geometries) and uses PostGIS for handling geometry data types. The tests restore the database to a clean state before each scenario using a dump created at the start of the test suite. However, subsequent scenarios fail with the following error:
ERROR: cache lookup failed for type [type_id]
Context: Initially, airports were added to the database during Flyway migrations, and a database trigger automatically generated geometry data for these records. Later, we introduced an API endpoint for creating airports, and the geometry for new records was generated by the application itself. As part of this change, we removed the database trigger. During integration tests: The application starts, and Flyway applies migrations, creating airports with geometry. A dump of the database is created immediately after the application starts. The first test scenario runs successfully. Before starting the second scenario, the database is restored using the initial dump, which reintroduces the airports with pre-generated geometry.
This error occurs specifically when trying to insert a new record into a table that includes a geometry column. The error is consistent, and here is the related stack trace:
SQL Error: 0, SQLState: XX000 ERROR: cache lookup failed for type 1059076 ARJUNA012125: TwoPhaseCoordinator.beforeCompletion - failed for SynchronizationImple< 0:ffff7f000001:f2b7:6790ca3e:c7, org.hibernate.resource.transaction.backend.jta.internal.synchronization.RegisteredSynchronization@32425a7c > org.hibernate.exception.GenericJDBCException: could not execute statement [ERROR: cache lookup failed for type 1059076] [insert into customer.table_name (created_by,created_on,coordinates,name...) values (?,?,?,?..)] at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:63) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:108) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197) at org.hibernate.id.insert.GetGeneratedKeysDelegate.performInsert(GetGeneratedKeysDelegate.java:107) at org.hibernate.engine.jdbc.mutation.internal.MutationExecutorPostInsertSingleTable.execute(MutationExecutorPostInsertSingleTable.java:100) at org.hibernate.persister.entity.mutation.InsertCoordinator.doStaticInserts(InsertCoordinator.java:175) at org.hibernate.persister.entity.mutation.InsertCoordinator.coordinateInsert(InsertCoordinator.java:113) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2868) at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81) at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:670) at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:291) at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:272) at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:322) at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:386) at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:300) at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:219) at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:134) at org.hibernate.event.internal.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:175) at org.hibernate.event.internal.DefaultPersistEventListener.persist(DefaultPersistEventListener.java:93) at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:77) at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:138) at org.hibernate.internal.SessionImpl.persistOnFlush(SessionImpl.java:818) at org.hibernate.engine.spi.CascadingActions8ドル.cascade(CascadingActions.java:333) at org.hibernate.engine.spi.CascadingActions8ドル.cascade(CascadingActions.java:323) at org.hibernate.engine.internal.Cascade.cascadeToOne(Cascade.java:517) at org.hibernate.engine.internal.Cascade.cascadeAssociation(Cascade.java:439) at org.hibernate.engine.internal.Cascade.cascadeProperty(Cascade.java:224) at org.hibernate.engine.internal.Cascade.cascadeCollectionElements(Cascade.java:551) at org.hibernate.engine.internal.Cascade.cascadeCollection(Cascade.java:481) at org.hibernate.engine.internal.Cascade.cascadeAssociation(Cascade.java:442) at org.hibernate.engine.internal.Cascade.cascadeProperty(Cascade.java:224) at org.hibernate.engine.internal.Cascade.cascade(Cascade.java:157) at org.hibernate.event.internal.AbstractSaveEventListener.cascadeAfterSave(AbstractSaveEventListener.java:513) at org.hibernate.event.internal.DefaultPersistEventListener.justCascade(DefaultPersistEventListener.java:161) at org.hibernate.event.internal.DefaultPersistEventListener.entityIsPersistent(DefaultPersistEventListener.java:154) at org.hibernate.event.internal.DefaultPersistEventListener.persist(DefaultPersistEventListener.java:90) at org.hibernate.event.internal.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:77) at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:138) at org.hibernate.internal.SessionImpl.persistOnFlush(SessionImpl.java:818) at org.hibernate.engine.spi.CascadingActions8ドル.cascade(CascadingActions.java:333) at org.hibernate.engine.spi.CascadingActions8ドル.cascade(CascadingActions.java:323) at org.hibernate.engine.internal.Cascade.cascadeToOne(Cascade.java:517) at org.hibernate.engine.internal.Cascade.cascadeAssociation(Cascade.java:439) at org.hibernate.engine.internal.Cascade.cascadeProperty(Cascade.java:224) at org.hibernate.engine.internal.Cascade.cascadeCollectionElements(Cascade.java:551) at org.hibernate.engine.internal.Cascade.cascadeCollection(Cascade.java:481) at org.hibernate.engine.internal.Cascade.cascadeAssociation(Cascade.java:442) at org.hibernate.engine.internal.Cascade.cascadeProperty(Cascade.java:224) at org.hibernate.engine.internal.Cascade.cascade(Cascade.java:157) at org.hibernate.event.internal.AbstractFlushingEventListener.cascadeOnFlush(AbstractFlushingEventListener.java:161) at org.hibernate.event.internal.AbstractFlushingEventListener.prepareEntityFlushes(AbstractFlushingEventListener.java:144) at org.hibernate.event.internal.AbstractFlushingEventListener.flushEverythingToExecutions(AbstractFlushingEventListener.java:79) at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:40) at org.hibernate.event.service.internal.EventListenerGroupImpl.fireEventOnEachListener(EventListenerGroupImpl.java:127) at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1403) at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:484) at org.hibernate.internal.SessionImpl.flushBeforeTransactionCompletion(SessionImpl.java:2319) at org.hibernate.internal.SessionImpl.beforeTransactionCompletion(SessionImpl.java:1976) at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.beforeTransactionCompletion(JdbcCoordinatorImpl.java:439) at org.hibernate.resource.transaction.backend.jta.internal.JtaTransactionCoordinatorImpl.beforeCompletion(JtaTransactionCoordinatorImpl.java:336) at org.hibernate.resource.transaction.backend.jta.internal.synchronization.SynchronizationCallbackCoordinatorNonTrackingImpl.beforeCompletion(SynchronizationCallbackCoordinatorNonTrackingImpl.java:47) at org.hibernate.resource.transaction.backend.jta.internal.synchronization.RegisteredSynchronization.beforeCompletion(RegisteredSynchronization.java:37) at com.arjuna.ats.internal.jta.resources.arjunacore.SynchronizationImple.beforeCompletion(SynchronizationImple.java:52) at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.beforeCompletion(TwoPhaseCoordinator.java:351) at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.end(TwoPhaseCoordinator.java:69) at com.arjuna.ats.arjuna.AtomicAction.commit(AtomicAction.java:138) at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1271) at com.arjuna.ats.internal.jta.transaction.arjunacore.BaseTransaction.commit(BaseTransaction.java:104) at io.quarkus.narayana.jta.runtime.NotifyingTransactionManager.commit(NotifyingTransactionManager.java:70) at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorBase.endTransaction(TransactionalInterceptorBase.java:406) at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorBase.invokeInOurTx(TransactionalInterceptorBase.java:171) at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorBase.invokeInOurTx(TransactionalInterceptorBase.java:107) at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorRequired.doIntercept(TransactionalInterceptorRequired.java:38) at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorBase.intercept(TransactionalInterceptorBase.java:61) at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorRequired.intercept(TransactionalInterceptorRequired.java:32) at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorRequired_Bean.intercept(Unknown Source) at io.quarkus.arc.impl.InterceptorInvocation.invoke(InterceptorInvocation.java:42) at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:70) at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:62) at io.quarkus.resteasy.runtime.StandardSecurityCheckInterceptor.intercept(StandardSecurityCheckInterceptor.java:45) at io.quarkus.resteasy.runtime.StandardSecurityCheckInterceptor_RolesAllowedInterceptor_Bean.intercept(Unknown Source) at io.quarkus.arc.impl.InterceptorInvocation.invoke(InterceptorInvocation.java:42) at io.quarkus.arc.impl.AroundInvokeInvocationContext.perform(AroundInvokeInvocationContext.java:30) at io.quarkus.arc.impl.InvocationContexts.performAroundInvoke(InvocationContexts.java:27) at ch.skycell.application.customer.controller.CompanySiteController_Subclass.createSite(Unknown Source) at ch.skycell.application.customer.controller.CompanySiteController_ClientProxy.createSite(Unknown Source) at [email protected]/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at [email protected]/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) at [email protected]/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at [email protected]/java.lang.reflect.Method.invoke(Method.java:568) at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:154) at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:118) at org.jboss.resteasy.core.ResourceMethodInvoker.internalInvokeOnTarget(ResourceMethodInvoker.java:560) at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTargetAfterFilter(ResourceMethodInvoker.java:452) at org.jboss.resteasy.core.ResourceMethodInvoker.lambda$invokeOnTarget2ドル(ResourceMethodInvoker.java:413) at org.jboss.resteasy.core.interception.jaxrs.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:321) at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget(ResourceMethodInvoker.java:415) at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:378) at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:356) at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:70) at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:429) at org.jboss.resteasy.core.SynchronousDispatcher.lambda$invoke4ドル(SynchronousDispatcher.java:240) at org.jboss.resteasy.core.SynchronousDispatcher.lambda$preprocess0ドル(SynchronousDispatcher.java:154) at org.jboss.resteasy.core.interception.jaxrs.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:321) at org.jboss.resteasy.core.SynchronousDispatcher.preprocess(SynchronousDispatcher.java:157) at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:229) at io.quarkus.resteasy.runtime.standalone.RequestDispatcher.service(RequestDispatcher.java:82) at io.quarkus.resteasy.runtime.standalone.VertxRequestHandler.dispatch(VertxRequestHandler.java:147) at io.quarkus.resteasy.runtime.standalone.VertxRequestHandler1ドル.run(VertxRequestHandler.java:93) at io.quarkus.vertx.core.runtime.VertxCoreRecorder14ドル.runWith(VertxCoreRecorder.java:582) at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513) at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1538) at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29) at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29) at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30) at [email protected]/java.lang.Thread.run(Thread.java:833) Caused by: org.postgresql.util.PSQLException: ERROR: cache lookup failed for type 1059076 at app//org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725) at app//org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412) at app//org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371) at app//org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:502) at app//org.postgresql.jdbc.PgStatement.execute(PgStatement.java:419) at app//org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:194) at app//org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:155) at io.agroal.pool.wrapper.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:90) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:194) ... 113 more
Key Observations:
- The first test scenario always works without any issues.
- When running two scenarios sequentially (restoring the database in between), the second scenario fails with the cache lookup error.
- Adding a query to fetch all rows from a table containing geospatial data (e.g., Airport) before inserting new records resolves the issue:
var allAirports = persistence.getAll(Airport.class);
Hypothesis: I believe the issue is related to PostGIS losing its connection to certain internal cache states (e.g., cached type OIDs for geometry) after restoring the database.
Environment: Quarkus 3.x PostgreSQL 14.x with PostGIS 3.x Hibernate ORM Tests use Testcontainers to set up PostgreSQL with PostGIS
Additional Details: The trigger used to generate geometry was removed in favor of application-level geometry creation. Could this change impact the validity of restored geometry data in PostGIS?
Any insights or advice on how to fix or debug this issue?