CMS Snapshot Analysis - Run Analyzer - Fatal Error - SQL Error - could not create unique index idx_cat

Description

During an analysis, the following error seen in the synchronization log :

SQL Error: ERROR: could not create unique index "idx_cat".

SQL Error: DETAIL: Key (idcat)=(139512) is duplicated. (Severity 1, Msg No 1).

Observed in CAST AIP

Release	Yes/No
8.3.x
8.2.x
8.1.x
8.0.x
7.3.x
7.2.x
7.0.x

Observed on RDBMS

RDBMS	Yes/No
Oracle Server
Microsoft SQL Server
CSS3
CSS2
CSS1

Step by Step Scenario

Package the source code
Run the analysis, analysis stops with errors in the log.

Action Plan

This issue occurs due to duplicates in the following tables in the local database:

cat
catattr
catcat

We need to remove the duplicates from the above tables.

For this First create a temporary table
Insert distinct values from the table to this temporary table.
Truncate the table and insert the values from the temporary table to this table,
Drop the temporary table.
Then run the analysis.

Below are the queries to remove the duplicate values from the tables.

CREATE TABLE tmp_cat
(
  idcat integer NOT NULL,
  catnam character varying(255) NOT NULL,
  catdsc character varying(255) NOT NULL,
  status character(10) NOT NULL
)
WITH (
  OIDS=FALSE
);

ALTER TABLE tmp_cat OWNER TO operator;

Insert into tmp_cat select distinct * from cat;
truncate table cat;
insert into cat select * from tmp_cat;
drop table tmp_cat;

create unique index idx_cat on cat using btree (idcat);
 

CREATE TABLE tmp_catattr
(
  idcat integer NOT NULL,
  attrnam character varying(255),
  attrtyp integer NOT NULL,
  intval integer,
  strval character varying(255),
  status character(10) NOT NULL
)
WITH (
  OIDS=FALSE
);

ALTER TABLE tmp_catattr OWNER TO operator;

insert into tmp_catattr select a.* from catattr a, catattr b where a.idcat = b.idcat and a.attrnam = b.attrnam and a.ctid < b.ctid;
truncate table catattr;
insert into catattr select * from tmp_catattr;
drop table tmp_catattr;

CREATE TABLE tmp_catcat
(
  idcat integer NOT NULL,
  idcatparent integer NOT NULL,
  status character(10) NOT NULL
)
WITH (
  OIDS=FALSE
);

ALTER TABLE tmp_catcat OWNER TO operator;

insert into tmp_catcat select a.* from catcat a, catcat b where a.idcat = b.idcat and a.idcatparent = b.idcatparent and a.ctid < b.ctid;
truncate table catcat;
insert into catcat select * from tmp_catcat;
drop table tmp_catcat;

Impact on Analysis Results and Dashboard

Notes/comments

Ticket # 4021

Related Pages