JWP Traces on Postgresql during MetricsCleanupTask - violates foreign key constraint fk_pmmav_pmma on table pmmav

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

On AE 24.x with Postgresql as Database, the JWP generates force traces daily while performing the purge of the performance metrics with the query "DELETE FROM PMMA WHERE PMMA_Timestamp < ?"

The kind of errors found in the JWP log are the following:

20240604/123008.026 - 38     U00003525 UCUDB: ===> 'DELETE FROM PMMA WHERE PMMA_Timestamp < ?'
20240604/123008.026 - 38     U00003434 Server routine  'MetricsCleanupTask/TIMER' required '0' minutes and '20' seconds for processing.
20240604/123008.026 - 38     U00003590 UCUDB - DB error: '23503', 'ERROR: update or delete on table "pmma" violates foreign key constraint "fk_pmmav_pmma" on table "pmmav"
20240604/123008.026 - 38       Detail: Key (pmma_idnr)=(122025376) is still referenced from table "pmmav".', '0', 'org.postgresql.util.PSQLException'
20240604/123008.027 - 38               SQL Statement which caused this DB error:
20240604/123008.027 - 38     U02012075 Set bind parameter '1' of type 'Timestamp' to value '2024-05-05 12:29:48.009'
20240604/123008.027 - 38               DELETE FROM PMMA WHERE PMMA_Timestamp < ?
20240604/123008.027 - 38     U00045014 Exception 'com.automic.database.api.DBException: "DELETE FROM PMMA WHERE PMMA_Timestamp < ?"' at 'com.automic.database.impl.DBConnectionImpl.executeInternal():562'.
20240604/123008.028 - 38     U00045015 The previous error was caused by 'org.postgresql.util.PSQLException: "ERROR: update or delete on table "pmma" violates foreign key constraint "fk_pmmav_pmma" on table "pmmav"
20240604/123008.028 - 38       Detail: Key (pmma_idnr)=(122025376) is still referenced from table "pmmav"."' at 'org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse():2676'.
20240604/123008.028 - 38     U00003620 Routine 'com.automic.kernel.impl.DBAction' forces trace because of error.

Environment

Automation Engine 24.x with Posgresql as Database

Cause

Defect (on Postgresql the PMMAV_Timestamp was one hour off from the PMMA_Timestamp).

Resolution

Update to a fix version listed below or a newer version if available.

Fix version:
Component(s): Automation Engine
Automation.Engine 24.1.0 HF1 - Available
Automation.Engine 24.2.0 - Available

Additional Information

Defect ID: AE-37404

Defect Title: MetricsCleanupTask/TIMER: update or delete on table pmma violates foreign key constraint fk_pmmav_pmma on table pmmav