DWH Incremental fails continuously with "there is no unique or exclusion constraint matching..." also on OOTB lookups

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Load Data Warehouse Incremental continuously fails with error on different tables including OOTB ones:

ERROR: ENCOUNTERED EXCEPTION WHILE MERGING INTO DWH_LOOKUP_LOAD DWH_LKP_TEST_PRJ__00. there is no unique or exclusion constraint matching the ON CONFLICT specification
  Where: PL/pgSQL function inline_code_block line 21 at RAISE

ERROR: ENCOUNTERED EXCEPTION WHILE MERGING INTO DWH_ODF_TEST_PROGRAM_LN. there is no unique or exclusion constraint matching the ON CONFLICT specification
  Where: PL/pgSQL function inline_code_block line 22 at RAISE

Environment

Release : 16.0.3, PostgreSQL and On Premise

Resolution

Check for the indexes on these tables in DWH database - if they are not created, and ppm_dwh user does not have superuser:  this is a permissions issue - proceed with the below steps to get the indexes rebuilt:

1. Grant superuser right to both clarity and dwh user
2. Drop the DWH database and recreate the schema from blank (make sure superuser rights still there)
3. Run Full Load 
4. Run Incremental Load

Additional Information

If the issue only happens on one custom attribute and object, check KB DWH error on custom object: "there is no unique or exclusion constraint matching..."