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

This page will help you to solve the issue related to analysis failure during the Saving step on the server with the following SQL Error message:

2019-07-24 08:43:23.401149 MODULMSG ; Job execution Comparing objects on server . . .
2019-07-24 08:43:33.885615 MODULMSG ; Job execution Comparison completed.
2019-07-24 08:43:33.885615 MODULMSG ; Job execution Merging objects on server . . .
2019-07-24 08:46:27.316284 Error MODULMSG ; Job execution SQL Error: ERROR: duplicate key value violates unique constraint "pk_accidacc".
0 gcommon\gcconnectionopen.h 23
2019-07-24 08:46:27.316284 Error MODULMSG ; Job execution SQL Error: DETAIL: Key (idacc)=(218593998) already exists..
0 gcommon\gcconnectionopen.h 23
2019-07-24 08:46:27.316284 Error MODULMSG ; Job execution SQL Error: CONTEXT: SQL statement "insert into Acc (IdAcc, IdClr, IdCle, AccTypLo, AccTypHi, AccTypLo2, AccTypHi2, AccKnd, IdPro, Prop).
0 gcommon\gcconnectionopen.h 23
2019-07-24 08:46:27.316284 Error MODULMSG ; Job execution SQL Error: select distinct IdAcc, INTERNAL_SOURCE_ID, INTERNAL_TARGET_ID, AccTypLo, AccTypHi, 0, 0, 0, IDPRO, PROP .
0 gcommon\gcconnectionopen.h 23
2019-07-24 08:46:27.316284 Error MODULMSG ; Job execution SQL Error: from TMP_WK_RESACC t.
0 gcommon\gcconnectionopen.h 23
2019-07-24 08:46:27.316284 Error MODULMSG ; Job execution SQL Error: where t.STATUS = 'I'.
0 gcommon\gcconnectionopen.h 23
2019-07-24 08:46:27.316284 Error MODULMSG ; Job execution SQL Error: and t.LINK_KIND='N'".
0 gcommon\gcconnectionopen.h 23
2019-07-24 08:46:27.316284 Error MODULMSG ; Job execution SQL Error: PL/pgSQL function amt_f_maj_lnkmain(integer) line 90 at SQL statement.
0 gcommon\gcconnectionopen.h 23
2019-07-24 08:46:27.316284 Error MODULMSG ; Job execution SQL Error: SQL statement "select AMT_F_MAJ_LNKMAIN (I_IDSESSION)".
0 gcommon\gcconnectionopen.h 23
2019-07-24 08:46:27.316284 Error MODULMSG ; Job execution SQL Error: PL/pgSQL function amt_f_maj_objmain(integer) line 111 at SQL statement.
0 gcommon\gcconnectionopen.h 23
2019-07-24 08:46:27.316284 Error MODULMSG ; Job execution SQL Error: SQL statement "select AMT_F_MAJ_OBJMAIN (I_IDSESSION)".
0 gcommon\gcconnectionopen.h 23
2019-07-24 08:46:27.316284 Error MODULMSG ; Job execution SQL Error: PL/pgSQL function amt_f_maj_kb_obj(integer) line 24 at SQL statement.
0 gcommon\gcconnectionopen.h 23
2019-07-24 08:46:27.331906 Error MODULMSG ; Job execution SQL Error: SQL statement "select AMT_F_MAJ_KB_OBJ (I_IDSESSION)".
0 gcommon\gcconnectionopen.h 23
2019-07-24 08:46:27.331906 Error MODULMSG ; Job execution SQL Error: PL/pgSQL function amt_f_maj_kb(integer) line 24 at SQL statement.
0 gcommon\gcconnectionopen.h 23
2019-07-24 08:46:27.331906 Error MODULMSG ; Job execution SQL Error: SQL statement "select AMT_F_MAJ_KB (I_IDSESSION)".
0 gcommon\gcconnectionopen.h 23
2019-07-24 08:46:27.331906 Error MODULMSG ; Job execution SQL Error: PL/pgSQL function cache_flushdata(integer) line 40 at SQL statement (Severity 1, Msg No 1).
0 gcommon\gcconnectionopen.h 19
2019-07-24 08:46:27.425664 Error MODULMSG ; Job execution Procedure call failed: ?cml_004.CACHE_FLUSHDATA,I_IDSESSION 0 amt\dbsaver.cpp 705

1. Migrate from lower version to higher version.
2. Analysis fails with the above error in analysis log.

Follow the below steps:

1. Run the below query on the local database to identify if the issue is due to IDKEY_GENERATOR

   select count(*) from acc where idacc >= (select nextval('IDKEY_GENERATOR'))

    If the above query returns count greater then 0 then go to next step.

2. Run the below query on the KB and note the result of below query

   select max(IdObj) + 1 from ( 
   select max(IdKey) as IdObj from Keys union all
   select max(IdAcc) from Acc union all
   select max(IdAcc) from AccSymb union all
   select max(IdKeyPar) from KeyPar union all
   select max(IdFus) from FusAcc union all
   select max(IdSel) from AnaSel )t

3. Run the below query in KB to create a new IDKEY_GENERATOR

   DROP SEQUENCE IDKEY_GENERATOR;
   CREATE SEQUENCE IDKEY_GENERATOR START WITH <Result_from_above_query>

4. Run the analysis again.

Analysis will run fine as expected