CODEWITHSUNDEEP
javapostgresql
Nicu
Nicu

Reputation: 43

Connection to Postgres database lost

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 ?

Upvotes: 1

Views: 2294

Answers (1)

Stelian Niculescu
Stelian Niculescu

Reputation: 86

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.

Upvotes: 2

Related Questions