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

This page will help you to find a solution if your snapshot is failing and the below-mentioned error is found in the Cast-MSx.log.txt file.

duplicate key value violates unique constraint dss_metr_typ_pk

This error is encountered when the primary key constraint is removed from the table dss_metric_types in the central base and duplicate metric IDs are introduced which can be seen by running the below query in the central base:

select metric_id, count(*) as count
from dss_metric_types
group by metric_id
having count(1) > 1
order by metric_id

Take a snapshot of an application

Snapshot fails with SQL error

Follow the steps below:

1. Open PGAdmin and check table dss_metric_types present in central base. When you check table dss_metric_types in pgadmin you will get somewhat like this:

CREATE TABLE <MISSING_CONSTRAINT_CENTRAL>.dss_metric_types
(
  metric_id integer NOT NULL,
  metric_name character varying(255),
  metric_description character varying(1500),
  metric_type integer NOT NULL,
  metric_group integer NOT NULL,
  metric_depends_on character varying(255),
  metric_options integer NOT NULL,
  scope_id integer NOT NULL DEFAULT 0,
  property_id integer NOT NULL DEFAULT 0
)
WITH (
  OIDS=FALSE
);
ALTER TABLE <MISSING_CONSTRAINT_CENTRAL>.dss_metric_types
  OWNER TO operator;

Instead of

CREATE TABLE <STANDARD_CENTRAL>.dss_metric_types
(
  metric_id integer NOT NULL,
  metric_name character varying(255),
  metric_description character varying(1500),
  metric_type integer NOT NULL,
  metric_group integer NOT NULL,
  metric_depends_on character varying(255),
  metric_options integer NOT NULL,
  scope_id integer NOT NULL DEFAULT 0,
  property_id integer NOT NULL DEFAULT 0,
  CONSTRAINT dss_metr_typ_pk PRIMARY KEY (metric_id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE <STANDARD_CENTRAL>.dss_metric_types
  OWNER TO operator;

In the first query "CONSTRAINT dss_metr_typ_pk PRIMARY KEY (metric_id)" is missing which happened because of the bug in PostgreSQL so the solution for this issue is as follows:

1. Open a SQL session in PGAdmin on the central schema and perform the following queries :

   truncate table dss_metric_value_types;
   vacuum full dss_metric_value_types;
   truncate table dss_metric_types;
   vacuum full dss_metric_types;

2. With Servman perform a component-> reinstall on the central schema.
3. Launch a new snapshot on the application.

Ticket # 8913