Java PostgreSQL error on large query: An I/O error occurred while sending to the backend

Question

In Java, using java.sql.PreparedStatement, I'm trying to submit a rather large query, containing constant (VALUES (?), (?), (?)...) expression for efficient join.

There are cca 250K values, so I'm also setting 250K parameters.

In Java, I'm getting

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

On my server PostgreSQL log, there is a single line about that error:

incomplete message from client

Any idea about some setting I could change anywhere to make my large query work?

Answer 1

The maximum number of parameters the JDBC driver can pass to the backend is 32767. This is limited by the v3 wire protocol, which passes the parameter count in a 16 bit int (see the doc for the definition of the Bind message).

You can work around this by passing values in an array and unnesting them on the server:

// Normally this would be one too many parameters
Integer[] ids = new Integer[Short.MAX_VALUE  + 1];
Arrays.setAll(ids, i -> i);
// Pass them in an array as a single parameter and unnest it 
PreparedStatement stmt = con.prepareStatement(
    "WITH ids (id) AS (SELECT unnest (?)) " +
    "SELECT f.* FROM foo f JOIN ids i ON i.id = f.id"
);
stmt.setArray(1, con.createArrayOf("INT", ids));