Unable to Open Projects in OWB/MSP Due to Large Number of Notes

Products

• Clarity PPM SaaS

Issue/Introduction

Tasks notes exponentially multiply when saving a project back from Open Workbench or Microsoft Project on GCP/Postgres environments. Once the tasks notes have increased to a large number, OWB/MSP is no longer able to open the affected projects. If the project does open, it opens after a very long time (15+ minutes). 

Steps to Reproduce:
0. Login to a Clarity environment using a Postgres database
1. Create a project
2. Create a task on the project
3. Export the project to OWB (or MSP)
4. In OWB (or MSP), add 1 note on the task
5. Save the project back to Clarity. Verify that there is still only 1 note on the task in Clarity
6. Export the project to OWB (or MSP). Verify that there is still only 1 note on the task in OWB/MSP
7. Save the project back to Clarity
8. In Clarity, check the number of notes on the task 

Expected Result: There is still only 1 note on the task

Actual Result: There are 2 notes on the task. The notes will exponentially multiple every time the project is opened in OWB/MSP and saved back from here on out. The 1 note will go to 2 notes then to 4... At some point when the notes increase to a very large number, this will cause OWB/MSP to no longer be able to open the project.  In OWB and MSP (legacy), the loading screen will stay stuck on a step similar to 'Project P1234 Notes 43,991 received' and will not move past this. 

Notes:

• This issue also occurs for task notes created in Clarity.
• This issue is not reproducible when using an Oracle database. 

Environment

Release : 15.8.1, 15.9

Component : PPM SAAS OPEN WORKBENCH (OWB)/MICROSOFT PROJECT (MSP)

Cause

Caused by DE58178

Resolution

DE58178 is fixed in 15.9.1

Workaround: Do not use task notes on any projects being managed with OWB or MSP. Delete any existing task notes on these projects.

If there are thousands of duplicate task notes to delete, they can be deleted from the database using a query such as the one below (this will delete ALL task notes on a particular project): 

1. Take a backup of the PRNOTE table

2. Use either of the queries to validate the project in question:

SELECT * from inv_investments WHERE code = <the project code>
or
SELECT * from inv_investments WHERE id = <the project id>

3. Once confirmed, run the following query to delete the notes:

delete from prnote
where prtablename ='PRTask'
and prrecordid in (select prid from prtask where prprojectid = <internal project id>)