I have an issue with database connection pooling. I am using Wildfly version 29, hibernate with Postgres DB.

There are a lot of transactions happening in my Java application.
All these DB connections that were created can be seen in the pg_stat_activity view from Postgres DB. what we have identified is that it takes some default time
to remove/clean these used connections even if they are marked status as “Idle” and query as COMMIT. I configure this to set an Idle-timeout-minute in the Widfly configuration and these connections are cleaning up properly.

There can be multiple transaction rollbacks in the system. Once that kind of rollback happens this pg_stat_activity view marks them as ROLLBACK in the query column.

Unfortunately, it removes only some of the connections for this table marked as ROLLBACK. Some of them are still in the connection pool in “Idle” status with ROLLBACK record in pg_stat_activity view.

What could be the cause for this and is there any specific configuration that I can use
to instruct Wildfly to remove all the connections that are “Idle” no matter if the query is COMMIT or in the ROLLBACK stage.

I have tried a few other settings as well like below and everything led to the same results.

<xa-pool>
    <min-pool-size>1</min-pool-size>
    <max-pool-size>20</max-pool-size>
    <flush-strategy>FailingConnectionOnly</flush-strategy>
</xa-pool>
<validation>
    <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
    <background-validation>true</background-validation>
    <background-validation-millis>60000</background-validation-millis>
    <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>
</validation>
<timeout>
    <idle-timeout-minutes>1</idle-timeout-minutes>
</timeout>