PostgreSQL Connection transaction remains open

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

Have a problem about PostgreSQL connection. When connecting and generate/mask data on PostgreSQL database from Portal, the connection transaction remains open. It causes performance problem(high cpu usage) on database. After the masking/generation process done, this transaction must be closed automatically. Not having this problem on DB2 or MsSQL.

Environment

All supported releases of TDM.

PostgreSQL database

Cause

N/A

Resolution

By design, TDM uses connection pooling with the following entry in application.properties (located at <TDM_HOM>/conf directory). This connection pool is to improve the application performance. 

As explained in a PostgreSQL forum, these idle connections will not have impact on Memory and CPU. If you want to reduce these the connections, modify below properties in application.properties file and restart the TDM portal service.

 

# Connection pool properties (per service) for the tomcat connection pool manager

spring.datasource.tomcat.initial-size=5

spring.datasource.tomcat.min-idle=5

spring.datasource.tomcat.max-idle=5

spring.datasource.tomcat.max-active=20

spring.datasource.tomcat.max-wait=30000