Duplicate Key Value violates unique constraints reported in the transformer logs

Products

• VMware Smart Assurance

Issue/Introduction

Customer migrated the Network Configuration Manager (NCM) database a few months ago.  They noted errors with a number of duplicate key values 

ERROR: 2024-06-20T07:36:23,562: DCSDiffDataTransformer: error fetching/inserting changed dcs : org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "cm_rpt_dcs_diff_pkey"
  Detail: Key (revision_id)=(\012F\214.\266T\317?\360\251\001\311e\001\000\000) already exists.

Environment

NCM 10.1.x 

Cause

During the database migration it appears that keys already existed and subsequent attempts to INSERT the information from the transformer service generated these errors. 

Resolution

Review the $VOYENCE_HOME/Transformation/logs/transformer.log Isolate the revision IDs that are generating the duplicate key errors.

Log on to the NCM database. 

Run the first query below:

SELECT count(*) FROM cm_device_revisionable_state LEFT JOIN cm_rpt_dcs_diff  ON (cm_device_revisionable_state.device_state_id = cm_rpt_dcs_diff.device_state_id) WHERE  cm_rpt_dcs_diff.device_state_id IS NULL AND  cm_device_revisionable_state.state_number > 1;

Review the output.

 

The next steps will require the revision ID(s) found in the $VOYENCE_HOME/Transformation/logs/transformer.log

select * from cm_rpt_dcs_diff where revision_id='<revision ID from log';

This query may return more than one output. Proceed with deletion. The transformer service will again insert the record. 

delete from cm_rpt_dcs_diff where revision_id='<revision ID from log';

Example below:

select * from cm_rpt_dcs_diff where revision_id='\\012F\\214\.\\012\\212\\342\?\+\\252\\2027g\\001\\000\\000';

This query may return more than one output. Proceed with deletion. The transformer service will again insert the record. 

delete from cm_rpt_dcs_diff where revision_id='\\012F\\214\.\\012\\212\\342\?\+\\252\\2027g\\001\\000\\000';

You can execute for other revision IDs similarly.