Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

PR_INDEX_REFERENCE primary key violation

SA-32228

Summary



On startup of Master for Agent a primary key violation error is thrown on PR_INDEX_REFERENCE in PegaRULES log file. This is an upgraded environment.


Error Messages



DatabaseException caused by prior exception: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (YOUR_RULESCHEMA.PR_INDEX_REFERENCE_PK) violated

| SQL Code: 1 | SQL State: 23000

From: (B2D25310876E5CA9EFC1B01D3EC090683:(Master For New Agents))
SQL: INSERT INTO YOUR_RULESCHEMA.pr_index_reference (pzInsKey , pzTenantId , pxCommitDateTime , "PXCREATEDATETIME" , "PXCREATEOPERATOR" , "PXCREATEOPNAME" , "PXCREATESYSTEMID" , "PXINDEXCOUNT" , "PXINDEXPURPOSE" , "PXINSINDEXEDCLASS" , "PXINSINDEXEDKEY" , "PXINSNAME" , "PXJAVACLASS" , "PXJAVAMETHODNAME" , "PXOBJCLASS" , "PXPAGENAME" , "PXREFERENCINGDESCRIPTION" , "PXREFERENCINGINSNAME" , "PXREFERENCINGRULESETNAME" , "PXREFERENCINGRULESETVERSION" , "PXRULECLASSNAME" , "PXRULEFAMILYNAME" , "PXRULEOBJCLASS" , "PXSAVEDATETIME" , "PXUPDATEDATETIME" , "PXUPDATEOPERATOR" , "PXUPDATEOPNAME" , "PXUPDATESYSTEMID" , "PYLABEL" , "PYRULENAME") VALUES (? , COALESCE((SELECT MAX(pzTenantId) FROM DEV_PEGASAND_RULES718.pr_index_reference WHERE PZINSKEY = ? GROUP BY (PZINSKEY)), 'shared') , CURRENT_TIMESTAMP , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)
SQL Inserts: <INDEX-REFERENCE DATA-AGENT-QUEUE PEGA-PROCOM!3C7DB6FEF39C2293E80D16745A953BEE!10!RULEREFERENCE> <INDEX-REFERENCE DATA-AGENT-QUEUE PEGA-PROCOM!3C7DB6FEF39C2293E80D16745A953BEE!10!RULEREFERENCE> <<null>> <<null>> <<null>> <<null>> <10> <RuleReference> <Data-Agent-Queue> <DATA-AGENT-QUEUE PEGA-PROCOM!3C7DB6FEF39C2293E80D16745A953BEE> <DATA-AGENT-QUEUE PEGA-PROCOM!3C7DB6FEF39C2293E80D16745A953BEE!10!RULEREFERENCE> <<null>> <<null>> <Index-Reference> <<null>> <<null>> <PEGA-PROCOM!3C7DB6FEF39C2293E80D16745A953BEE> <<null>> <<null>> <<null>> <<null>> <Rule-Obj-Activity> <<null>> <<null>> <<null>> <<null>> <<null>> <<null>> <GetConvertedPDFsFromPDM>

Caused by SQL Problems.
Problem #1, SQLState 23000, Error code 1: com.ibm.websphere.ce.cm.DuplicateKeyException: ORA-00001: unique constraint (YOUR_RULESCHEMA.PR_INDEX_REFERENCE_PK) violated


Steps to Reproduce



Not Applicable


Root Cause



A defect in Pegasystems’ code or rules.

Resolution



Perform the following local-change:
Follow the instructions below to clean up your system of old rules and shrink the pr_index_reference table.

1. Use the cleanup.bat/cleanup.sh script available in the ../scripts dir of each the media to generate SQL that deletes all of the old PRPC rules. All rules prior to the current major release will be deleted. The generated script can be cut and pasted into SQL dev. An example is included below.

2. Using SQL dev or a similar application, run the generated script to delete the old PRPC rules. Issue the commit command after the deletion.

4. Truncate the <rules schema>.pr_index_reference table and rebuild all indexes for this table. See example below
- truncate table <rules schema>."PR_INDEX_REFERENCE";
- alter index <rules schema>."INDEX_REFPORT" rebuild // do this for each index

5. Rebuild the pr_index_reference table using prpcUtils. PrpcUtils is a script available with every build and can be found in the ../scripts dir.
- In the .../scripts dir update the prpcUtils.properties file.
- Set the DB connection and schema details as appropriate for your system. See example below
- Update the "Settings for Expose Tool" section to include the Rule- class and its descendants. See example below.
- Run prpcUtils.bat or prpcUtils.sh with the expose option - For example. prpcUtils.bat expose
This will resave all of the rules in the system triggering a rebuild of the pr_index_reference data. This will take about 30 minutes.

6. Rebuild the stats on <rules schema>.pr_index_reference table.

Suggest Edit

Published March 26, 2017 - Updated October 8, 2020

Did you find this content helpful? Yes No

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us