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)
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.