[midPoint-git] [Evolveum/midpoint] 4f3a10: Optimize reviewer notifications from certification

[Andrej]

  Branch: refs/heads/bugfix/4.9/10225
  Home:   https://github.com/Evolveum/midpoint
  Commit: 4f3a10b315792183f453922f5bf8ded3896f5d25
      https://github.com/Evolveum/midpoint/commit/4f3a10b315792183f453922f5bf8ded3896f5d25
  Author: Andrej Zan <andrej.zan at evolveum.com>
  Date:   2025-02-24 (Mon, 24 Feb 2025)

  Changed paths:
    M docs/diag/performance.adoc
    M infra/schema/src/main/java/com/evolveum/midpoint/schema/util/CertCampaignTypeUtil.java
    M infra/schema/src/main/java/com/evolveum/midpoint/schema/util/cases/WorkItemTypeUtil.java
    M model/certification-impl/src/main/java/com/evolveum/midpoint/certification/impl/AccCertQueryHelper.java
    M model/certification-impl/src/main/java/com/evolveum/midpoint/certification/impl/AccCertUpdateHelper.java
    M model/certification-impl/src/main/java/com/evolveum/midpoint/certification/impl/task/AccessCertificationStageManagementRun.java
    M model/certification-impl/src/test/java/com/evolveum/midpoint/certification/test/AbstractCertificationTest.java
    A model/certification-impl/src/test/java/com/evolveum/midpoint/certification/test/AccessCertificationEventListenerStub.java
    M model/certification-impl/src/test/java/com/evolveum/midpoint/certification/test/TestCertificationBasic.java
    A model/certification-impl/src/test/java/com/evolveum/midpoint/certification/test/TestCertificationNotifications.java
    M model/certification-impl/src/test/java/com/evolveum/midpoint/certification/test/TestEscalation.java
    M model/certification-impl/src/test/java/com/evolveum/midpoint/certification/test/TestManualEscalation.java
    A model/certification-impl/src/test/resources/common/certification-of-eroot-user-assignments-notifications.xml
    M model/certification-impl/src/test/resources/common/user-bob.xml
    M release-notes.adoc

  Log Message:
  -----------
  Optimize reviewer notifications from certification

**What**

Optimize and simplify the queries (and code) used to select reviewers
who should receive "required review" notifications.

**Why**

The previous implementation tended to cause opening of certification
campaign stages to hang indefinitely\*. These freezes seem to be caused
by a large number of inserts followed by more complex select.

It looks like rapid inserts causes need for the DB to do vacuum/analyze
operations, but those operations are run periodically with certain
delay. If the select operation hits the DB before the "autovacuuming"
period (called nap time) elapses, it may appear to freeze\* in the DB
without any sign of real activity.

Lowering the "autovacuum nap time" configuration option seems to
mitigate this issue. By default, this option is set to 1 minute. During
local testing, setting it to 10 seconds helped (although this was only
limited manual testing).

\*We are not sure if this freeze is just temporary and the select would
eventually finish or if it gets stuck forever.

**Notes**

Originally, the `notifyReviewers` method executed two queries. The first
query was simple and did not cause any issues. However, the second
query, which involved joins over three different tables (or in fact,
nested selects in an `exists` clause), caused problems.

After several iterations, I decided to use a single query to load all
cases with open work items, map these to reviewers in code, and then
send notifications based on that mapping. Open work items are those
whose `closeTimestamp` is null, generally meaning they belong to the
currently opened stage.

This approach has pros and cons:

**Disadvantages:**
- Loads a large number of cases into memory
- Filtering happens in code (no fancy index optimizations)

**Advantages:**
- Only one DB round trip (though technically not entirely true due to
  iterative search)
- Much simpler query
- The same cases are not read more than once for reviewers with open
  work items

Note that when a notification is sent, all of the reviewer's cases from
the current stage are included, not just those where the reviewer has
work items without replies. This is one of the reasons I chose to load
all the cases.

Regarding the iterative search, in this case it probably has only one
benefit and that is it does not have limit on number of returned items
in total. The non iterative search has limit of 10 000 records.

**Fixes**: MID-10225

(cherry picked from commit 16293b749feba5031a5aa3543988eba980e7ac0e)



To unsubscribe from these emails, change your notification settings at https://github.com/Evolveum/midpoint/settings/notifications