4

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?

Tregoreg
  • 18,872
  • 15
  • 48
  • 69
  • You're trying to execute a `PreparedStatement` with 250000 parameters? There's your problem. Try creating a temp. table instead, use batched inserts to that table, then join with the temp. table. – Andreas Dec 18 '16 at 01:18
  • 1
    @Andreas I see your point and thanks, I'll probably have to do something like that. I know 250K is a lot, but still, as long as computers have no emotions, it must be some limit put somewhere that I've reached. My question is about that limit and whether I can increase it. I've read that psql query size limit is 1GB, but my parameters are short identifiers, it must be somewhere else... – Tregoreg Dec 18 '16 at 01:40
  • The limit might depend on the JDBC driver; [some drivers](http://stackoverflow.com/questions/14631346/java-jdbc-prepared-statement-maximum-parameter-markers) have the limit set to 2k. I agree with Andreas; use a plain insert with `addBatch()` or some other batch insert variant that's supported by the driver. – Mick Mnemonic Dec 18 '16 at 02:05
  • @MickMnemonic It isn't as much the driver, but mostly limits of the server. The driver may just pre-enforce the limits, sometimes slightly lower. E.g. MS SQL 2016 has max. 2100 parameters per stored procedure (see https://msdn.microsoft.com/en-us/library/ms143432.aspx), and I believe the JDBC driver uses temporary stored procedures for PreparedStatements. – Andreas Dec 18 '16 at 02:25

1 Answers1

6

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));
teppic
  • 7,051
  • 1
  • 29
  • 35