DE57663 GCP Only - Load Data Warehouse deadlocks on grant GRANT SELECT ON ALL TABLES IN SCHEMA sXXXXpdwh TO sXXXXpdwh
Steps to Reproduce:
Expected Results: The job to run successfully
Actual Results: The job fails with a deadlock issue:
Reload Facts? - An error occurred executing this job entry : Couldn't execute SQL: CALL DWH_CFG_POP_HISTORY_SP();TRUNCATE TABLE DWH_CMN_ERROR_MESSAGE;CALL DWH_CFG_PRE_CONFIG_SP('N');CALL DWH_CFG_FOREIGN_TABLES_SP();ERROR: deadlock detected
Detail: Process 12098 waits for ExclusiveLock on tuple (63,41) of relation 1259 of database 341371; blocked by process 25154.
Process 25154 waits for ShareLock on transaction 450603; blocked by process 25156.
Process 25156 waits for ShareLock on transaction 427825; blocked by process 12098.
Hint: See server log for query details. Where: SQL statement "GRANT SELECT ON ALL TABLES IN SCHEMA sXXXXpdwh TO sXXXXpdwh "PL/pgSQL function dwh_cfg_foreign_tables_sp() line 32 at EXECUTE
Workaround: Run the job again
When reviewing dwh_cfg_foreign_tables_sp we can see it's calling this statement:
GRANT SELECT ON ALL TABLES IN SCHEMA sXXXXpdwh TO sXXXXpdwh
The statement is used when recreating the foreign tables (PG specific only).
Release : 15.8.1
Component : CA PPM SAAS DATA WAREHOUSE
This is logged as DE57663, in review by Engineering