postgresql 的 idle_session_timeout 与连接池的 max-ide-time参数

发布时间 2023-06-01 11:22:30作者: 白衣风云

看下面的异常:

  • 下面的错误说:terminating connection due to idle-session timeout

  • 下面的这个错误说:Caused by: reactor.pool.PoolShutdownException: Pool has been shut down

reactor.core.Exceptions$ErrorCallbackNotImplemented: org.springframework.dao.DataAccessResourceFailureException: Failed to obtain R2DBC Connection; nested exception is reactor.pool.PoolShutdownException: Pool has been shut down
Caused by: org.springframework.dao.DataAccessResourceFailureException: Failed to obtain R2DBC Connection; nested exception is reactor.pool.PoolShutdownException: Pool has been shut down
    at org.springframework.r2dbc.connection.ConnectionFactoryUtils.lambda$getConnection$0(ConnectionFactoryUtils.java:88) ~[spring-r2dbc-5.3.17.jar:5.3.17]
    at reactor.core.publisher.Mono.lambda$onErrorMap$31(Mono.java:3733) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:94) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:106) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxRetry$RetrySubscriber.onError(FluxRetry.java:95) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.MonoFlatMap$FlatMapMain.onError(MonoFlatMap.java:172) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.pool.AbstractPool$Borrower.fail(AbstractPool.java:477) ~[reactor-pool-0.2.8.jar:0.2.8]
    at reactor.pool.SimpleDequePool.doAcquire(SimpleDequePool.java:292) ~[reactor-pool-0.2.8.jar:0.2.8]
    at reactor.pool.AbstractPool$Borrower.request(AbstractPool.java:432) ~[reactor-pool-0.2.8.jar:0.2.8]
    at reactor.core.publisher.MonoFlatMap$FlatMapMain.onSubscribe(MonoFlatMap.java:110) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.pool.SimpleDequePool$QueueBorrowerMono.subscribe(SimpleDequePool.java:720) ~[reactor-pool-0.2.8.jar:0.2.8]
    at reactor.core.publisher.InternalMonoOperator.subscribe(InternalMonoOperator.java:64) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.MonoDefer.subscribe(MonoDefer.java:52) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxRetry$RetrySubscriber.resubscribe(FluxRetry.java:117) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.MonoRetry.subscribeOrReturn(MonoRetry.java:50) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.Mono.subscribe(Mono.java:4385) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:103) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.MonoFlatMap$FlatMapMain.onError(MonoFlatMap.java:172) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxMap$MapSubscriber.onError(FluxMap.java:132) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.Operators.error(Operators.java:198) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.MonoError.subscribe(MonoError.java:53) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.MonoDeferContextual.subscribe(MonoDeferContextual.java:55) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.Mono.subscribe(Mono.java:4400) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.MonoUsingWhen.subscribe(MonoUsingWhen.java:96) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.Mono.subscribe(Mono.java:4400) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.MonoFlatMapMany$FlatMapManyMain.onNext(MonoFlatMapMany.java:195) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxMap$MapSubscriber.onNext(FluxMap.java:120) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxDefaultIfEmpty$DefaultIfEmptySubscriber.onNext(FluxDefaultIfEmpty.java:101) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxMapFuseable$MapFuseableSubscriber.onNext(FluxMapFuseable.java:127) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxMapFuseable$MapFuseableSubscriber.onNext(FluxMapFuseable.java:127) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxPeekFuseable$PeekFuseableSubscriber.onNext(FluxPeekFuseable.java:210) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxMapFuseable$MapFuseableSubscriber.onNext(FluxMapFuseable.java:127) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.Operators$MonoSubscriber.complete(Operators.java:1816) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.MonoCollectList$MonoCollectListSubscriber.onComplete(MonoCollectList.java:128) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxConcatMap$ConcatMapImmediate.drain(FluxConcatMap.java:368) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxConcatMap$ConcatMapImmediate.onComplete(FluxConcatMap.java:276) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.Operators.complete(Operators.java:137) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxIterable.subscribe(FluxIterable.java:148) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxIterable.subscribe(FluxIterable.java:87) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.Flux.subscribe(Flux.java:8469) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxUsingWhen.subscribe(FluxUsingWhen.java:94) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.Mono.subscribe(Mono.java:4400) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.Mono.subscribeWith(Mono.java:4515) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.Mono.subscribe(Mono.java:4371) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.Mono.subscribe(Mono.java:4307) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.Mono.subscribe(Mono.java:4254) ~[reactor-core-3.4.16.jar:3.4.16]
    at com.fengyun.medical.orderservice.handlers.rabbitmq.rpc.OrderPaymentHandler.lambda$updatePayStatus$2(OrderPaymentHandler.java:66) ~[classes/:na]
    at reactor.core.publisher.LambdaSubscriber.onNext(LambdaSubscriber.java:160) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxCreate$BufferAsyncSink.drain(FluxCreate.java:793) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxCreate$BufferAsyncSink.next(FluxCreate.java:718) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.core.publisher.FluxCreate$SerializedFluxSink.next(FluxCreate.java:154) ~[reactor-core-3.4.16.jar:3.4.16]
    at reactor.rabbitmq.Receiver.lambda$null$9(Receiver.java:216) ~[reactor-rabbitmq-1.5.5.jar:1.5.5]
    at com.rabbitmq.client.impl.recovery.AutorecoveringChannel$2.handleDelivery(AutorecoveringChannel.java:588) ~[amqp-client-5.13.1.jar:5.13.1]
    at com.rabbitmq.client.impl.ConsumerDispatcher$5.run(ConsumerDispatcher.java:149) ~[amqp-client-5.13.1.jar:5.13.1]
    at com.rabbitmq.client.impl.ConsumerWorkService$WorkPoolRunnable.run(ConsumerWorkService.java:104) ~[amqp-client-5.13.1.jar:5.13.1]
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) ~[na:na]
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) ~[na:na]
    at java.base/java.lang.Thread.run(Thread.java:833) ~[na:na]
Caused by: reactor.pool.PoolShutdownException: Pool has been shut down
    ... 51 common frames omitted

造成上面的原因是 PG中的参数idle_session_timeout,这个参数默认为0,表示禁用。数据库默认的最大连接数为100.在频繁的数据库操作中会报最大连接数已满,当时为了解决这个问题我修改了配置文件中idle_session_timeout为5分钟,也由此为后来的报错埋下了坑。当使用连接池去连接时,连接池中的max-ide-time默认为30分钟。数据库在超过5分钟内没有交互时,会自动断开连接而连接池此时还不知道连接已断,所以就造成上面的错误。

解决方法:

修改连接池参数: max-ide-time 为10s



来自为知笔记(Wiz)