SAAS PostgreSQL – after database refresh search_paths in multiple functions are hardcoded to prior schema name

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Upon refresh from a lower environment to another lower environment, unable to update Allocations in MUX – Project – Staff screen or unable to add “Show Total Row” to the view on that screen)

STEPS TO REPRODUCE:

1. Request a refresh from a lower environment to another lower environment in SAAS
2. Connect to Clarity MUX – open any project – Staff tab with Per Period Metrics with Allocations
3. Try to modify any allocation
4. Now go to View Options and try to enable Show Total Row
5. Now open the same project in Classic and go on the Team table

Expected Results: The allocation and view to update correctly, Team tab in Classic to display correctly

Actual Results: Error messages:

• In Classic, no data on Team tab and error Sorting by the column specified is not allowed.
•  In MUX on updating Per Period Metrics - Allocation, error is thrown: "exception processing NkCurve attribute: System error. Contact system administrator."
• On trying to Show Total Row, the error is: "Could not load total row"

In logs we can see errors on multiple functions:

ERROR 2025-05-13 16:33:22,683 [http-nio-80-exec-2] niku.xql2 (clarity:admin:6316438__3FF354B6-70A2-4DC6-8EDB-850DF2DE8DE8:PPM_REST_API) (320217ed-1932-477f-9dda-0e6b971d6e85) Exception in processing

java.lang.Exception: Invalid expression qobj: com.niku.union.persistence.PersistenceException:

SQL error code: 0

Error message: ERROR: relation "prassignment" does not exist

  Where: PL/pgSQL function niku.prj_max_actthru_fct(bigint,bigint) line 9 at EXECUTE

Caused by: org.postgresql.util.PSQLException: ERROR: type "crv_period[]" does not exist

  Where: compilation of PL/pgSQL function "crv_fiscal_calendar_fct" near line 4

Environment

Clarity 16.3.1 with SAAS GCP, PostgreSQL and refresh on lower environment from a lower environment

Cause

When opening the functions, there are 18 functions with hardcoded search_path to the old schema.

cop_date_trunc_fct
cop_date_trunc_fct
cop_date_trunc_fct
cop_days_late_fct
cop_days_late_pct_fct
cop_cal_date_add_fct
cop_cal_startdate_fct
cop_calc_finish_fct
cop_calc_finish_time_fct
cop_start_days_late_fct
cop_start_days_late_pct_fct
crv_fiscal_calendar_fct
crv_read_double_fct
dwh_cal_datelabel_fct
prj_default_alloc_rate_fct
prj_effort_fct
prj_max_actthru_fct
prj_sum_assignment_fct

Resolution

Workaround: Correct the search_path or remove the search_path, recreate the function, restart the services

This is DE170682, in review with Engineering