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

This page will help you to troubleshoot the Fatal Error message "ERROR: duplicate key value violates unique constraint "<INDEX NAME>" which you may face while running the Snapshot.

1. In the CAST-MS log file, identify the table or the view that is involved in the error message.
   The Index name follows the error : "ERROR: duplicate key value violates unique constraint "<INDEX NAME>" ".
   The Table name is in the UPDATE or INSERT STATEMENT
   
   In the above example the table name is: DS_KeysExtra.
2. Check if there are indexes that have been dropped in the local and central. To do so compare the count of indexes between the used local and central schema with a fresh local and central schema, for more information refer to SQL Queries - Common SQL Queries - How to retrieve the count of indexes used for a schema In the above example the Unique index "idx_dss_keysextra" was dropped from the table "dss_keysextra", which led to the insertion of duplicated values hence the execution failed.
   
3. If there are indexes that have been dropped:

1. Delete Duplicated values from the table mentioned in the error message

   DELETE FROM dss_keysExtra
   WHERE  ctid NOT IN (
      SELECT min(ctid)                    
      FROM   <TABLE>
      GROUP  BY object_id); 

   Replace <TABLE> by the name of the table mentioned in the error message

   In our example the query is the following:

   DELETE FROM dss_keysExtra
   WHERE  ctid NOT IN (
      SELECT min(ctid)                    
      FROM   dss_keysextra
      GROUP  BY object_id); 

    

2. Run a component reinstall using the Server Manager Tool. 
3. Compute snapshot by skipping analysis.
4. If the component reinstall does not solve the issue, navingate to the below pages:
   
   • CMS Snapshot Analysis - Compute Snapshot - Fatal Error - Error while executing procedure - duplicate key value violates unique constraint dss metr typ pk
   • CMS Snapshot Analysis - Compute Snapshot - Fatal Error - Error while executing procedure - duplicate key value violates unique constraint uidx_efp_objects_statuses - duplicated datafunctions
   • CMS Snapshot Analysis - Compute Snapshot - Fatal Error - Error while executing procedure - duplicate key value violates unique constraint uidx - efp - objects -statuses - duplicated objects
    
5. If the above points do not help you, contact Cast Technical Support and provide the Relevant Input in order to reproduce the issue.