Spring Data+PostgreSQL偶发报错：预准备语句S_25已存在

问题场景 #

在Spring Data结合PostgreSQL（Supabase）开发过程中，出现偶发异常ERROR: prepared statement "S_25" already exists。该异常并非每次请求都会触发，通常在发送5-10次API请求后出现，之后可正常处理几次请求，随后再次报错。同一应用中仅部分API受影响，复用另一应用的安全配置未出现此问题。环境信息：Java 17、SpringBoot 3.2.2/3.3.1、postgresql JDBC驱动42.7.2、Windows 11。

报错日志 #

ERROR: prepared statement "S_25" already exists
2024-07-18T17:40:04.358+02:00 ERROR 65632 --- [nio-8080-exec-2] o.a.c.c.C.[.[.[.[dispatcherServlet]      : Servlet.service() for servlet [dispatcherServlet] in context with path [/api] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select c1_0.id,c1_0.gdp,c1_0.location_id,l1_0.id,lj1_0.id,lj1_0.img_name,l1_0.location_type,l1_0.x,l1_0.y,c1_0.max_population,c1_0.name,c1_0.population,c1_0.stage from city c1_0 join location l1_0 on l1_0.id=c1_0.location_id left join location_jobs lj1_0 on lj1_0.id=l1_0.location_jobs_id where c1_0.location_id=?] [ERROR: prepared statement "S_25" already exists] [n/a]; SQL [n/a]] with root cause

org.postgresql.util.PSQLException: ERROR: prepared statement "S_25" already exists

可能原因 #

该异常通常与PostgreSQL JDBC驱动的预编译语句缓存机制、连接池配置，或Supabase的连接代理（Pgbouncer）兼容性有关：

• 连接池复用的连接中残留未清理的预编译语句，新请求尝试创建同名语句时冲突
• JDBC驱动的prepareThreshold配置过高，导致连接上累积大量预编译语句
• Supabase默认的Pgbouncer连接池模式与驱动的预编译逻辑不匹配

解决方案 #

1. 调整JDBC驱动预编译语句缓存配置 #

在application.properties或application.yml中添加以下配置，禁用或限制预编译语句缓存：

# 关闭预编译语句缓存
spring.datasource.hikari.data-source-properties.preparedStatementCacheSize=0
spring.datasource.hikari.data-source-properties.preparedStatementCacheSqlLimit=0
# 关闭自动预编译转换
spring.datasource.hikari.data-source-properties.prepareThreshold=0

设置prepareThreshold=0后，驱动不会自动将普通SQL转为预编译语句，避免连接复用时代码尝试创建已存在的预编译语句。

2. 优化连接池配置 #

确保连接池的连接回收机制正常，以HikariCP为例：

spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.connection-test-query=SELECT 1

通过合理设置连接生命周期和空闲超时，确保连接复用前经过有效性检查，清理残留资源。

3. 适配Supabase的Pgbouncer配置 #

Supabase默认使用Pgbouncer的transaction模式，需添加驱动配置适配：

spring.datasource.hikari.data-source-properties.pgBouncer=true

该配置会让JDBC驱动适配Pgbouncer的事务级连接复用逻辑，避免预编译语句冲突。

4. 排查特定API的代码问题 #

异常对应的SQL是City表的查询语句，需检查该API对应的Repository方法：

• 是否手动创建了预编译语句但未正确关闭
• 是否存在自定义SQL查询的预编译语句复用逻辑问题
  确保所有JDBC资源（PreparedStatement、ResultSet等）都被正确释放，Spring Data通常会自动处理，但自定义查询时需注意。

5. 升级JDBC驱动版本 #

当前使用的42.7.2版本可能存在已知的预编译缓存bug，尝试升级到最新稳定版（如42.7.3及以上），查看官方发布说明是否修复了相关问题。

验证方法 #

每次修改配置后重启应用，连续发送5-10次以上API请求，观察是否仍出现异常。

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