1

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.

nilgun
  • 10,460
  • 4
  • 46
  • 57
  • Could you show the connection url ? Are there any additional parameters passed to the driver when creating connections? In particular, whether [socketTimeout parameter](https://jdbc.postgresql.org/documentation/head/connect.html#connection-parameters) is set or left unchanged ? – krokodilko Mar 29 '18 at 07:53
  • Hi krokodilko, no paremeters are set in connection url except host, port and user. – nilgun Mar 29 '18 at 09:00
  • @nilgun Did you get a resolution to this error? – Debi Dec 31 '19 at 21:48
  • 1
    @Debi we ended up adding tcp_keepalives_idle=500 parameter to postgresql.conf and tcp_keepidle = 500 to pgbouncer.ini – nilgun Jan 03 '20 at 00:26

0 Answers0