PostgreSQL and Hibernate java.io.IOException: Tried to send an out-of-range integer as a 2-byte value

Question

I have hibernate query:

getSession()                     
        .createQuery("from Entity where id in :ids") 
        .setParameterList("ids", ids)
        .list();

where ids is Collection ids, that can contain a lot of ids. Currently I got exception when collection is very large: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value I heard that postgre has some problem with it. But I can't find the solution how to rewrite it on hql.

Glenn

have you solved this issue? – Mendelism

Answer 1

The exception occurs because of PostgreSQL's limit of 32767 bind variables per statement (limit raised to 65535 since PostgreSQL driver 42.4.0). As a possible workaround try to split your large query into smaller ones.

Babu

Thanks for the tips, it has saved me in a production application ! Thanks for your science @andrey-dolgikh. – Shaver

related issue on GitHub github.com/pgjdbc/pgjdbc/issues/1311 – Scholium

Answer 2

In newer PostgreSQL driver 42.4.0 you can now pass up to 65,535 records.

fix: queries with up to 65535 (inclusive) parameters are supported now (previous limit was 32767) PR #2525, Issue #1311

Source: https://jdbc.postgresql.org/changelogs/2022-06-09-42.4.0-release/

Blairblaire

Answer 3

If you don't want split your large query into smaller ones, you may use JDBC: ResultSet executeQuery()

Utilitarianism

Answer 4

Try adding parenthesis around :ids, i.e.:

"from Entity where id in (:ids)"

Fremantle

it does not matter. Exception the same – Eldwin

then try executeUpdate(); – Wray

you don't know what saying, Do you know anything about work with db? – Eldwin

i'm just traying to help you :) – Wray

Thanks. I found that it is limits of postgreSQL – Eldwin

great, happy for you :) – Wray

@РоманБондаренко would you mind linking to the documentation explaining those limits? I'm running into the same error – Anthozoan