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

This page describes the solution when you get the following error during the analysis using peoplesoft package 5.0.0,

INF: 2015-02-10 19:48:22: [SQL] select psoft5_v_local.PSOFT_CONTENT_ENRICHMENT() INF: 2015-02-10 19:48:23: [SQL] select psoft5_v_local.PSOFT_DISCRIMINATION_DATA() ERR: 2015-02-10 19:48:45: Error while executing Batch: select psoft5_v_local.PSOFT_DISCRIMINATION_DATA() Query failed because: ERROR: duplicate key value violates unique constraint "psoft_objects_pk"   Detail: Key (object_id)=(593532) already exists.   Where: SQL statement "insert into psoft_objects (object_id,object_type,object_name,object_fullname,object_lastupd,object_checksum,parent_id)   select t1.idkey,t1.objtyp,t1.keynam,t4.fullname     ,coalesce((select upd.infval from psoft_wk_lastupd upd where upd.idobj = t1.idkey),'')     ,coalesce((select upd.infval from psoft_wk_checksum upd where upd.idobj = t1.idkey),0)     ,(select T2.idparent from keypar T2 where T2.idkey = t1.idkey)   from keys t1     join objpro t2 on (t2.idobj = t1.idkey)     join objfulnam t4 on (t4.idobj = t1.idkey)   where t1.objtyp in (1600673,1600999)   and t2.idpro = L_IDSUBSET" PL/pgSQL function psoft_discrimination_data() line 136 at SQL statement Batch entry 0 select psoft5_v_local.PSOFT_DISCRIMINATION_DATA() was aborted.  Call getNextException to see the cause.

1. CAST 7.3.x installed
2. PSOFT Lang copied to ProgramData
3. Extractor deployed
4. Schemas installed
5. Sources delivered
6. Analysis launched,
7. When analyzubg the peoplesoft UI, getting the error above

This is a known problem due to a side effect of the "PeopleSoft UA objects" SQL jobs added on top of this package as described in the documentation.

To fix, this issue, you can apply the following workaround:

1. In the PSOFT_DISCRIMINATION_DATA function, put in comments the following lines

– specific case for PeopleSoft : add UA objects (functions and methods) insert into psoft_objects (object_id,object_type,object_name,object_fullname,object_lastupd,object_checksum,parent_id) select t1.idkey,t1.objtyp,t1.keynam,t4.fullname ,coalesce((select upd.infval from psoft_wk_lastupd upd where upd.idobj = t1.idkey),'') ,coalesce((select upd.infval from psoft_wk_checksum upd where upd.idobj = t1.idkey),0) ,(select T2.idparent from keypar T2 where T2.idkey = t1.idkey) from keys t1 join objpro t2 on (t2.idobj = t1.idkey) join objfulnam t4 on (t4.idobj = t1.idkey) where t1.objtyp in (1600673,1600999) and t2.idpro = L_IDSUBSET;

After applying the workaround, analysis will not end up with the above error.

 Issue will be fixed in 5.0.1 peoplesoft release