16.0 Incremental Load DWH fails with "syntax error" on PostgreSQL

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

On Postgres 16.0, the Load Data Warehouse (DWH) incremental job can fail with "syntax error" if certain tables are updated.

Actions that cause the issue:

• Adding a new department, location or any new OBS unit or rename
• Adding a new portfolio with investments and then syncing it.

Failing on tables:

• DWH_CMN_OBS_HIERARCHY
• DWH_PFM_INVESTMENT_MAPPING

STEPS TO REPRODUCE:

1. Ensure Load DWH Incremental job runs with no issue on Clarity 16.0 with PostgreSQL
2. Create a new Portfolio
3. Add several projects
4. Sync the portfolio
5. Now run Load Data Warehouse - Incremental job again

It fails with the below error message:

ERROR 2021-11-30 13:53:24,648 [Thread-33113] dwh.event Merge - PFM_INVESTMENT  - An error occurred executing this job entry : 
Couldn't execute SQL: DO $$
DECLARE
    V_SQL_TEXT text;
    V_EXCEPTION varchar(4000);
    V_SQLERRM varchar(4000);
    V_SQLSTATE varchar(5);
BEGIN
V_SQL_TEXT := 'INSERT INTO DWH_PFM_INVESTMENT_MAPPING SELECT CLARITY_CREATED_DATE AS CLARITY_CREATED_DATE, CLARITY_PORTFOLIO_KEY AS CLARITY_PORTFOLIO_KEY, LAST_UPDATED_DATE AS CLARITY_UPDATED_DATE, INVESTMENT_ID AS INVESTMENT_ID, INVESTMENT_KEY AS INVESTMENT_KEY, INVESTMENT_NAME AS INVESTMENT_NAME, PORTFOLIO_ID AS PORTFOLIO_ID, PORTFOLIO_KEY AS PORTFOLIO_KEY, PORTFOLIO_NAME AS PORTFOLIO_NAME, TO_TIMESTAMP('30-nov-2021 13:53:19', 'dd-mon-yyyy hh24:mi:ss') AS dw_updated_date FROM DWH_PFM_INVESTMENT_MAPPING_V A WHERE LAST_UPDATED_DATE >= TO_TIMESTAMP('30-nov-2021 13:41:57', 'dd-mon-yyyy hh24:mi:ss') AND LAST_UPDATED_DATE <= (SELECT DISTINCT dwh_dim_start_date FROM dwh_cfg_settings) ON CONFLICT (PORTFOLIO_KEY, INVESTMENT_KEY) DO UPDATE SET CLARITY_PORTFOLIO_KEY = EXCLUDED.CLARITY_PORTFOLIO_KEY, ' ||
              'PORTFOLIO_ID = EXCLUDED.PORTFOLIO_ID, PORTFOLIO_NAME = EXCLUDED.PORTFOLIO_NAME, INVESTMENT_ID = EXCLUDED.INVESTMENT_ID, INVESTMENT_NAME = EXCLUDED.INVESTMENT_NAME, ' ||
              'CLARITY_CREATED_DATE = EXCLUDED.CLARITY_CREATED_DATE, CLARITY_UPDATED_DATE = EXCLUDED.CLARITY_UPDATED_DATE, DW_UPDATED_DATE = EXCLUDED.DW_UPDATED_DATE'; 
EXECUTE V_SQL_TEXT;
EXCEPTION
    WHEN OTHERS THEN
    BEGIN
        V_SQLSTATE := SQLSTATE;
        V_SQLERRM := SQLERRM;
        V_SQL_TEXT := 'INSERT INTO DWH_PFM_INVESTMENT_MAPPING SELECT CLARITY_CREATED_DATE AS CLARITY_CREATED_DATE, CLARITY_PORTFOLIO_KEY AS CLARITY_PORTFOLIO_KEY, LAST_UPDATED_DATE AS CLARITY_UPDATED_DATE, INVESTMENT_ID AS INVESTMENT_ID, INVESTMENT_KEY AS INVESTMENT_KEY, INVESTMENT_NAME AS INVESTMENT_NAME, PORTFOLIO_ID AS PORTFOLIO_ID, PORTFOLIO_KEY AS PORTFOLIO_KEY, PORTFOLIO_NAME AS PORTFOLIO_NAME, TO_TIMESTAMP(**30-nov-2021 13:53:19**, **dd-mon-yyyy hh24:mi:ss**) AS dw_updated_date FROM DWH_PFM_INVESTMENT_MAPPING_V A WHERE LAST_UPDATED_DATE >= TO_TIMESTAMP(**30-nov-2021 13:41:57**, **dd-mon-yyyy hh24:mi:ss**) AND LAST_UPDATED_DATE <= (SELECT DISTINCT dwh_dim_start_date FROM dwh_cfg_settings) ON CONFLICT (PORTFOLIO_KEY, INVESTMENT_KEY) DO UPDATE SET CLARITY_PORTFOLIO_KEY = EXCLUDED.CLARITY_PORTFOLIO_KEY, ' ||
                      'PORTFOLIO_ID = EXCLUDED.PORTFOLIO_ID, PORTFOLIO_NAME = EXCLUDED.PORTFOLIO_NAME, INVESTMENT_ID = EXCLUDED.INVESTMENT_ID, INVESTMENT_NAME = EXCLUDED.INVESTMENT_NAME, ' || 
                      'CLARITY_CREATED_DATE = EXCLUDED.CLARITY_CREATED_DATE, CLARITY_UPDATED_DATE = EXCLUDED.CLARITY_UPDATED_DATE, DW_UPDATED_DATE = EXCLUDED.DW_UPDATED_DATE'; 
        V_EXCEPTION := 'ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_PFM_INVESTMENT_MAPPING. ' || V_SQLERRM;
        ROLLBACK;
        BEGIN
        INSERT INTO DWH_CMN_ERROR_MESSAGE VALUES('DWH_PFM_INVESTMENT_MAPPING', V_EXCEPTION, V_SQLSTATE,REPLACE(V_SQL_TEXT,'**',''''));
        END;
        COMMIT;
        RAISE EXCEPTION '%', V_EXCEPTION;
    END;
    COMMIT;
END $$;

ERROR: syntax error at or near "30"
  Position: 546

Environment

Release : 16.0

Component : CLARITY DATA WAREHOUSE

Database: PostgreSQL

Cause

This has been reported as DE63317.

Resolution

On Premise: Fixed in 16.0.1 and patch 16.0.0.1.

GCP: The fix has been applied as a prepatch in SAAS environments as Defect DE63317.  

Workaround:

Run the Full Load of the Load Data Warehouse job for now.

Mitigation:

• Run the Full Load Data Warehouse any time changes are made to one of the below:
  • OBS
  • OBS units
  • locations
  • departments
• Enable notifications on Load Data Warehouse job failures to respond when an incremental fails and run a full load.
• Determine how long the Full Load DWH job takes to complete in your environment and schedule reports to happen after the full load runs.