Liquibase 4.24.0重建PostgreSQL存储过程时连接关闭异常求助

环境与触发场景 #

• 运行环境：Windows 10
• 工具版本：Liquibase 4.24.0
• 目标数据库：PostgreSQL
• 异常触发：执行重建函数/存储过程步骤时，抛出错误：Unexpected Error running Liquibase: This Connection has been Closed
• 异常特征：每次固定在同一存储过程触发，移除该存储过程后会在下一个存储过程处失败；涉事存储过程未做任何修改；暂不考虑升级Liquibase版本。

异常堆栈信息 #

[2024-06-26 11:22:02] INFO [liquibase.command] Update command encountered an exception.
[2024-06-26 11:22:02] WARNING [liquibase.lockservice] Failed to release change log lock
[2024-06-26 11:22:02] WARNING [liquibase.command] Update command encountered exception
java.lang.NullPointerException
    at liquibase.executor.jvm.JdbcExecutor.showSqlWarnings(JdbcExecutor.java:105)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:86)
    at liquibase.executor.jvm.JdbcExecutor.query(JdbcExecutor.java:222)
    at liquibase.executor.jvm.JdbcExecutor.query(JdbcExecutor.java:230)
    at liquibase.executor.jvm.JdbcExecutor.queryForObject(JdbcExecutor.java:238)
    at liquibase.executor.jvm.JdbcExecutor.queryForObject(JdbcExecutor.java:253)
    at liquibase.executor.jvm.JdbcExecutor.queryForObject(JdbcExecutor.java:248)
    at liquibase.database.core.DatabaseUtils.initializeDatabase(DatabaseUtils.java:41)
    at liquibase.database.core.PostgresDatabase.rollback(PostgresDatabase.java:404)
    at liquibase.lockservice.StandardLockService.releaseLock(StandardLockService.java:415)
    at liquibase.command.core.AbstractUpdateCommandStep.doRun(AbstractUpdateCommandStep.java:125)
    at liquibase.command.core.AbstractUpdateCommandStep.lambda$run$0(AbstractUpdateCommandStep.java:63)
    at liquibase.Scope.lambda$child$0(Scope.java:184)
    at liquibase.Scope.child(Scope.java:193)
    at liquibase.Scope.child(Scope.java:183)
    at liquibase.Scope.child(Scope.java:162)
    at liquibase.command.core.AbstractUpdateCommandStep.run(AbstractUpdateCommandStep.java:62)
    at liquibase.command.core.UpdateCommandStep.run(UpdateCommandStep.java:106)
    at com.datical.liquibase.ext.command.ProUpdateCommandStep.run(Unknown Source)
    at liquibase.command.CommandScope.execute(CommandScope.java:214)
    at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:55)
    at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:24)
    at picocli.CommandLine.executeUserObject(CommandLine.java:2041)
    at picocli.CommandLine.access$1500(CommandLine.java:148)
    at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2461)
    at picocli.CommandLine$RunLast.handle(CommandLine.java:2453)
    at picocli.CommandLine$RunLast.handle(CommandLine.java:2415)
    at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2273)
    at picocli.CommandLine$RunLast.execute(CommandLine.java:2417)
    at picocli.CommandLine.execute(CommandLine.java:2170)
    at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$2(LiquibaseCommandLine.java:383)
    at liquibase.Scope.child(Scope.java:193)
    at liquibase.Scope.child(Scope.java:169)
    at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$3(LiquibaseCommandLine.java:358)
    at liquibase.Scope.child(Scope.java:193)
    at liquibase.Scope.child(Scope.java:169)
    at liquibase.integration.commandline.LiquibaseCommandLine.execute(LiquibaseCommandLine.java:356)
    at liquibase.integration.commandline.LiquibaseCommandLine.main(LiquibaseCommandLine.java:96)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at liquibase.integration.commandline.LiquibaseLauncher.main(LiquibaseLauncher.java:133)

原因分析 #

从堆栈信息看，核心异常是NullPointerException，发生在JdbcExecutor.showSqlWarnings方法中，说明Liquibase尝试读取SQL警告时，数据库连接已经被关闭，导致相关对象为null。结合场景推导可能原因：

1. 连接超时被回收：存储过程执行时间过长，触发了JDBC连接池或PostgreSQL端的超时机制，连接被提前断开
2. DDL事务冲突：PostgreSQL的DDL操作会隐式提交事务，而Liquibase的事务管理逻辑可能未正确处理这种情况，导致连接状态异常
3. 存储过程资源占用：涉事存储过程可能存在长时间运行、资源占用过高的情况，导致数据库端主动断开连接

解决思路 #

1. 调整连接超时参数 #

• 修改JDBC URL，增加连接超时相关参数：

  jdbc:postgresql://host:port/db?connectTimeout=60000&socketTimeout=300000&tcpKeepAlive=true
  

• 若使用连接池，调整maxIdle、maxWait等参数，延长连接存活时间

2. 拆分变更集 #

将批量重建存储过程的操作拆分为多个独立的changeSet，每个changeSet仅处理一个存储过程，减少单个事务的执行时长，降低连接超时概率

3. 禁用SQL警告日志 #

修改Liquibase的日志配置，关闭SQL警告输出，避免因连接关闭后访问null对象触发NPE：

• 在liquibase.properties中添加：log.level.liquibase.executor.jvm.JdbcExecutor=SEVERE

4. 调整数据库端连接设置 #

查看PostgreSQL的idle_in_transaction_session_timeout参数，将其调整为更大的值（例如300s），防止数据库主动断开长时间处于事务中的连接

5. 显式指定事务属性 #

在存储过程对应的changeSet中添加runInTransaction="false"，因为PostgreSQL的DDL不支持事务回滚，强制事务管理可能导致连接状态异常

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