When you use a JDBC prepared statement then the number of parameters has a hard limit: 32767. If you don’t obey this limit you’ll see something like this:
java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 40000
Not a very concrete message, right? When I first saw this exception I was kind of baffled and disaffected. But after narrowing down the problem in a debug session and looking at the PostgreSQL JDBC driver’s source code the cause was obvious: the PostgreSQL client/backend protocol dictates that the number of parameters be send from the client to the Postgres backend as a 2 byte integer (aaah, now the above message actually makes sense). You’ll find details of the protocol here if you’re brave (my 2-byte friend is defined in the Parse message).
All right, got the bugger, what’s the solution now? Easy: Just split the parameter list up into chunks of say 30.000 parameters per INSERT statement.
