DWH Full Load fails with "Could not remove FK constraint" error

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Data Warehouse (DWH) Full load job is failing with below error.

ERROR: Could not remove FK constraint XXXXXXX on table TABLE_NAME. Aborting DWH load. Where: PL/pgSQL function dwh_disable_constraints_sp(character varying,character varying) line 66 at RAISE SQL statement "CALL DWH_DISABLE_CONSTRAINTS_SP('ALL_DWH')" PL/pgSQL function dwh_cfg_pre_config_sp(character varying) line 16 at CALL

Examples of tables this occurs on : dwh_res_period_facts, dwh_inv_idea

Environment

Release : 15.9.2

Component : CLARITY SAAS DATA WAREHOUSE

Cause

This occurs when there is a process on Postgres which already has a lock on the Table. In this scenario, the procedure DWH_DISABLE_CONSTRAINTS_SP doesn't try to drop the constraint and generates the above error.

This is caused by: DE60883 / DE60749

Resolution

Both Defects DE60883 / DE60749 are fixed in 15.9.3 

Workaround:

Ask the DBA to run the below query on DWH database. 

SELECT COALESCE(MAX(pid),0)                    

FROM   pg_locks l
INNER JOIN pg_class cl ON l.relation=cl.oid
INNER JOIN pg_namespace nsp ON cl.relnamespace=nsp.oid
WHERE l.pid <> pg_backend_pid()
 AND   relname IN (SELECT LOWER('TABLE_NAME') dtable
FROM   DUAL
 UNION
 SELECT ccu.table_name dtable
 FROM   information_schema.table_constraints tc
INNER JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tc.constraint_name
AND ccu.table_schema = tc.table_schema
 WHERE  tc.constraint_type = 'FOREIGN KEY'
AND    tc.table_name = LOWER('TABLE_NAME'))

The query will provide the PID which is holding the lock on the said table. Validate the session details and either wait for it to complete(in case it's active) or kill it (if it's an idle session which has been there for long time).