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

[Tony Tkáčik]

  Branch: refs/heads/master
  Home:   https://github.com/Evolveum/midpoint
  Commit: d53df1d00a077205959039535180dd10540db10f
      https://github.com/Evolveum/midpoint/commit/d53df1d00a077205959039535180dd10540db10f
  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


  Commit: 1d1a4747b3b94e1de9b94558eede7ffca9f6c774
      https://github.com/Evolveum/midpoint/commit/1d1a4747b3b94e1de9b94558eede7ffca9f6c774
  Author: Tony Tkáčik <tonydamage at users.noreply.github.com>
  Date:   2025-02-25 (Tue, 25 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:
  -----------
  Merge pull request #265 from Evolveum/bugfix/10225

Optimize reviewer notifications from certification


Compare: https://github.com/Evolveum/midpoint/compare/4dd39def6d10...1d1a4747b3b9

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