CMS Snapshot Analysis - Compute Snapshot - Fatal Error - Error while executing procedure - duplicate key value violates unique constraint uidx_efp_objects_statuses - duplicated datafunctions

Description

This page explains how to resolve the below error message that occurs during the Snapshot:

And In CAST MS log file (how to locate it is explained in the CAST Management Studio - Information - How to find logs page)

ERROR: duplicate key value violates unique constraint "uidx_efp_objects_statuses"
Detail: Key (object_id, module_id, snapshot_id)=(2331402, 52, 13) already exists.
Where: SQL statement "insert into EFP_OBJECTS_STATUSES(OBJECT_ID, MODULE_ID, SNAPSHOT_ID, PREVIOUS_SNAPSHOT_ID, OBJECT_STATUS, IS_ART, COST_COMPLEXITY, TECHNO_TYPE_ID)
select distinct wkd.OBJECT_ID,
wkd.MODULE_ID,
I_SNAPSHOT_ID,
I_SNAPSHOT_ID_PREVIOUS,
wkd.OBJECT_STATUS,
(case when wka.OBJECT_ID is null then -1 else 1 end), /* 1 : object is an artifact, else -1 */
(case when wka.OBJECT_ID is null then -1 else wka.COST_COMPLEXITY end),
0
from WK_EFP_OBJ_STATUS_LIGHT wkd
left join WK_EFP_ARTIFACTS wka
on wka.OBJECT_ID = wkd.OBJECT_ID"
PL/pgSQL function efp_build_obj_status(integer,integer,integer) line 112 at SQL statement
SQL statement "SELECT EFP_BUILD_OBJ_STATUS(I_SNAPSHOT_ID, I_SNAPSHOT_ID_START, I_APP_ID)"
PL/pgSQL function efp_build_art_cpt_statuses_sys(integer) line 54 at PERFORM
SQL statement "select EFP_BUILD_ART_CPT_STATUSES_SYS (13)"
PL/pgSQL function dss_execute_named_side_process(integer,character varying,integer) line 64 at EXECUTE statement
SQL statement "select DSS_EXECUTE_NAMED_SIDE_PROCESS(I_SNAPSHOT_ID, 'ALL', I_SIDE)"
PL/pgSQL function dss_execute_side_process(integer,integer) line 5 at SQL statement
PL/pgSQL function dss_cleanup_compute_metric(integer) line 6 at assignment
org.postgresql.util.PSQLException:ERROR: duplicate key value violates unique constraint "uidx_efp_objects_statuses"
Detail: Key (object_id, module_id, snapshot_id)=(2331402, 52, 13) already exists.

Observed in CAST AIP

Release	Yes/No
8.3.x
8.2.x
8.1.x
8.0.x
7.3.x
7.2.x
7.0.x

Observed on RDBMS

RDBMS	Yes/No
Oracle Server
Microsoft SQL Server
CSS3
CSS2
CSS1

Step by Step Scenario

Run a new Snapshot (old snapshots are already registered in the Central database)

Action Plan

There are several issues which can cause this error. For this error caused by duplicate objects, see the following page: CMS Snapshot Analysis - Compute Snapshot - Fatal Error - Error while executing procedure - duplicate key value violates unique constraint uidx - efp - objects -statuses - duplicated objects

The issue occurs when many snapshot have been executed and if Datafunction are duplicated :

Check in the local database if Datafunctions are duplicated:

set search_path = <Your local database>;
 
select *
  from DSS_DataFunction dd  
 where MainTable_Id in (select MainTable_Id
                          from DSS_DataFunction dd  
                         group by MainTable_Id
                        having count(1) > 1
                       )
 order by MainTable_Id, Object_Id

If rows are raised go to next step, else contact CAST Technical Support.

Delete in the local database the duplicated datafunction

set search_path = <Your local database>;
 
delete from DSS_DataFunction 
 where MainTable_Id in (select MainTable_Id
                          from DSS_DataFunction dd  
                         group by MainTable_Id
                        having count(1) > 1
                       )
  and cal_flags & 1 = 0;

In the Central database, run the following statement to create a temporary RECONSOLIDATE_TRANSACTIONS function and remove duplicated rows :

set search_path = <Your central database>;
 CREATE OR REPLACE FUNCTION RECONSOLIDATE_TRANSACTIONS(I_SNAPSHOT_ID INTEGER )
RETURNS INTEGER AS
$body$
DECLARE
 ERRORCODE INTEGER := 0;
