PostgreSQL 16.2.3 upgrade fails on PREUPGRADE_KEY_RESULTS_VALUE_TYPE.xml with error ERROR: type of parameter 39 (numeric) does not match that when preparing the plan (bigint)
Article ID: 376283
Updated On:
Products
Issue/Introduction
Upgrade to 16.2.3 fails with Error Message:
2024-08-30 10:24:02.667 IST Process - pre-upgrade: PREUPGRADE_KEY_RESULTS_VALUE_TYPE.xml
2024-08-30 10:24:02.867 IST org.postgresql.util.PSQLException: ERROR: type of parameter 39 (numeric) does not match that when preparing the plan (bigint)
2024-08-30 10:24:02.868 IST Where: PL/pgSQL function trigger_fct_t_okr_key_result_o_key_results() line 69 at IF
2024-08-30 10:24:02.868 IST SQL statement "UPDATE OKR_KEY_RESULTS SET CURRENT_VALUE_BACKUP = CURRENT_VALUE WHERE CURRENT_VALUE IS NOT NULL"
2024-08-30 10:24:02.868 IST PL/pgSQL function inline_code_block line 17 at SQL statement
2024-08-30 10:24:02.868 IST at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
2024-08-30 10:24:02.868 IST at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2412)
2024-08-30 10:24:02.868 IST at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:371)
Environment
Clarity 16.2.3 with PostgreSQL
Cause
DE153727
Resolution
Caused by DE153727, fix provided in 16.2.3.release-overrides.11.zip, available on Broadcom Support site for download
Workaround:
1. Remove Audit fields on OKR Result Object in Object - Studio
2. Create a new backup table with the values
CREATE TABLE okr_key_results_bkp AS
SELECT id, START_VALUE, CURRENT_VALUE, TARGET_VALUE FROM okr_key_results
3. Set fields to null in OKR_KEY_RESULTS table:
update okr_key_results set START_VALUE = null, CURRENT_VALUE = null, target_value = null
4. Run the upgrade. After the upgrade completes, run the query:
UPDATE OKR_KEY_RESULTS AS orig
SET START_VALUE = backup.START_VALUE, CURRENT_VALUE = backup.CURRENT_VALUE, TARGET_VALUE = backup.TARGET_VALUE
FROM OKR_KEY_RESULTS_BKP AS backup
WHERE orig.id = backup.id
Make sure the values are back in okr_key_results table
5. Put the below fields back in Audit Trail on all 3 Selection:
Current Value,
Key Result Type,
Owner,
Start,
Start Value,
Status,
Target,
Target Value
Feedback
