原因：org.PostgreSQL.util.psqlException：发送到后端时发生I/O错误

Question

我正在使用mybatis将数据插入到postgresql DB中。我有19629个记录要插入。我正在尝试一次插入所有记录。但是，如果我向查询传递超过6K条记录，我将得到原因：org.PostgreSQL L.util.psqlException：发送到后端时发生I/O错误。

{ @Insert({ "<script>","insert into temp_overdrive_csv_dtls (lpat_library_card_number,day_of_use,sessions,minutes_read,hours_read,sys_created_by)","values ", "<foreach  collection='recordList' item='record' separator=','>","(#{record.lpatLibraryCardNumber},#{record.dayofUse}, #{record.sessions}, #{record.minutesRead}, #{record.hoursRead}, #{record.sysCreatedBy})","</foreach>", "</script>" })public Integer insert(@Param("recordList") List<CsvRecord> recordList); 

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

设置参数SQL：insert到temp_overdrive_csv_dtls(LPAT_LIBRARY_CARD_NUMER、day_of_use、sessions、minutes_read、hours_read、sys_created_by)值（？、？、？、？、？、？、？）时出错,（？,？,？,？,？,？,？）,（？,？,？,？,？,？,？）,（？,？,？,？,？,？,？）,（？,？,？,？,？,？,？）

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:150)
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:137)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:46)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)
at com.sun.proxy.$Proxy79.insert(Unknown Source)
at com.apds.overdrive.service.OverdriveService.processRequest(OverdriveService.java:105)
at com.apds.overdrive.PartnerOverdriveApplication.main(PartnerOverdriveApplication.java:75)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:49)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:336)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:138)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:100)
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:148)
... 11 more

Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 36000
at org.postgresql.core.PGStream.sendInteger2(PGStream.java:252)
at org.postgresql.core.v3.QueryExecutorImpl.sendParse(QueryExecutorImpl.java:1470)
at org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:1793)
at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1356)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:301)
... 21 more

Answer 1

它不是行数，而是占位符的数量。
大多数驱动程序对PreparedStatement的占位符数量有限制（我认为是32767与pgjdbc）。
这是为什么在插入或更新大量行时不建议使用多行插入的原因之一（另一个原因是性能）。

您应该切换到批处理插入。
请查看此答案以获得示例代码。