Full Load DWH Job Fails With Error: relation "avail_hrs" already exists (PostgreSQL)

Products

• Clarity PPM SaaS

Issue/Introduction

Full Load DWH Job started to fail recently with BG-DWH log error:

2023/01/30 10:11:12 - Write Time Info to Log.0 - 
2023/01/30 10:11:12 - Write Time Info to Log.0 - ------------> Linenr 1------------------------------
2023/01/30 10:11:12 - Write Time Info to Log.0 - Date Time Values
2023/01/30 10:11:12 - Write Time Info to Log.0 - 
2023/01/30 10:11:12 - Write Time Info to Log.0 - application_time = 2023-01-30 10:11:12.597-08
2023/01/30 10:11:12 - Write Time Info to Log.0 - ppm_time_minus_15 = 2023-01-30 10:10:57.75-08
2023/01/30 10:11:12 - Write Time Info to Log.0 - ppm_time_plus_15 = 2023-01-30 10:11:27.75-08
2023/01/30 10:11:12 - Write Time Info to Log.0 - dwh_time = 2023-01-30 10:11:12
2023/01/30 10:11:12 - Write Time Info to Log.0 - dwh_time_minus_15 = 2023-01-30 10:10:57.75-08
2023/01/30 10:11:12 - Write Time Info to Log.0 - dwh_time_plus_15 = 2023-01-30 10:11:27.75-08
2023/01/30 10:11:12 - Write Time Info to Log.0 - 
2023/01/30 10:11:12 - Write Time Info to Log.0 - ====================
2023/01/30 10:11:17 - Pre-Execution Config PG - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : An error occurred executing this job entry : 
2023/01/30 10:11:17 - Pre-Execution Config PG - Couldn't execute SQL: CALL DWH_CFG_LONG_RUN_QUERY_SP();
2023/01/30 10:11:17 - Pre-Execution Config PG - CALL DWH_CFG_POP_HISTORY_SP();
2023/01/30 10:11:17 - Pre-Execution Config PG - CALL DWH_CFG_TRUNCATE_TABLE_SP('DWH_CMN_ERROR_MESSAGE');
2023/01/30 10:11:17 - Pre-Execution Config PG - CALL DWH_CFG_PRE_CONFIG_SP('Y');
2023/01/30 10:11:17 - Pre-Execution Config PG - CALL DWH_CFG_FOREIGN_TABLES_SP();
2023/01/30 10:11:17 - Pre-Execution Config PG - UPDATE DWH_CFG_SETTINGS SET ENABLE_PLAN_ACTUALS = 0;
2023/01/30 10:11:17 - Pre-Execution Config PG - 
2023/01/30 10:11:17 - Pre-Execution Config PG - ERROR: relation "avail_hrs" already exists
2023/01/30 10:11:17 - Pre-Execution Config PG -   Where: importing foreign table "avail_hrs"
2023/01/30 10:11:17 - Pre-Execution Config PG - SQL statement "IMPORT FOREIGN SCHEMA [schma] EXCEPT (dual, cmn_db_history, dwh_cfg_object_type, prcounter, user_index_columns) FROM SERVER sst095pdwh_sst095p INTO sst095pdwh"
2023/01/30 10:11:17 - Pre-Execution Config PG - PL/pgSQL function dwh_cfg_foreign_tables_sp() line 30 at EXECUTE

Environment

Release : 16.1.0

Cause

This is due to an issue with the database link configuration being incorrect.

Resolution

Please open a case with Support to perform the DWH configuration.

1. Check if records need to be removed from tables:
a. pg_user_mapping and
b. pg_foreign_server 

2. Recreate DB link

3. Rebuild the DWH database.

Once rebuilt, rerun the full Load DWH job.