DWH query is running long on PostgreSQL

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

A specific query to recreate views is taking very long, sometimes up to 8 hours.

 

STEPS TO REPRODUCE:

1. On a PG environment with DWH instant Sync enabled
2. Schedule Full and Incremental DWH jobs

Expected Results: The DWH job to run correctly

Actual Results: At least one Incremental instance per day is taking a lot more time to complete. A session on the database is taking over 8 hours and sometimes over 10 hours and getting automatically terminated

 

The below session running for 8+ hours:

 SELECT ('CREATE OR REPLACE VIEW ' || a.fact_view || ' AS ') AS operation,
            +
        a.fact_view,
            +
        a.column_name,
            +
        'COMMENT ON VIEW ' || a.fact_view || ' IS ''' || coalesce(utc.comments, ' ') || ''';' AS view_comment,
            +
        'COMMENT ON COLUMN ' || a.fact_view || '.' || a.simple_col_name || ' IS ''' || coalesce(ucc.comments, ' ') || ''';' AS column_comment,
            +
        a.ordinal_position 
            +
 FROM  (SELECT DISTINCT
            +
               map.fact_view,
            +
               (c.table_name || '.' || c.column_name) || (CASE WHEN map.suffix IS NULL THEN '' ELSE (' AS ' || c.column_name || '_' || map.suffix) END) AS co
lumn_name
               (CASE WHEN map.suffix IS NULL THEN c.column_name ELSE (c.column_name || '_' || map.suffix) END) AS simple_col_name,
            +
               CAST(c.ordinal_position AS VARCHAR) AS ordinal_position
            +
        FROM   information_schema.columns c
            +
               INNER JOIN (SELECT fact_view, has_tsv, tsv_table AS table_name, tsv_key AS key_col, tsv_period_key AS period_key_col, suffix

Environment

Clarity 16.3.3 with PostgreSQL

Cause

DE176575 

Resolution

In review by Engineering, targeted for 16.4.0

Workaround:

• Remove the query on database end and re-run DWH job