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
Release : 15.9.2
Component : CLARITY SAAS DATA WAREHOUSE
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
Both Defects DE60883 / DE60749 are fixed in 15.9.3
Ask the DBA to run the below query on DWH database.
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
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).