PG的JDBC对SQL中绑定变量个数的限制

瀚高数据库

目录

环境
症状
问题原因
解决方案

环境

系统平台：N/A
版本：N/A

症状

问题

有开发人员使用一条Insert values 语句，插入多行数据。例如：表A有88个字段，一条INSERT values …中包含500条数据。

报错如下：

2023-09-09 01:30:10.787  WARN 449840 --- [   scheduling-1] com.zaxxer.hikari.pool.ProxyConnection   : HikariPool-1 - Connection com.highgo.jdbc.jdbc.PgConnection@4ebf3451 marked as broken because of SQLSTATE(08006), ErrorCode(0)

com.highgo.jdbc.util.PSQLException: An I/O error occurred while sending to the backend.

------skip many rows------

Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 44589

...

问题原因

postgresql的JDBC对于sql语句的参数数量是有限制的，最大为32767。

JDBC源代码为：

public void sendInteger2(int val) throws IOException {

        if (val >= -32768 && val <= 32767) {

            this.int2Buf[0] = (byte)(val >>> 8);

            this.int2Buf[1] = (byte)val;

            this.pgOutput.write(this.int2Buf);

        } else {

            throw new IOException("Tried to send an out-of-range integer as a 2-byte value: " + val);

        }

    }

从源代码中可以看到pgsql使用2个字节的有符号integer，故其取值范围为[-32768, 32767]。

这意味着sql语句的变量数量，即行数*列数之积必须小于等于32767.

解决方案

1. 分批Insert

如果一次插入的数据量太多，使得绑定变量数量超过了32767最大值，那么只能分批插入。

2. 更新JDBC驱动

该限制在较新的 PostgreSQL 驱动程序42.4.0中得到缓解，在新版本中最多可以传递 65535 条记录。解决方式是将原来的的有符号int2变为了2字节的无符号长度数。

PG JDBC驱动42.4.0参考文档：

https://jdbc.postgresql.org/changelogs/2022-06-09-42.4.0-release/

3.修改jdbc参数preferQueryMode=simple

想更进一步解决该问题：

参考：

https://jdbc.postgresql.org/documentation/use/

使用jdbc:postgresql://:/?preferQueryMode=simple命令将JDBC连接串修改为Simple模式

配置jdbc的参数 preferQueryMode=simple

该参数会将整个SQL作为一整个文本传入数据库，不会进行绑定变量操作

参数解释：

preferQueryMode(String) 默认值：extended

指定使用哪种模式对数据库执行查询：

1. simple 表示（‘Q’ execute，无解析，无绑定，仅文本模式），

2. extended 表示始终使用绑定/执行消息，

3. extendedForPrepared 表示仅针对准备好的语句进行扩展，

4. extendedCacheEverything
   表示使用extended协议并尝试将每个语句（包括Statement.execute(String sql)）缓存在查询缓存中。