Where - in usage throws IOException

[Shyam]

Hi there,

I am in JOOQ v 3.7.3.

In my usage, I am trying to fetch records with ids matching those ids in my collection.
The collection has around 35293 items which is out of range for an Integer2. Hence it throws an IOException.
The code would look like below,

final Set<Long> idValues = ...; // set of 35293 long values
dslContext.selectFrom(TEST_DIMENSION).where(TEST_DIMENSION.ID.in(idValues))
          .fetch().intoMap(TEST_DIMENSION.ID, TEST_DIMENSION.DID);

To avoid this exception I am left with option of breaking up idValues in to partial set and execute query for each partial set.

Why is that where - in implementation limits the collection size to Integer2 ?
What is the recommended way to use where - in when I have large collection like this?

Thanks in advance.

--
Shyam

[Shyam]

Here is the stack trace,

org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:227)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:161)
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:155)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
    at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:194)
    at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:269)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:348)
    at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:312)
    at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2705)
Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 35293
    at org.postgresql.core.PGStream.SendInteger2(PGStream.java:214)
    at org.postgresql.core.v3.QueryExecutorImpl.sendParse(QueryExecutorImpl.java:1329)
    at org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:1629)
    at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1216)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:197)

[Lukas Eder]

Hi Shyam,

PostgreSQL has a limit of 32767 bind variables per statement. In all jOOQ versions, you can explicitly inline your bind variables to prevent running into this limit. See also this section of the manual.

https://www.jooq.org/doc/latest/manual/sql-building/bind-values/inlined-parameters/

jOOQ 3.9.0 fixes this issue by auto-inlining your bind variables, if you run beyond that limit:

https://github.com/jOOQ/jOOQ/issues/5701

Hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Shyam]

Thank you Lukas for the details.

I moved to v 3.9.0.

--
Shyam

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.