Scenario
When logging into the system, the size of the pr_index_operators table expands with entries corresponding to the number of access groups assigned. This causes the table size to grow significantly, reaching millions of records. As a result, running the Report Definition List Operator with Admin Access leads to timing out of report.
Exception in logs
Following is the exception printed in the log file:
org.postgresql.util.PSQLException: ERROR: canceling statement due to user request
Database operation took more than the threshold of 500 ms: 30,100 ms SQL: SELECT DISTINCT "PC0"."pyuseridentifier" AS "pyUserIdentifier", "PC0"."pyaccessgroup" AS "pyAccessGroup", "PC0"."pyusername" AS "pyUserName", "PC0"."pyopavailable" AS "pyOpAvailable", "PC0"."pzinskey" AS "pzInsKey", "PC0"."pyposition" AS "pyPosition" FROM pegadata.pr_operators "PC0" INNER JOIN pegadata.pr_index_operators "ADDAG" ON (((("PC0"."pyuseridentifier" = "ADDAG"."pylabel" AND "PC0"."pxobjclass" = ?) AND "ADDAG"."pxobjclass" = ?))) INNER JOIN pegadata.pr_data_admin "AG" ON (((("ADDAG"."pyaccessgroupadditional" = "AG"."pyaccessgroup" AND "AG"."pxobjclass" = ?) AND "ADDAG"."pxobjclass" = ?))) WHERE (LOWER("PC0"."pyaccessgroup") NOT LIKE ? AND "AG"."pydefaultappname" = ? AND "AG"."pydefaultappversion" = ?)
Explanation
The pr_index_operators table contains rows that correspond to instances of the Index-Operator class which links you to access groups. The pyAccessGroupsAdditional property is associated with a Rule-Declare-Index (AdditionalAccessGroup) that populates the pr_index_operators data table. Modifying these properties can trigger the declare index, potentially increasing the table count or causing duplicates.
The reason for duplicate entries in the pr_index_operators table is as follows:
A merge statement on the index table when it is not committed to the database, remains in the deferred operations queue. Subsequently, rebuilding the indexes which happened after the merge removes all entries from the index table.
When the declare index triggers again with a new merge statement, while the first merge statement is still in the deferred operations queue, this issue occurs. That is, the new merge statement for the same work object causes duplicate entries in the index table.
Environments
The problem was reported in the following Pega Platform environments:
- Pega Platform 8.5.3
- Pega Platform 8.6.4
- Pega Platform 8.7.4
- Pega Platform 8.8.4
Solution
In the custom SSO logic, include a Property-Set step on the page to set the .pzReindex property to true. This makes sure that when the engine saves the operator record after the login activity, reindexing of the property is proper, and it triggers the declare index value list.
In the post-authentication activity, add steps to perform an Obj-Open on the operator record, update the .pzReindex property to true, and commit the operator record.
Best Practice
Review the custom SSO logic in the lower environment, before implementing it in production environment.
Related content
• Configuring SSO login authentication
• Declare Index Rule details