Insert or update on table "dwh_x_res_avail_per_facts" violates foreign key constraint "dwh_x_res_avail_per_facts_fk1"
search cancel

Insert or update on table "dwh_x_res_avail_per_facts" violates foreign key constraint "dwh_x_res_avail_per_facts_fk1"


Article ID: 234042


Updated On:


Clarity PPM SaaS


Steps to Reproduce:

  1. Incremental DWH Load
  2. Resource updates?

Expected Results:

  • Job runs without a problem.

Actual Results:

Job Type: Load Data Warehouse - Job Properties



Job Name Load Data Warehouse - Incremental Sun Through Fri
Run ID 10523010
Job ID 5302737
Run Status Failed


Full Load  


Run Start Date 12/14/21 5:15 AM
Run Finish Date 12/14/21 5:24 AM
Duration (Minutes) 9:54

Log Entries

Log Entry Date Message
Job Started 12/14/21 5:15 AM
Message 12/14/21 5:15 AM
Executing ETL Job.
Error 12/14/21 5:24 AM
Load Facts PG - An error occurred executing this job entry : Couldn't execute SQL: CALL DWH_X_RES_AVAIL_PER_FACTS_LOAD(P_LAST_LOAD_DATE => to_timestamp('2021/12/14 03:15:13', 'yyyy/MM/dd HH24:mi:ss')::timestamp,P_CURRENT_DIM_LOAD_DATE => to_timestamp('2021/12/14 04:15:12', 'yyyy/mm/dd HH24:MI:SS')::timestamp);ERROR: ENCOUNTERED EXCEPTION WHILE MERGING INTO DWH_X_RES_AVAIL_PER_FACTS. insert or update on table "dwh_x_res_avail_per_facts" violates foreign key constraint "dwh_x_res_avail_per_facts_fk1"  Where: PL/pgSQL function dwh_x_res_avail_per_facts_load(timestamp without time zone,timestamp without time zone) line 89 at RAISE
Error 12/14/21 5:24 AM
ETL Job Failed. Please see log bg-dwh.log for details.
Job Completed 12/14/21 5:24 AM
NJS-0401: Execution of job failed.


Release : 16.0.0



The issue was reported as defect DE63537.

Defect monitored further in clarity SaaS environments and could not be reproduced from 16.0.2. If the issue is noticed please log a case with Broadcom support as additional diagnostics from the test case needs to be captured to debug further. 

The following SQL query in particular addition to the bg-dwh.log

select * from dwh_cmn_error_message where table_name='DWH_X_RES_AVAIL_PER_FACTS'; 

select * from dwh_cmn_error_msg_history where table_name='DWH_X_RES_AVAIL_PER_FACTS