Connection to Postgres database lost

Question

On a performance server - with rather a big load, i have a weird behavior. From one moment in time all the connection the database start to say "connection has been closed".

The only hint so far is this IOException :

Caused by: org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:314)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:430)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:356)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:168)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:116)
    at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1812)
    at org.hibernate.loader.Loader.doQuery(Loader.java:697)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
    at org.hibernate.loader.Loader.doList(Loader.java:2232)
    ... 73 more

Caused by: java.io.IOException: Tried to send an out-of-range integer as a 2-byte value: 33001
    at org.postgresql.core.PGStream.sendInteger2(PGStream.java:211)
    at org.postgresql.core.v3.QueryExecutorImpl.sendParse(QueryExecutorImpl.java:1409)
    at org.postgresql.core.v3.QueryExecutorImpl.sendOneQuery(QueryExecutorImpl.java:1729)
    at org.postgresql.core.v3.QueryExecutorImpl.sendQuery(QueryExecutorImpl.java:1294)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:280)
    ... 83 more

However i can't really link it to some business scenario for the moment.

Any ideas ?

Maybe max_connections needs to be raised?

kometen
– kometen

2018-03-21 09:54:46 +00:00
Commented Mar 21, 2018 at 9:54 — kometen
– kometen, Commented Mar 21, 2018 at 9:54

Stelian Niculescu · Accepted Answer · 2018-03-21 13:57:15Z

2

It's a PostgreSQL driver limitation, the maximum number of parameters for a query is 32768.

You have a query that exceeds that limit - and by doing so the driver has an erratic behavior of closing connections. I encountered that on a JBoss server using Hibernate with PostgreSQL and the connection closing led to a pretty messed up state of the connection pool.

This parameter is described here, in the Parse section: "Int16 - The number of parameter data types specified".

The solution is to split that long query into smaller ones with a known number of parameters.

answered Mar 21, 2018 at 13:57

Stelian Niculescu

861 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nicu Over a year ago

Good call - i reproduced the issue in a JUnit - and indeed we do have some nasty queries using a "bit" to much the IN clause.

Collectives™ on Stack Overflow

Connection to Postgres database lost

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related