This page outlines the solution for the below error message that occurs while upgrading the Central base using Server Manager:
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).
The issue is raised due to a corruption where by some objects are present in a metric result of a snapshot, but not explicitly present in the snapshot which is not expected.
Release | Yes/No |
---|---|
8.3.x |
RDBMS | Yes/No |
---|---|
CSS |
- Run CSS Upgrade
- Run CUT Central base fails with the mentioned error.
The solution is to remove the corruption on the central base before starting the migration. - Identify the central base on the CSS1 server (before migration) - Run the below scripts on the central base:
Query to see if some objects are 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;
If any rows are returned (even if the Duplicates Nb is 1), then you have duplicates and should proceed with the cleanup steps below. The results would look like the results below:
1;"V1";"2016-02-11 00:00:00";102;"JV_METHOD";1
Remove duplicated objects with the following script:
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;
Query to see if some objects are missing 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.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;
If any rows are returned (even if the Missing Objects Nb is 1), then you have missing objects and should proceed with the cleanup steps below. The results would look like the results below:
1;"V1";"2016-02-11 00:00:00";100;"JV_CLASS";1
Missing objects can be added back into the snapshot by the following script:
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;
Start with the migration process once again.
NA
Ticket # 4611
Compute snapshot for this error due to duplicated objects: CMS Snapshot Analysis - Compute Snapshot - Fatal Error - Error while executing procedure - duplicate key value violates unique constraint uidx - efp - objects -statuses - duplicated objects
Compute snapshot for this error 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