The Incremental Load Data Warehouse job fails intermittently with the following exception
Executing ETL Job. |
||
Error | 1/19/23 2:30 AM |
Refresh Matl View - An error occurred executing this job entry : Couldn't execute SQL: DO $$DECLARE V_COUNT BIGINT; V_SQL_TEXT TEXT; V_DELETE_IDX TEXT; V_TABLENAME VARCHAR(30); V_INDEXNAME VARCHAR(30); V_EXCEPTION VARCHAR(4000); V_SQLERRM VARCHAR(4000); V_SQLSTATE VARCHAR(5); cursor_1 REFCURSOR;BEGINIF 1 = 1 THEN SELECT COUNT(1) INTO V_COUNT FROM PG_CATALOG.PG_CLASS C WHERE C.RELKIND = 'm' AND UPPER(C.RELNAME) = 'DWH_FIN_PLAN_VIRT_NEW_MV'; IF V_COUNT = 1 THEN EXECUTE 'DROP INDEX IF EXISTS DWH_FIN_PLAN_VIRTUAL_MV_N1'; EXECUTE 'DROP INDEX IF EXISTS DWH_FIN_PLAN_VIRTUAL_MV_U1'; EXECUTE 'DROP INDEX IF EXISTS DWH_FIN_PLAN_VIRTUAL_SUM_MV_N1'; EXECUTE 'DROP INDEX IF EXISTS DWH_FIN_PLAN_VIRTUAL_SUM_MV_U1'; ALTER MATERIALIZED VIEW DWH_FIN_PLAN_VIRTUAL_MV RENAME TO DWH_FIN_PLAN_VIRT_OLD_MV; ALTER MATERIALIZED VIEW DWH_FIN_PLAN_VIRTUAL_SUM_MV RENAME TO DWH_FIN_PLAN_VIRT_OLD_SUM_MV; ALTER MATERIALIZED VIEW DWH_FIN_PLAN_VIRT_NE |
Error | 1/19/23 2:30 AM |
ETL Job Failed. Please see log bg-dwh.log for details. |
Job Completed | 1/19/23 2:30 AM |
NJS-0401: Execution of job failed. |
Release : 16.1.0
The job cannot build the unique index because there are duplicate rows
ERROR: duplicate key value violates unique constraint "dwh_fin_plan_virtual_mv_u1"
Detail: Key (plan_detail_key, period_key, language_code_key)=(5664215, 5027069, 1) already exists.
Where: SQL statement "INSERT INTO ppm_dwh.dwh_fin_plan_virtual_mv
SELECT (diff.newdata).* FROM pg_temp_83.pg_temp_20779_2 diff WHERE tid IS NULL"
SQL statement "REFRESH MATERIALIZED VIEW CONCURRENTLY DWH_FIN_PLAN_VIRTUAL_MV"
PL/pgSQL function inline_code_block line 190 at SQL statement
Follow the steps documented here to Enable Financial Plan Actuals