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.

score 61 · Answer 1 · answered Sep 10 '18 at 09:52

61

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

answered Sep 10 '18 at 09:52

Andrey Dolgikh

861
8
7

1

Thanks for the tips, it has saved me in a production application ! Thanks for your science @andrey-dolgikh. – cdescours Sep 30 '20 at 14:21
related issue on GitHub https://github.com/pgjdbc/pgjdbc/issues/1311 – Dmitry.M Nov 16 '21 at 07:38

score 0 · Answer 2 · answered Aug 03 '23 at 21:08

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/

score -2 · Answer 3 · answered Jun 09 '22 at 12:45

-2

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

answered Jun 09 '22 at 12:45

Stan

1
1

score -6 · Answer 4 · edited Mar 14 '18 at 10:23

-6

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

"from Entity where id in (:ids)"

edited Mar 14 '18 at 10:23

Pang

9,564
146
81
122

answered Mar 14 '18 at 10:20

Fayçal Foura

1
1

it does not matter. Exception the same – Роман Бондаренко Mar 14 '18 at 10:25
then try executeUpdate(); – Fayçal Foura Mar 14 '18 at 12:12
you don't know what saying, Do you know anything about work with db? – Роман Бондаренко Mar 14 '18 at 14:04
i'm just traying to help you :) – Fayçal Foura Mar 14 '18 at 14:51
1

Thanks. I found that it is limits of postgreSQL – Роман Бондаренко Mar 15 '18 at 10:34
great, happy for you :) – Fayçal Foura Mar 15 '18 at 14:22
1

@РоманБондаренко would you mind linking to the documentation explaining those limits? I'm running into the same error – irregular Apr 05 '18 at 14:48

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

4 Answers4