LDWH ERROR: no unique or exclusion constraint matching the ON CONFLICT specification
Article ID: 226370
Updated On:
Products
Issue/Introduction
Incremental Load Data Warehouse fails with the following error:
ERROR 2021-10-18 04:28:16,049 [dwh_x_plan_act_per_facts_pg UUID: b130ee4b-6dd8-4363-ace8-7286ea87ec5d] dwh.event Fin Transactions? -
An error occurred executing this job entry : Couldn't execute SQL: CALL DWH_X_PLAN_ACT_PER_FACTS_LOAD(P_LAST_LOAD_DATE => to_timestamp('2021/10/14 23:00:08', 'yyyy/MM/dd HH24:mi:ss')::timestamp, P_CURRENT_DIM_LOAD_DATE => to_date('2021/10/18 04:20:23', 'yyyy/mm/dd HH24:MI:SS')::timestamp);
ERROR: ENCOUNTERED EXCEPTION WHILE MERGING INTO DWH_X_PLAN_ACT_PER_FACTS. there is no unique or exclusion constraint matching the ON CONFLICT specification Where: PL/pgSQL function dwh_x_plan_act_per_facts_load(timestamp without time zone,timestamp without time zone) line 70 at RAISEINFO
2021-10-18 04:28:16,078 [Thread-471045] dwh.event null - Job execution finished
ERROR 2021-10-18 04:28:16,092 [Dispatch Load Data Warehouse : bg@%host%(tenant=clarity)] dwh.event ETL Job Failed. Log details below:
2021/10/18 04:28:12 - audit table name - & DWH_CFG_AUDIT2021/10/18 04:28:12 - audit table name - & DWH_CFG_AUDIT
2021/10/18 04:28:13 - audit table name - & DWH_CFG_AUDIT
2021/10/18 04:28:13 - audit table name - & DWH_CFG_AUDIT
2021/10/18 04:28:13 - audit table name - & DWH_CFG_AUDIT
2021/10/18 04:28:14 - audit table name - & DWH_CFG_AUDIT
2021/10/18 04:28:14 - audit table name - & DWH_CFG_AUDIT
2021/10/18 04:28:16 - Fin Transactions? - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry : 2021/10/18 04:28:16 - Fin Transactions? - Couldn't execute SQL:
CALL DWH_X_PLAN_ACT_PER_FACTS_LOAD(P_LAST_LOAD_DATE => to_timestamp('2021/10/14 23:00:08', 'yyyy/MM/dd HH24:mi:ss')::timestamp, P_CURRENT_DIM_LOAD_DATE => to_date('2021/10/18 04:20:23', 'yyyy/mm/dd HH24:MI:SS')::timestamp);
2021/10/18 04:28:16 - Fin Transactions? - 2021/10/18 04:28:16 - Fin Transactions? -
ERROR: ENCOUNTERED EXCEPTION WHILE MERGING INTO DWH_X_PLAN_ACT_PER_FACTS. there is no unique or exclusion constraint matching the ON CONFLICT specification2021/10/18 04:28:16 - Fin Transactions? -
Where: PL/pgSQL function dwh_x_plan_act_per_facts_load(timestamp without time zone,timestamp without time zone) line 70 at RAISEnullINFO
Environment
Release : 15.9.3, 16.0, 16.0.1
Component : Load Data Warehouse
Postgres
Cause
A prior run of the Load DWH was interrupted/cancelled and indexes were removed and not added back
Resolution
Work with support to identify missing indexes on the table in the error message.
In this case: DWH_X_PLAN_ACT_PER_FACTS
Work with the DBA to have these added back, for example:
CREATE UNIQUE INDEX dwh_x_plan_act_per_facts_u3 ON dwh_x_plan_act_per_facts USING btree (plan_detail_key, plan_key, investment_key, period_key, cost_type_key, transaction_class_key, charge_code_key, department_key, input_type_code_key, location_key, user_value1_key, user_value2_key, resource_class_key, resource_key, role_key);
CREATE INDEX dwh_x_plan_act_per_facts_pk2 ON dwh_x_plan_act_per_facts USING btree (plan_key);
CREATE INDEX dwh_x_plan_act_per_facts_n1 ON dwh_x_plan_act_per_facts USING btree (investment_key, plan_detail_key, period_key);
CREATE INDEX dwh_x_plan_act_per_facts_n2 ON dwh_x_plan_act_per_facts USING btree (dw_updated_date);
Re-run the Load DWH
Additional Information
A Full Load should also resolve this issue.
Feedback
