CMS Snapshot Analysis - Compute Snapshot - Fatal Error - Error while executing procedure - duplicate key value violates unique constraint uidx - efp - objects -statuses - duplicated objects

This page outlines the issue with the running of a snapshot which fails with the following error in the CAST-MS log: ERROR: duplicate key value violates unique constraint "uidx_efp_objects_statuses".

SQL Error: ERROR: duplicate key value violates unique constraint "uidx_efp_objects_statuses".
SQL Error: DETAIL: Key (object_id, module_id, snapshot_id)=(8324633, 103096, 908) already exists..
SQL Error: CONTEXT: 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).
SQL Error: select distinct wkd.OBJECT_ID, .
SQL Error: wkd.MODULE_ID,.
SQL Error: I_SNAPSHOT_ID, .
SQL Error: I_SNAPSHOT_ID_PREVIOUS, .
SQL Error: wkd.OBJECT_STATUS,.
SQL Error: (case when wka.OBJECT_ID is null then -1 else 1 end), /* 1 : object is an artifact, else -1 */.
SQL Error: (case when wka.OBJECT_ID is null then -1 else wka.COST_COMPLEXITY end),.
SQL Error: 0.
SQL Error: from WK_EFP_OBJ_STATUS_LIGHT wkd.
SQL Error: left join WK_EFP_ARTIFACTS wka on ( wka.OBJECT_ID = wkd.OBJECT_ID)".
SQL Error: PL/pgSQL function efp_build_obj_status(integer,integer,integer) line 115 at SQL statement.
SQL Error: SQL statement "SELECT EFP_BUILD_OBJ_STATUS(I_SNAPSHOT_ID, I_SNAPSHOT_ID_START, I_APP_ID)".
SQL Error: PL/pgSQL function efp_build_art_cpt_statuses_sys(integer) line 47 at PERFORM.
SQL Error: SQL statement "select EFP_BUILD_ART_CPT_STATUSES_SYS (908)".
SQL Error: PL/pgSQL function dss_execute_named_side_process(integer,character varying,integer) line 64 at EXECUTE statement.
SQL Error: PL/pgSQL function dss_execute_full_side_process(character varying,integer) line 20 at assignment (Severity 1, Msg No 1).

For locating CAST MS log, see:  CAST Management Studio - Information - How to find logs

1. Run Snapshot.
2. Snapshot fails with the mentioned error.

There are several situations that can result in this error.

The issue is raised due to a corruption where some objects are present in the metric result of a snapshot, but not explicitly present in the snapshot which is not expected.

There are two known cases that can cause this corruption.  Run the query checks for both cases, and the appropriate remediation if this corruption is present in the central databases. 

After resolving these issues with the corruption, then proceed forward by rerunning the snapshot.

Case 1:  Duplicated objects in snapshots

Case 2:  Missing objects in snapshots

Duplicated Objects in Snapshots

1. Run the below query on the central database to see if there are objects duplicated in snapshots:

   select ds.SNAPSHOT_ID as "Snapshot ID",
          ds.SNAPSHOT_NAME as "Snapshot Name",
          ds.FUNCTIONAL_DATE as "Snapshot Date",
          t.IdTyp as "Type ID",
          t.TypNam as "Type Name",
          v.DUPLICATES_NB as "Duplicates Nb"
     from (select u.SNAPSHOT_ID,
                  u.OBJECT_TYPE_ID,
                  count(1) as DUPLICATES_NB
             from (select doi.SNAPSHOT_ID,
                          doi.OBJECT_ID,
                          doi.OBJECT_TYPE_ID
                     from DSS_OBJECT_INFO doi
                    group by doi.SNAPSHOT_ID, doi.OBJECT_ID, doi.OBJECT_TYPE_ID
                   having count(1) > 1
                  ) u
            group by u.SNAPSHOT_ID, u.OBJECT_TYPE_ID
          ) v
     join DSS_SNAPSHOTS ds
       on ds.SNAPSHOT_ID = v.SNAPSHOT_ID
     join Typ t
       on t.IdTyp = v.OBJECT_TYPE_ID
    order by ds.FUNCTIONAL_DATE, t.TypNam;

2. If any rows are returned (even if the Duplicates Nb is 1), then you have duplicates and should proceed with the cleanup steps shown below.  The results will look like the results below: 

   Query result example

    1;"V1";"2016-02-11 00:00:00";102;"JV_METHOD";1

