Post Refresh errors in PostgreSQL on audited objects

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

After a database refresh from one instance to another instance of a clarity environment with PostgreSQL environment, multiple errors are faced in MUX UI when audits are enabled on TSV attributes  

Steps To Reproduce:

1. On source environment, enable Auditing on some object, such as the team or assignment object.
2. Set up the Team or Assignment > Audit Trail  for the following fields for insert, change, delete operations: ETC
   Note: ETC will enable TSV audit
3. Make changes on the object instance to track audit activity such as insert, update, delete.
4. Check the Administration > Data Administration > Audit Trail on the team and prassignment objects do get logged.
5. Refresh the database from the source environment to a target environment.
6. On the target environment, perform an action,  such as adding a  resource to the Modern Project > Staff

Expected Results: Various errors are seen during navigating to different modules in clarity. 

Actual Results: Multiple errors are seen in the UI during navigation, below are few examples provided

• API-1007 : You are not authorized to process request. Contact your system administrator for necessary security rights.
• In viewing and accessing Staffing Workspace views, data does not load and errors seen in app ca logs 

  org.postgresql.util.PSQLException: ERROR: type "crv_period[]" does not exist Where: compilation of PL/pgSQL function "crv_fiscal_calendar_fct"
  
  Error message: ERROR: relation "prtask" does not exist
    Where: PL/pgSQL function schema.prj_effort_fct(numeric,text,text) line 36 at EXECUTE
  ERROR 2021-07-27 12:11:19,575 [http-nio-8080-exec-51] union.persistence (clarity:user:session::projmgr.teamList) Exception on odf-pmd.team.select-no-security-list-conditional.map statement null
  org.postgresql.util.PSQLException: ERROR: relation "prassignment" does not exist
    Where: PL/pgSQL function schema.prj_sum_assignment_fct(bigint,numeric) line 13 at EXECUTE

 

 

Environment

Clarity 16.2.1, 16.2.2 with PostgreSQL database only

Cause

DE80430 - The source schema is being referenced/hard-coded in the trigger/functions related to audit table operations.
 

 

 

 

Resolution

DE80430 is fixed in 16.2.3 and 16.2.2.1 (Patch 1)

Workaround: Obtain a list of all functions being incorrectly referenced and manually update the functions, referencing the current schema for functions

Additional Information

Check the target functions mentioned in the error, and see that the source schema is referenced/hard-coded.

 Example1 in target database:

CREATE OR REPLACE FUNCTION target_schema2.trigger_fct_t_assignment_pprassignment()
Line  18:  TSV_OLD_VALUE := source_schema1.NK_EXTRACT_CURVE_FCT( OLD.prExtension , 'prEstCurve' );
Line  19:  TSV_NEW_VALUE := source_schema1.NK_EXTRACT_CURVE_FCT( NEW.prExtension , 'prEstCurve' );

 Example2 in target database:

CREATE OR REPLACE FUNCTION target_schema2.trigger_fct_t_team_pprteam()
Line  76:      (nextval('CMN_AUDITS_WEU_START_FINISH_S1'), V_AUDIT_ID, source_schema2.audit_fct_team_por_pravailstart(OLD.prProjectID, OLD.prAvailStart), source_schema2.audit_fct_team_por_pravailfinish(OLD.prProjectID, OLD.prAvailStart), source_schema2.audit_fct_team_por_pravailstart(NEW.prProjectID, NEW.prAvailStart), source_schema2.audit_fct_team_por_pravailfinish(NEW.prProjectID, NEW.prAvailStart));
Line  79:      UPDATE CMN_AUDITS_WEU_START_FINISH SET after_start_date = source_schema2.audit_fct_team_por_pravailstart(NEW.prProjectID, NEW.prAvailStart), after_finish_date = source_schema2.audit_fct_team_por_pravailfinish(NEW.prProjectID, NEW.prAvailStart)