CMS Snapshot Analysis - Run Analyzer - Fatal Error - SQL Error - duplicate key value violates unique constraint idx1 coobject

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

1. CSS Upgrade.
2. Run CUT Migration.
3. 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.

1. 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 

    

2. 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'

3. Execute it to remove the duplicates.

   SELECT CleanObjects()

4. Run the analysis again.

Analysis will fail with the same duplicate key value violation error