Auto Approve Timesheets Job leaving idle in transaction with PostgreSQL database

Products

• Clarity PPM SaaS
• Clarity FedRAMP
• Clarity PPM On Premise

Issue/Introduction

Auto Approve / Auto Submit Timesheets job fails with Invalid Time Period when configured with a closed time period, and leaves a PostgreSQL connection in Idle in Transaction state after the failure.

Steps to Reproduce:

1. Login to clarity with necessary rights
2. Navigate to Reports and Home --> Run the Job Auto Approve Timesheets Job with closed time period

Expected Results: The Job Auto Approve Timesheets Job should complete and connection should be released

Actual Results: The Job Auto Approve Timesheets Job is leaving  idle in transaction database connection

Environment

Clarity 16.4.0, 16.4.1 

Cause

DE184252

Resolution

Fixed in

• Clarity 16.4.2 

Additional Information

Diagnostic SQL: Use the following queries on a running Clarity environment to identify active connection leaks

• Oracle — Idle sessions older than 30 minutes

  SELECT s.SID, s.SERIAL#, s.USERNAME, s.STATUS, s.LAST_CALL_ET,
         s.PROGRAM, s.MODULE, s.ACTION, s.SQL_ID
  FROM V$SESSION s
  WHERE s.USERNAME = UPPER('&clarity_schema')
    AND s.LAST_CALL_ET > 1800
    AND s.STATUS = 'INACTIVE'
  ORDER BY s.LAST_CALL_ET DESC;

• PostgreSQL — Long-running idle connections 

  SELECT pid, usename, application_name, state,
         now() - state_change AS idle_duration, query
  FROM pg_stat_activity
  WHERE state = 'idle in transaction'
    AND now() - state_change > interval '30 minutes'
  ORDER BY idle_duration DESC;

• MS SQL Server — Sessions idle in transaction

  SELECT s.session_id, s.login_name, s.status, s.program_name,
         s.login_time, s.last_request_start_time,
         r.open_transaction_count,
         DATEDIFF(MINUTE, s.last_request_start_time, GETDATE()) AS idle_minutes
  FROM sys.dm_exec_sessions s
  LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
  WHERE s.is_user_process = 1
    AND s.status = 'sleeping'
    AND r.open_transaction_count > 0
  ORDER BY idle_minutes DESC;