This page will help you to solve the issue related to analysis failiure during the Saving step at the Comparing objects on server with the following SQL Error message:
Comparing objects on server...
SQL Error: ERROR: duplicate key value violates unique constraint "idx1_coobject".
SQL Error: DETAIL: Key (object_id)=(63261) already exists..
SQL Error: CONTEXT: SQL statement "insert into AMT_OBJECT (OBJECT_ID, NAME_ID, SHORT_NAME_ID, OBJECT_TYPE_ID, INTERNAL_ID, STATUS).
SQL Error: select OBJECT_ID, NAME_ID, SHORT_NAME_ID, OBJECT_TYPE_ID, .
SQL Error: coalesce(o.IdKey, nextval('IDKEY_GENERATOR') ),.
SQL Error: case when o.IdKey is null then 'I'.
SQL Error: else 'U'.
SQL Error: end.
SQL Error: from IN_OBJECTS left join Objects o.
SQL Error: on o.IdShortNam = IN_OBJECTS.SHORT_NAME_ID .
SQL Error: and o.IdNam = IN_OBJECTS.NAME_ID .
SQL Error: and o.ObjTyp = IN_OBJECTS.OBJECT_TYPE_ID.
SQL Error: where SESSION_ID = I_IDSESSION".
SQL Error: PL/pgSQL function amt_p_fill_obj(integer) line 20 at SQL statement.
SQL Error: SQL statement "select AMT_P_FILL_OBJ (I_IDSESSION)".
SQL Error: PL/pgSQL function amt_p_split_fill(integer) line 12 at SQL statement.
SQL Error: SQL statement "select AMT_P_SPLIT_FILL (I_IDSESSION)".
SQL Error: PL/pgSQL function amt_p_split_in(integer) line 15 at SQL statement.
SQL Error: SQL statement "select AMT_P_SPLIT_IN (I_IDSESSION)".
SQL Error: PL/pgSQL function cache_processid(integer,integer) line 14 at SQL statement (Severity 1, Msg No 1).
Procedure call failed: ?test_app_test_local.CACHE_PROCESSID,I_IDSESSION,I_IDUSRPRO
Release | Yes/No |
---|---|
8.3.x | |
8.2.x | |
8.1.x | |
8.0.x | |
7.3.x |
RDBMS | Yes/No |
---|---|
Oracle Server | |
Microsoft SQL Server | |
CSS3 | |
CSS2 | |
CSS1 |
- CSS Upgrade.
- Run CUT Migration.
- Knowledge base fails with the SQL error during migration.
The issue is due to duplication of the objects name and objects GUID in the table OBJECTS, in the local database.
Run the below query on the local database to identify the wrong objects
SELECT o.idkey, o.idnam, o.objtyp, t.typnam, t.typdsc FROM objects o JOIN (SELECT idnam, idshortnam FROM objects o GROUP BY idnam, idshortnam HAVING Count(1) > 1) od ON od.idnam = o.idnam AND od.idshortnam = o.idshortnam JOIN objpro op ON op.idobj = o.idkey JOIN anapro ap ON ap.idpro = op.idpro JOIN typ t ON t.idtyp = o.objtyp ORDER BY o.idnam
Install the following procedure on the KB
CREATE OR replace FUNCTION cleanobjects() returns int AS $body$DECLARE l_errorcode INT;BEGIN l_errorcode := 0; select droptemporarytable('WK_ObjToKeep') INTO l_errorcode; create local temporary TABLE wk_objtokeep (idobj int); insert INTO wk_objtokeep ( idobj ) SELECT max(o.idkey) FROM objects o JOIN ( SELECT idnam, idshortnam FROM objects GROUP BY idnam, idshortnam HAVING count(1) > 1 ) od ON od.idnam = o.idnam AND od.idshortnam = o.idshortnam JOIN objpro op ON op.idobj = o.idkey JOIN anapro ap ON ap.idpro = op.idpro; select droptemporarytable('WK_ObjToDelete') INTO l_errorcode; create local temporary TABLE wk_objtodelete (idobj int NOT NULL); insert INTO wk_objtodelete ( idobj ) SELECT o.idkey FROM objects o JOIN ( SELECT idnam, idshortnam FROM objects GROUP BY idnam, idshortnam HAVING count(1) > 1 ) od ON od.idnam = o.idnam AND od.idshortnam = o.idshortnam LEFT JOIN wk_objtokeep otk ON otk.idobj = o.idkey WHERE otk.idobj IS NULL; select int_object_del() INTO l_errorcode; delete FROM objects using wk_objtodelete wo WHERE objects.idkey = wo.idobj; drop TABLE wk_objtodelete; drop TABLE wk_objtokeep; return l_errorcode ; end;$body$ language 'plpgsql'
Execute it to remove the duplicates.
SELECT CleanObjects()
Run the analysis again.
Analysis will fail with the same duplicate key value violation error