Problem Description

The following error occurs during analysis which are made on Kb that have been wrongly exported and restored with CSS tools (CSSdump & CSSrestore) ;

SQL Error: ERROR: duplicate key value violates unique constraint "idx2_objects".
SQL Error: CONTEXT: SQL statement "insert into Objects (IdKey, IdNam, IdShortNam, ObjTyp) select INTERNAL_ID, NAME_ID, SHORT_NAME_ID, OBJECT_TYPE_ID from AMT_OBJECT obj where obj.STATUS = 'I'".
SQL Error: PL/pgSQL function "amt_f_maj_kb_prj" line 76 at SQL statement.
SQL Error: SQL statement "select AMT_F_MAJ_KB_PRJ ( $1 )".
SQL Error: PL/pgSQL function "amt_f_maj_kb" line 15 at SQL statement.
SQL Error: SQL statement "select AMT_F_MAJ_KB ( $1 )".

This issue seems to be related to incorrect valuse of the sequences idkey_generator and idpath_generator used to generate id,

which leads to duplicated values for some primary keys constraints.

Applicable in CAST Version
Release
Yes/No
8.3.x(tick)
8.2.x(tick)
8.1.x(tick)
8.0.x(tick)
7.3.x(tick)
7.2.x(tick)
7.0.x(tick)
Action Plan

Run the following queries on the KB.

  1. set search_path = <Your schema name>
  2. Value of Id sequence

    select 
    max(T.nb)+1 from (
    select max(IdKey) 
    as nb from keys
    union
    select max(IdAcc) 
    as nb from acc
    union
    select max(Idfus) 
    as nb from fusacc
    union
    select max(idkey) 
    as nb from Objects
    union
    select max(idacc) 
    as nb from accraw
    union
    select max(idacc) 
    as nb from AccSymb
    Union
    select max(idkeypar) 
    as nb from keypar) as T 
    

    Save the value named V1

  3. Value of IdPath sequence

    select max(idfilref) from refpath

    Save the value named V2

  4. Value of dss_history sequence

    select max(history_id)+1 from dss_history

    Save the value named V3

  5. Play the following queries

    alter SEQUENCE idkey_generator RESTART WITH <V1>;
    alter SEQUENCE idpath_generator RESTART WITH <V2>;
    alter SEQUENCE dss_history_history_id_seq RESTART WITH <V3>; 

    You can verify the values in the "sequences" part in the PGADMIN tree.

  6. Activate the value

    select nextval('idkey_generator') ;
    select nextval('idpath_generator');
    select nextval('dss_history_history_id_seq');
Notes

 

Related Pages