PostgreSQL - Unable to audit an OBS field, when selected, error 500 on project update, logs show "null value in column "transaction_id" violates not-null constraint"

book

Article ID: 197093

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

We are trying to add an OBS field for Audit. When we do that, we are able to save it correctly for audit, but then once we try saving the project, we get error 500. 

Error in Clarity UI: "ERROR Error 500 - Internal Server Error. The server could not retrieve the document due to server-configuration or other technical problems. Contact your site administrator."

Error in New UX: “An exception occurred while trying to update an object instance project” 

Error in logs:

ERROR 2020-08-10 05:43:09,520 [http-nio-8080-exec-62] service.AttributeWriteService (clarity:XXXXXXXXXX:session:PPM_REST_API) Cannot write OBS exception:
com.niku.union.persistence.PersistenceException: 
SQL error code: 0
Error message: ERROR: null value in column "transaction_id" violates not-null constraint
  Detail: Failing row contains (10667302, project, 5094066, odf_xxxxxxxxx, SRM_PROJECTS, , null, /OBS1/IT/Sales So..., null, 5030085, U, 2020-08-10 05:43:09.502, 5016856, 2020-08-10 05:43:09.502, 5016856, null).
  Where: SQL statement "INSERT INTO CMN_AUDITS( ID, OBJECT_CODE, OBJECT_ID,  ATTRIBUTE_CODE,  TABLE_NAME,  COLUMN_NAME, VALUE_BEFORE ,
                  VALUE_AFTER, RAW_VALUE_BEFORE, RAW_VALUE_AFTER, OPERATION_CODE, CREATED_DATE, CREATED_BY, LAST_UPDATED_DATE, LAST_UPDATED_BY, TRANSACTION_ID )
        SELECT nextval('cmn_audits_s1'), V_OBJECT_CODE, V_OBJECT_ID, V_ATTRIBUTE_CODE, NEW.table_name, '',null, OBS_UNIT_FULL_PATH(NEW.unit_id) , null, NEW.unit_id, 'U', V_LAST_UPDATED_DATE, V_LAST_UPDATED_BY, V_LAST_UPDATED_DATE, V_LAST_UPDATED_BY, V_TRANSACTION_ID"
PL/pgSQL function trigger_fct_audit_obs_attributes() line 160 at SQL statement

Environment

Release : 15.8.1

Component : CA PPM SAAS STUDIO

Resolution

This is logged as DE56551, fixed in 15.8.1.1

Workaround:

1. On the database, edit the trigger function: trigger_fct_audit_obs_attributes

2. Modify the line:

SELECT txid_current_if_assigned() INTO STRICT V_TRANSACTION_ID;

To:

SELECT txid_current() INTO STRICT V_TRANSACTION_ID;

3. Run and compile the function  (See attached example SQL)

4. Once done, you can go ahead and add your OBS attributes safely.

 

Note: the function is not automatically recreated on adding audit fields, so adding and removing fields after applying the workaround does not affect the resolution and keeps the desired behavior. 

Attachments

1601059806617__trigger_fct_audit_obs_attributes.sql get_app