PostgresSQL - Load Data Warehouse Full failing with duplicate key value violates unique constraint "dwh_x_inv_task_sum_facts_u1" or EXCEPTION WHILE MERGING INTO DWH_X_INV_TASK_SUM_FACTS. ON CONFLICT DO UPDATE command cannot affect row a second time

Products

• Clarity PPM SaaS

Issue/Introduction

PostgresSQL - Load Data Warehouse Full failing repeatedly.

Errors in bg-dwh.log:

Couldn't execute SQL: CALL DWH_X_INV_TASK_SUM_FACTS_LOAD (P_LAST_LOAD_DATE => to_timestamp('1910/01/01 12:00:00', 'yyyy/MM/dd HH24:mi:ss')::timestamp, P_CURRENT_DIM_LOAD_DATE => to_timestamp('2020/05/25 18:00:09', 'yyyy/mm/dd HH24:MI:SS')::timestamp);

ERROR: ENCOUNTERED EXCEPTION WHILE MERGING INTO DWH_X_INV_TASK_SUM_FACTS. ON CONFLICT DO UPDATE command cannot affect row a second time
  Where: PL/pgSQL function dwh_x_inv_task_sum_facts_load(timestamp without time zone,timestamp without time zone) line 78 at RAISE"

 

ERROR: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_X_INV_TASK_SUM_FACTS. duplicate key value violates unique constraint "dwh_x_inv_task_sum_facts_u1"

  Where: PL/pgSQL function dwh_x_inv_task_sum_facts_load(timestamp without time zone,timestamp without time zone) line 49 at RAISE

Environment

Release : 15.8

Component : CA PPM SAAS DATA WAREHOUSE

Cause

Caused by Earned Value duplicates in PRJ_EV_HISTORY

Resolution

Run the query on the database:

SELECT OBJECT_ID, OBJECT_TYPE, PERIOD_NUMBER, count(1)
from PRJ_EV_HISTORY
group by OBJECT_ID, OBJECT_TYPE, PERIOD_NUMBER having count(1) > 1

 

1. Identify all the projects that have the problem.
2. Open the projects in Clarity UI - go to Baseline - note you see two duplicate entries - click Update Costs
3. Update EV Costs for this project, note the duplicate entry disappears. Now run the same for all the reported projects.
4. Now run Load Data Warehouse - Full Load and it should complete successfully