Full Load DWH fails with "must be a member of the role whose process is being terminated or member of pg_signal_backend"

Products

• Clarity PPM SaaS

Issue/Introduction

On SAAS GCP, The Full Load Data Warehouse intermittently fails with the following or similar error related to an ALTER TABLE

ERROR 2022-02-19 21:07:46,048 [dwh_fin_plan_summary_facts UUID: 2cc224bc-1b1f-4ea2-aa6c-2e9b8340c14d] 
dwh.event Load Facts PG - An error occurred executing this job entry : Couldn't execute SQL: CALL DWH_FIN_PLAN_SUM_FACTS_LOAD();
ERROR: must be a member of the role whose process is being terminated or member of pg_signal_backend Where:
SQL statement "SELECT pg_terminate_backend(v_pid)" PL/pgSQL function dwh_disable_constraints_sp(character varying,character varying) line 58 at PERFORM SQL statement "CALL DWH_DISABLE_CONSTRAINTS_SP('DWH_FIN_PLAN_SUMMARY_FACTS','FK')" PL/pgSQL function dwh_fin_plan_sum_facts_load() line 70 at CALL 

Environment

Release : 16.0.0, 16.0.1 

Cause

DE64173

• The Load DWH is coming into conflict with Postgres autovacuum runs.
• The run of an auto vacuum will lock the table and prevent any ALTER table statements such as dropping constraints 

 

Workaround: The next 'Incremental' execution of the Load DWH job will resolve the issue. 

 

Resolution

Resolved in 16.0.2

Prior Release:

• This is resolved by Broadcom checking the Postgres Auto Vacuum activity and configuring as appropriate.
• This is resolved in all PROD environments.

Additional Information

This may surface if a large query is being run on the Data Warehouse table being updated by the Load Data Warehouse.  It is a timing issue.

Please ensure all scheduled reports or odata pulls are not scheduled the same time as the Load DWH.

Another culprit could be the Auto Vacuum taking too long as mentioned above.