DE57663 GCP Only - Load Data Warehouse deadlocks on grant GRANT SELECT ON ALL TABLES IN SCHEMA sXXXXpdwh TO sXXXXpdwh

Products

• Clarity PPM SaaS

Issue/Introduction

DE57663 GCP Only - Load Data Warehouse deadlocks on grant GRANT SELECT ON ALL TABLES IN SCHEMA sXXXXpdwh TO sXXXXpdwh

 

Steps to Reproduce:

1. On a large dataset, run Load Data Warehouse - Full

 

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).

Environment

Release : 15.8.1

Component : CA PPM SAAS DATA WAREHOUSE

Resolution

This is logged as DE57663, in review by Engineering