We are getting "org.postgresql.util.PSQLException: This connection has been closed." on one of our deployments for only long running transactions (more than a few minutes):

Caused by: org.hibernate.TransactionException: rollback failed
        at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.rollback(AbstractTransactionImpl.java:217)
        at org.springframework.orm.hibernate4.HibernateTransactionManager.doRollback(HibernateTransactionManager.java:604)
        ... 87 more
Caused by: org.hibernate.TransactionException: unable to rollback against JDBC connection
        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doRollback(JdbcTransaction.java:167)
        at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.rollback(AbstractTransactionImpl.java:211)
        ... 88 more
Caused by: org.postgresql.util.PSQLException: This connection has been closed.
        at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:822)
        at org.postgresql.jdbc2.AbstractJdbc2Connection.rollback(AbstractJdbc2Connection.java:839)
        at org.apache.commons.dbcp2.DelegatingConnection.rollback(DelegatingConnection.java:492)
        at org.apache.commons.dbcp2.DelegatingConnection.rollback(DelegatingConnection.java:492)
        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doRollback(JdbcTransaction.java:163)
        ... 89 more

Our stack is as follows:

  • Postgresql 9.2 (on db server Ubuntu 16.03)
  • PgBouncer (on application server Ubuntu 16.03)
  • Jars (on application server Ubuntu 16.03)
    • org.postgresql:postgresql:9.2-1004-jdbc41
    • javax.transaction:jta:1.1
    • org.apache.commons:commons-pool2:2.4.2
    • org.apache.commons:commons-dbcp2:2.1.1'

Postgresql and Pgbouncer use the default parameters and we use the following parameters for dbcp:

database-initial-size = 2
database-max-total = 200
database-validation-query = SELECT 1
database-test-on-borrow = true
database-test-while-idle = true
database-max-wait-millis = 3000
database-time-between-eviction-runs-millis = 34000
database-min-evictable-idle-time-millis = 55000

We have other deployments with same parameters but we are not having the same problem there.

I suspect there is a Firewall/Nat which resets the connection after some timeout but I don't know how to check if this is the case. I would appreciate very much if you can guide me about what logs/parameters/configurations to check which may cause this exception.

I have tested that if Postgresql and PgBouncer are on the same server this problem does not occur. I have also investigated the Postgresql logs and no error messages are logged.

  • how about pgbouncer logs? – Sahap Asci Mar 29 '18 at 11:00
  • About one year later, can you remember if you solved it and how ? Thanks – Benj Jan 15 '20 at 16:08
  • @Benj we ended up adding tcp_keepalives_idle=500 parameter to postgresql.conf and tcp_keepidle = 500 to pgbouncer.ini – nilgun Jan 16 '20 at 23:16
  • @nilgun thanks for the tip, you could make it an answer i would upvote – Benj Jan 17 '20 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.