3. If rows are returned, then remove duplicated objects with the following script run on the central database:

   insert into DSS_OBJECT_INFO (SNAPSHOT_ID, OBJECT_ID, OBJECT_TYPE_ID, OBJECT_CHECKSUM)
        select doi.SNAPSHOT_ID,
               - doi.OBJECT_ID,
               doi.OBJECT_TYPE_ID,
               min(doi.OBJECT_CHECKSUM) as CHECKSUM
          from DSS_OBJECT_INFO doi
         group by doi.SNAPSHOT_ID, doi.OBJECT_ID, doi.OBJECT_TYPE_ID
        having count(1) > 1;
    
   delete from DSS_OBJECT_INFO
    where (SNAPSHOT_ID, OBJECT_ID) in (select doi.SNAPSHOT_ID,
                                            - doi.OBJECT_ID
                                         from DSS_OBJECT_INFO doi
                                        where doi.OBJECT_ID < 0
                                      );
    
   update DSS_OBJECT_INFO
      set OBJECT_ID = - OBJECT_ID
    where OBJECT_ID < 0;

 Missing Objects in Snapshots

1. Run the query below on the central database to see if there are objects missing in the snapshots:

   select ds.SNAPSHOT_ID as "Snapshot ID",
          ds.SNAPSHOT_NAME as "Snapshot Name",
          ds.FUNCTIONAL_DATE as "Snapshot Date",
          t.IdTyp as "Type ID",
          t.TypNam as "Type Name",
          v.MISSING_OBJECTS_NB as "Missing Objects Nb"
     from (select dmr.SNAPSHOT_ID,
                  dos.OBJECT_TYPE_ID,
                  count(1) as MISSING_OBJECTS_NB
             from (select dmr.SNAPSHOT_ID,
                          dmr.OBJECT_ID
                     from DSS_METRIC_RESULTS dmr
                     left join DSS_OBJECT_INFO doi
                       on doi.SNAPSHOT_ID = dmr.SNAPSHOT_ID
                      and doi.OBJECT_ID = dmr.OBJECT_ID
                    where dmr.METRIC_ID not in (10320, 10321, 10322)
                      and doi.OBJECT_ID is null
                  ) dmr
             join DSS_OBJECTS dos
               on dos.OBJECT_ID = dmr.OBJECT_ID
            group by dmr.SNAPSHOT_ID, dos.OBJECT_TYPE_ID
          ) v
     join DSS_SNAPSHOTS ds
       on ds.SNAPSHOT_ID = v.SNAPSHOT_ID
     join Typ t
       on t.IdTyp = v.OBJECT_TYPE_ID
    order by ds.FUNCTIONAL_DATE, t.TypNam;

2. If any rows are returned (even if the Missing Objects No is 1), then you have missing objects and should proceed with the cleanup steps as shown below.  The results will look like the results below:

   Query result example

   1;"V1";"2016-02-11 00:00:00";100;"JV_CLASS";1

3. Missing objects can be added back into the snapshot by the following script on the central database:

   insert into DSS_OBJECT_INFO (SNAPSHOT_ID, OBJECT_ID, OBJECT_TYPE_ID, OBJECT_CHECKSUM)
        select distinct dmr.SNAPSHOT_ID, dmr.OBJECT_ID, dos.OBJECT_TYPE_ID, 0
          from (select dmr.SNAPSHOT_ID,
                       dmr.OBJECT_ID
                  from DSS_METRIC_RESULTS dmr
                  left join DSS_OBJECT_INFO doi
                    on doi.SNAPSHOT_ID = dmr.SNAPSHOT_ID
                   and doi.OBJECT_ID = dmr.OBJECT_ID
                 where dmr.METRIC_ID not in (10320, 10321, 10322)
                   and doi.OBJECT_ID is null
               ) dmr
          join DSS_OBJECTS dos
            on dos.OBJECT_ID = dmr.OBJECT_ID;

   
   
   

NA

Ticket # 7043

For other errors during the compute snapshot step, 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 datafunctions

Compute snapshot error for this issue due to duplicated datafunctions:  CMS Snapshot Analysis - Compute Snapshot - Fatal Error - Error while executing procedure - duplicate key value violates unique constraint uidx_efp_objects_statuses - duplicated datafunctions