Quarkus集成测试中PostGIS数据库恢复后缓存查找失败问题求助

问题重现 #

基于Quarkus 3.x的应用使用PostgreSQL 14+PostGIS 3.x处理地理空间数据，集成测试时采用以下流程：

1. 应用启动后执行Flyway迁移，生成带几何数据的Airport记录
2. 立即创建数据库初始转储
3. 第一个测试场景运行正常
4. 第二个场景前用初始转储恢复数据库，插入新记录时触发错误：

ERROR: cache lookup failed for type 1059076

完整堆栈跟踪：

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.CascadingActions$8.cascade(CascadingActions.java:333) at org.hibernate.engine.spi.CascadingActions$8.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.CascadingActions$8.cascade(CascadingActions.java:333) at org.hibernate.engine.spi.CascadingActions$8.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 java.base@17.0.5/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base@17.0.5/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) at java.base@17.0.5/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.base@17.0.5/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$invokeOnTarget$2(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$invoke$4(SynchronousDispatcher.java:240) at org.jboss.resteasy.core.SynchronousDispatcher.lambda$preprocess$0(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.VertxRequestHandler$1.run(VertxRequestHandler.java:93) at io.quarkus.vertx.core.runtime.VertxCoreRecorder$14.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 java.base@17.0.5/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

原因分析 #

你的推测完全正确：问题出在数据库恢复后PostgreSQL/PostGIS的类型OID缓存不一致。

• 应用启动时，PostgreSQL驱动和Hibernate会缓存PostGIS几何类型的OID（比如geometry类型的OID）
• 用初始转储恢复数据库后，数据库内的类型OID可能发生变化，或者连接池中的旧连接仍持有失效的缓存OID
• 插入新记录时，驱动尝试用旧的OID查找类型，导致PostgreSQL抛出"cache lookup failed"错误
• 你添加的persistence.getAll(Airport.class)查询会触发Hibernate重新加载表元数据，间接刷新了类型OID缓存，所以能临时解决问题

解决方案 #

1. 恢复数据库后刷新连接池 #

Quarkus使用Agroal作为连接池，在测试中恢复数据库后，调用Agroal的API强制刷新连接池，让所有旧连接失效，新连接重新获取类型元数据：

@Inject
AgroalDataSource dataSource;

// 恢复数据库后执行
dataSource.flush();

2. 恢复后执行PostGIS类型刷新SQL #

在恢复数据库后，执行一条触发PostGIS类型元数据加载的SQL，强制驱动刷新缓存：

SELECT postgis_full_version();

或者直接查询几何类型的OID：

SELECT oid, typname FROM pg_type WHERE typname LIKE 'geometry%';

3. 修改测试流程，避免复用初始转储 #

放弃初始转储的方式，每个测试场景前重新执行Flyway迁移，虽然测试速度会变慢，但能彻底避免缓存不一致问题。在Quarkus测试中可以用@TestTransaction或自定义测试生命周期钩子实现。

4. 禁用Hibernate的元数据缓存 #

在测试配置中添加：

quarkus.hibernate-orm.metadata-cache.enabled=false

让Hibernate每次操作都重新加载元数据，不过这会影响测试性能，仅建议用于测试环境。

针对补充疑问的解答 #

移除数据库触发器改为应用层生成几何数据，不会影响恢复后几何数据的有效性。几何数据是以PostGIS标准的二进制格式存储在列中的，和生成方式（触发器/应用层）无关。问题的核心是类型OID缓存，不是数据本身的有效性。

调试建议 #

• 对比恢复前后的pg_type表，查看geometry类型的OID是否变化：执行SELECT oid FROM pg_type WHERE typname = 'geometry';
• 开启PostgreSQL的日志，添加log_statement = 'all'和log_min_messages = debug，查看类型查找的详细错误日志
• 在测试中打印PostgreSQL驱动的类型缓存信息：

PGConnection pgConn = dataSource.getConnection().unwrap(PGConnection.class);
TypeInfo typeInfo = pgConn.getTypeInfo();
// 打印geometry类型的OID
System.out.println("Geometry OID: " + typeInfo.getPGType("geometry"));

内容的提问来源于stack exchange，提问作者Самир Шахмурадлы