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

Question

I am using mybatis to insert the data into postgresql db. I have 19629 number of records to insert. I am trying to insert all records in one time. But if I pass more that 6k records to the query I am getting Cause: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.

So is there any limit to number of insert records in one time in postgresql?

Mybatis code.

{ @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.

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

The error may involve com.apds.mybatis.mapper.overdrive.OverdriveTotMapper.insert-Inline

The error occurred while setting parameters SQL: insert into temp_overdrive_csv_dtls (lpat_library_card_number,day_of_use,sessions,minutes_read,hours_read,sys_created_by) values (?,?, ?, ?, ?, ?) , (?,?, ?, ?, ?, ?) , (?,?, ?, ?, ?, ?) , (?,?, ?, ?, ?, ?) , , (?,?, ?, ?, ?, ?)

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

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

It is not the number of rows, but the number of placeholders.
Most drivers have a limit on the number of placeholders of PreparedStatement (32767 with pgjdbc, I think).
This is one of the reasons why multi-row insert is not recommended when inserting or updating a large number of rows (another reason is performance).

You should switch to batch insert.
Please see this answer for an example code.