Java 17+WildFly 26升级后PostgreSQL连接异常问题咨询

将Java版本从8升级至17并完成WildFly升级后，出现三类数据库连接相关异常。其中HeuristicMixedException已通过设置max_prepared_transactions解决，现咨询以下两类异常的解决方案：

1. 连接已关闭错误 #

Cause: org.postgresql.util.PSQLException: This connection has been closed.
; This connection has been closed.; nested exception is org.postgresql.util.PSQLException: This connection has been closed.","message":"
### Error querying database.  Cause: org.postgresql.util.PSQLException: This connection has been closed.
### The error may exist in mybatis

2. 连接重置错误 #

"message":"An I/O error occurred while sending to the backend.","name":"org.postgresql.util.PSQLException","cause":{"commonElementCount":108,"localizedMessage":"Connection reset","message":"Connection reset","name":"java.net.SocketException","extendedStackTrace":"java.net.SocketException: Connection reset

当前XA数据源配置 #

<xa-datasource jndi-name="java:/datasourvr" pool-name="poolname" enabled="true" use-java-context="true">
    <xa-datasource-property name="ServerName">
        {{ serverName }}
    </xa-datasource-property>
    <xa-datasource-property name="PortNumber">
        {{ port }}
    </xa-datasource-property>
    <xa-datasource-property name="DatabaseName">
        {{ dbname }}
    </xa-datasource-property>
    <xa-datasource-property name="User">
        {{ .Values.db.username }}
    </xa-datasource-property>
    <xa-datasource-property name="Password">
        {{ .Values.db.password }}
    </xa-datasource-property>
    <xa-datasource-property name="PrepareThreshold">
        0
    </xa-datasource-property>
    <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
    <driver>postgresql</driver>
    <new-connection-sql>select 1</new-connection-sql>
    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
    <xa-pool>
        <min-pool-size>0</min-pool-size>
        <max-pool-size>50</max-pool-size>
    </xa-pool>
    <validation>
        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>
    </validation>
    <timeout>
        <idle-timeout-minutes>1</idle-timeout-minutes>
    </timeout>
    <statement>
        <prepared-statement-cache-size>32</prepared-statement-cache-size>
        <share-prepared-statements>true</share-prepared-statements>
    </statement>
</xa-datasource>

针对「连接已关闭错误」 #

1. 完善连接验证机制：在现有validation配置中添加连接校验SQL并开启匹配时验证，确保从连接池获取的连接立即生效：

   <validation>
       <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
       <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>
       <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>
       <validate-on-match>true</validate-on-match>
   </validation>
   

2. 调整空闲超时配置：当前1分钟的空闲超时若大于PostgreSQL端的idle_in_transaction_session_timeout或tcp_keepalives_idle，会导致数据库主动关闭连接但连接池未感知。建议将空闲超时设为小于数据库端的超时值，比如30秒：

   <timeout>
       <idle-timeout-minutes>0.5</idle-timeout-minutes>
   </timeout>
   

3. 规范MyBatis连接管理：确保代码中用try-with-resources管理SqlSession，避免连接长期占用未归还：

   try (SqlSession session = sqlSessionFactory.openSession()) {
       // 执行数据库操作
   }
   

针对「连接重置错误」 #

1. 启用TCP保活：在数据源配置中添加TCP保活属性，防止网络层主动断开长连接：

   <xa-datasource-property name="tcpKeepAlive">true</xa-datasource-property>
   <xa-datasource-property name="tcpKeepIdle">60</xa-datasource-property> <!-- 单位：秒 -->
   

2. 增强连接池容错性：添加查询超时与重试配置，提升连接稳定性：

   <timeout>
       <idle-timeout-minutes>0.5</idle-timeout-minutes>
       <query-timeout>30</query-timeout> <!-- 单位：秒 -->
   </timeout>
   <xa-pool>
       <min-pool-size>0</min-pool-size>
       <max-pool-size>50</max-pool-size>
       <retry-attempts>3</retry-attempts>
   </xa-pool>
   

3. 升级PostgreSQL驱动：Java 17与新版WildFly需适配更高版本的驱动，建议升级至42.6.x及以上版本，确保兼容性。
4. 排查网络中间件：检查数据库与应用服务器之间的防火墙、负载均衡器是否存在超时规则，避免中间设备主动重置连接。

内容的提问来源于stack exchange，提问作者karthick M