PostgreSQL Clarity error duplicate key value violates unique constraint "cmn_sec_assgnd_obj_perm_pk"

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

A new PostgreSQL install or refresh throws this error message in Clarity logs on different actions such as updating projects or similar:

Error message:

ERROR: duplicate key value violates unique constraint "cmn_sec_assgnd_obj_perm_pk" Detail: Key (id)=(5xxxxxx) already exists.

Executed: INSERT INTO cmn_sec_assgnd_obj_perm (ID, principal_type, principal_id, object_id, object_instance_id, permission_id, right_id, created_date, created_by, last_updated_date, last_updated_by ) VALUES (?, ?,?,?, ?, ?, ?, ?, ?, ?, ? )

Fixing the sequences and restarting does not work

Resolution

1. (re)Create the PG loopback and DWH links by running the below commands one by one:
   • admin db create-db-link
   • admin db create-db-link -Dtype=app -Dsysusername=postgres -Dsyspassword=<password> --replace with the actual password.
     Note: Needs to be run with a superuser DB account
2. Restart the services