Begin
 select ADG_CENTRAL_STARTUP_INIT (I_SNAPSHOT_ID, -1, -1, 60001) into ERRORCODE;
 -- Reconsolidate IFPUG metrics
 delete from DSS_METRIC_RESULTS
 where SNAPSHOT_ID = I_SNAPSHOT_ID
 and METRIC_ID in (10202, 10203, 10204)
 and OBJECT_ID in ( select O.OBJECT_ID
 from DSS_OBJECT_TYPES T, DSS_OBJECTS O 
 where O.OBJECT_TYPE_ID = T.OBJECT_TYPE_ID 
 and T.OBJECT_GROUP >= 2);
 select APM_CENTRAL_RESULT_SUM_D (I_SNAPSHOT_ID, 10202, 10203, 1) into ERRORCODE;
 select APM_CENTRAL_RESULT_SUM_D (I_SNAPSHOT_ID, 10202, 10204, 1) into ERRORCODE;
 select APM_CENTRAL_RESULT_SUM_RES (I_SNAPSHOT_ID, 60004, 10202, 1) into ERRORCODE;
 
 -- Rebuild deltas
 select APMCFP_RESULT_ADDED_DATAF (I_SNAPSHOT_ID, 10300, 10301, 1) into ERRORCODE;
 select APMCFP_RESULT_ADDED_TRAN (I_SNAPSHOT_ID, 10300, 10302, 1) into ERRORCODE;
 select APM_CENTRAL_RESULT_SUM_FP (I_SNAPSHOT_ID, 10250, 10300, 1) into ERRORCODE;
 select APMCFP_RESULT_UPDATED_DATAF (I_SNAPSHOT_ID, 10310, 10311, 1) into ERRORCODE;
 select APMCFP_RESULT_UPDATED_TRAN (I_SNAPSHOT_ID, 10310, 10312, 1) into ERRORCODE;
 select APM_CENTRAL_RESULT_SUM_FP (I_SNAPSHOT_ID, 10250, 10310, 1) into ERRORCODE;
 select APMCFP_RESULT_DELETED_DATAF (I_SNAPSHOT_ID, 10320, 10321, 1) into ERRORCODE;
 select APMCFP_RESULT_DELETED_TRAN (I_SNAPSHOT_ID, 10320, 10322, 1) into ERRORCODE;
 select APM_CENTRAL_RESULT_SUM_FP (I_SNAPSHOT_ID, 10250, 10320, 1) into ERRORCODE;
 select APMCFP_RESULT_UNCHANGED_DATAF (I_SNAPSHOT_ID, 10330, 10331, 1) into ERRORCODE;
 select APMCFP_RESULT_UNCHANGED_TRAN (I_SNAPSHOT_ID, 10330, 10332, 1) into ERRORCODE;
 select APM_CENTRAL_RESULT_SUM_FP (I_SNAPSHOT_ID, 10250, 10330, 1) into ERRORCODE;
 return ERRORCODE; 
End;
$body$
language 'plpgsql';-- remove duplicated rows
insert into DSS_OBJECT_INFO (SNAPSHOT_ID, OBJECT_ID, OBJECT_TYPE_ID, OBJECT_CHECKSUM)
select SNAPSHOT_ID, -OBJECT_ID, OBJECT_TYPE_ID, min(OBJECT_CHECKSUM)
 from DSS_OBJECT_INFO 
group by SNAPSHOT_ID, OBJECT_ID, OBJECT_TYPE_ID;
 
delete from DSS_OBJECT_INFO 
where OBJECT_ID > 0;
update DSS_OBJECT_INFO
set OBJECT_ID = -OBJECT_ID;

Then, for each snapshot id, you have to run the transaction consolidation :

To get all the snapshot id, run the following query

set search_path = <Your central database>;
select snapshot_id from c735_4060_sko.dss_snapshots
where snapshot_status = 2
 
 
For instance, the result is as follow :
snapshot_id
6
7
8

Run the RECONSOLIDATE_TRANSACTIONS function for each snapshot id as below:

set search_path = <Your central database>;
select RECONSOLIDATE_TRANSACTIONS (snapshot id);
 
for instance, according to the previous result:
select RECONSOLIDATE_TRANSACTIONS (6);
select RECONSOLIDATE_TRANSACTIONS (7);
select RECONSOLIDATE_TRANSACTIONS (8);

Run the efp_build_obj_status function for each snapshot id as below :

 
select efp_build_obj_status(snapshot_id, previous_snapshot_id, 3);
 
for instance, according to the previous result:
 
select efp_build_obj_status(6, -1, 3);
select efp_build_obj_status(7, 6, 3);
select efp_build_obj_status(8, 7, 3);

Drop the RECONSOLIDATE_TRANSACTIONS temporary function

  
 drop function  RECONSOLIDATE_TRANSACTIONS(I_SNAPSHOT_ID		INTEGER );

Impact on Analysis Results and Dashboard

Impact of issue: Snapshot fails
Impact after applying solution: Snapshot goes to the end.

Notes/comments