Incremental DWH Load Failed with deadlock REFRESH MATERIALIZED VIEW DWH_FIN_PLAN_VIRTUAL_SUM_MV

Products

• Clarity PPM SaaS

Issue/Introduction

When attempting to run an Adhoc report using Financial Management domain while DWH Incremental job is running will cause deadlock on DWH job and it will fail.

Environment

Release : 15.9.2

Cause

Error observed in bg-dwh.log is below

ERROR 2021-02-08 10:22:55,546 [dwh_fin_plan_per_facts UUID: 722777a7-a3f7-4650-9fc8-7b2607a8b3d8] dwh.event Fin Transactions? - An error occurred executing this job entry : Couldn't execute SQL: DO $$BEGINCALL DWH_SAVEDROP_CREATE_INDEXES_SP('DWH_FIN_PLAN_VIRTUAL_MV','SAVE_DROP');REFRESH MATERIALIZED VIEW DWH_FIN_PLAN_VIRTUAL_MV;CALL DWH_SAVEDROP_CREATE_INDEXES_SP('DWH_FIN_PLAN_VIRTUAL_MV','CREATE');CALL DWH_GATHER_TABLE_STATS_SP('DWH_FIN_PLAN_VIRTUAL_MV');CALL DWH_SAVEDROP_CREATE_INDEXES_SP('DWH_FIN_PLAN_VIRTUAL_SUM_MV','SAVE_DROP');REFRESH MATERIALIZED VIEW DWH_FIN_PLAN_VIRTUAL_SUM_MV;CALL DWH_SAVEDROP_CREATE_INDEXES_SP('DWH_FIN_PLAN_VIRTUAL_SUM_MV','CREATE');CALL DWH_GATHER_TABLE_STATS_SP('DWH_FIN_PLAN_VIRTUAL_SUM_MV');END $$;ERROR: deadlock detected  Detail: Process 24370 waits for AccessExclusiveLock on relation 464960713 of database 21845169; blocked by process 19741.Process 19741 waits for AccessShareLock on relation 464960704 of database 21845169; blocked by process 24370.  Hint: See server log for query details.  Where: SQL statement "REFRESH MATERIALIZED VIEW DWH_FIN_PLAN_VIRTUAL_SUM_MV"PL/pgSQL function inline_code_block line 8 at SQL statement

Resolution

DE59726

Fix included in upcoming 16.0 release

Workaround: Run a Full Load DWH

This defect should be very infrequent if at all defect.

It does not happen in most environments