Search code examples
springpostgresqlspring-data-jdbc

Spring Data CrudRepository.findAllById exception with large number of IDs

I am using the Spring Data JDBC (version 1.1.6) method CrudRepository.findAllById to load entities from a database with a large number of IDs. The underlying database connection uses a Postgres database. The invocation of the method raises a PSQLException:

2020-05-28 05:58:35,260 WARN com.zaxxer.hikari.pool.ProxyConnection [task-2] HikariPool-1 - Connection org.postgresql.jdbc.PgConnection@1224f39f marked as broken because of SQLSTATE(08006), ErrorCode(0)
org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:358)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:109)
...
Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 137525
    at org.postgresql.core.PGStream.sendInteger2(PGStream.java:275)
    at org.postgresql.core.v3.QueryExecutorImpl.sendParse(QueryExecutorImpl.java:1553)
    at org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:1876)
    at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1439)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)

The exception seem to be ultimately caused by a 16-bit limit on the number of values in the SELECT IN (?) clause, that is generated by Sprint Data JDBC upon using findAllById.

Am I supposed to partition the list of IDs myself? Shouldn't the CrudRepository.findAllById handle this correctly in a fashion compatible with the underlying database dialect?

Solution

  • Am I supposed to partition the list of IDs myself?

    Yes, assuming this kind of query makes sense in the first place.

    Spring Data JDBC currently creates a straight forward select ... where id in (..) query which in turn is limited by the capabilities of the underlying database/JDBC driver.

    With the apparent limit being ~216 for Postgres there doesn't seem to be much need for special handling in Spring Data JDBC since looking for so many ids in a single select seems rare enough to justify some manual coding.