Tried to send an out-of-range integer as a 2-byte value

背景

Cause: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.

; An I/O error occurred while sending to the backend.; nested exception is org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend. at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:107) ~[spring-jdbc-5.3.27.jar:5.3.27] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) ~[spring-jdbc-5.3.27.jar:5.3.27] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) ~[spring-jdbc-5.3.27.jar:5.3.27] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) ~[spring-jdbc-5.3.27.jar:5.3.27] at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88) ~[mybatis-spring-2.0.4.jar:2.0.4] at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440) ~[mybatis-spring-2.0.4.jar:2.0.4] at com.sun.proxy.$Proxy107.insert(Unknown Source) ~[?:?] at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:271) ~[mybatis-spring-2.0.4.jar:2.0.4] at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62) ~[mybatis-3.5.4.jar:3.5.4] at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:144) ~[mybatis-3.5.4.jar:3.5.4] at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85) ~[mybatis-3.5.4.jar:3.5.4] at com.sun.proxy.$Proxy161.batchInsert(Unknown Source) ~[?:?] at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[?:1.8.0_301] at java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:266) ~[?:1.8.0_301] at java.util.concurrent.FutureTask.run(FutureTask.java) ~[?:1.8.0_301] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_301] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_301] at java.lang.Thread.run(Thread.java:748) [?:1.8.0_301] Caused by: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend. at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:349) ~[postgresql-42.2.25.jar:42.2.25] at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481) ~[postgresql-42.2.25.jar:42.2.25] at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401) ~[postgresql-42.2.25.jar:42.2.25] at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164) ~[postgresql-42.2.25.jar:42.2.25] at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153) ~[postgresql-42.2.25.jar:42.2.25] at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-4.0.3.jar:?] at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-4.0.3.jar:?] at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) ~[mybatis-3.5.4.jar:3.5.4] at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.5.4.jar:3.5.4] at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.4.jar:3.5.4] at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.4.jar:3.5.4] at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.5.4.jar:3.5.4] at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[mybatis-3.5.4.jar:3.5.4] at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184) ~[mybatis-3.5.4.jar:3.5.4] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_301] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_301] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_301] at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_301] at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ~[mybatis-spring-2.0.4.jar:2.0.4] ... 25 more Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 51765 at org.postgresql.core.PGStream.sendInteger2(PGStream.java:349) ~[postgresql-42.2.25.jar:42.2.25] at org.postgresql.core.v3.QueryExecutorImpl.sendParse(QueryExecutorImpl.java:1559) ~[postgresql-42.2.25.jar:42.2.25] at org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:1884) ~[postgresql-42.2.25.jar:42.2.25] at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1445) ~[postgresql-42.2.25.jar:42.2.25] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:314) ~[postgresql-42.2.25.jar:42.2.25] at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481) ~[postgresql-42.2.25.jar:42.2.25] at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401) ~[postgresql-42.2.25.jar:42.2.25] at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164) ~[postgresql-42.2.25.jar:42.2.25] at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153) ~[postgresql-42.2.25.jar:42.2.25] at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-4.0.3.jar:?] at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-4.0.3.jar:?] at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) ~[mybatis-3.5.4.jar:3.5.4] at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.5.4.jar:3.5.4] at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.4.jar:3.5.4] at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.4.jar:3.5.4] at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.5.4.jar:3.5.4] at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[mybatis-3.5.4.jar:3.5.4] at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184) ~[mybatis-3.5.4.jar:3.5.4] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_301] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_301] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_301] at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_301] at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ~[mybatis-spring-2.0.4.jar:2.0.4] ... 25 more

分析

2-byte的范围是：-32768~32767

从代码中看到参数为51765，远远超过了short的限制

但是，我的insert参数只有2465个

如何会达到51765个？

从debug中看，pgsql会将所有的pos按照顺序拆开成一个数组，即所有的列都拼在一起：[P0_field1, P0_field2, P1_field1, P1_field2, P2_field1, P2_field2, P3_field1, P3_field2]

po这里有21个field，则21 * 2465 = 51765

所以，还是pos太大了，需要拆分

解决方法

不要一次insert，拆分成多次。

考虑到short.MAXVALUE的值，最大的insert为 32767 / 21 = 1560个

扩展：field字段超过大小，报错形式

刚开始还以为是字段值超过了大小，但是看table中都是int4的，不存在超过大小的情况。

超过大小的报错内容为：

Caused by: org.postgresql.util.PSQLException: ERROR: smallint out of range at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2565) ~[postgresql-42.2.25.jar:42.2.25]

Caused by: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(32) at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2565) ~[postgresql-42.2.25.jar:42.2.25]