Data Warehouse job failing: ERROR: cannot alter type of a column used by a view or rule Detail: rule _RETURN on view ...

Products

• Clarity PPM SaaS

Issue/Introduction

On Postgres 16.0 it appears the Load Data Warehouse full and incremental can fail with errors similar to

 

Couldn't execute SQL: ALTER TABLE DWH_CMN_TODO ALTER COLUMN <column name> TYPE VARCHAR(4000)

In this example the column in test_notes.

ERROR: cannot alter type of a column used by a view or rule Detail: rule _RETURN on view dwh_inv_task_todo_v depends on column "test_notes" at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:236) at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62) at java.base/java.lang.Thread.run(Thread.java:829) Caused by: org.pentaho.di.core.exception.KettleDatabaseException: Couldn't execute SQL: ALTER TABLE DWH_CMN_TODO ALTER COLUMN NH_NOTES TYPE VARCHAR(4000)

 

These are custom attributes with Rich Text or Large String type.

Environment

Release : 16.0.0

Component : CLARITY DATA WAREHOUSE

Cause

This has been reported as DE63399 and has to do with Rich Text or Large String fields.

it can cause the Load Data Warehouse job to fail on both full load and incremental load.

 

Specific error will have 

ERROR: cannot alter type of a column used by a view or rule
  Detail: rule _RETURN on view ...

Resolution

On the ToDo object or other object having the issue 

 

1. Exclude the attribute from the data warehouse

      

You can also try 

1. Excluding the attribute from DWH

2. Running Full Load

3. Including the attribute back

4. Running Full Load

 

If the job continues to fail, keep the attribute excluded from the DWH.

Once the defect is resolved the field can be included back