Description

A dashboard chart grouped by Approver (or Current step) with any measure other than Approvals fails to load for tenants with many approvals or large approver groups, for example GET /forge/jira/rest/gadget/chart?dimension=APPROVER&measure=TIME_IN_STEP&aggregation=MEDIAN&statuses=ONGOING. The log shows:

org.jooq.exception.DataAccessException: SQL [with "measured_config" as materialized (select ...ERROR: canceling statement due to user request

"Canceling statement due to user request" is the chart's 10-second query timeout (ApprovalAggregationDao.AGGREGATION_TIMEOUT_SECONDS), not a database failure. In the gadget editor the preview then fails with can't access property "filter", e is undefined, because the error comes back as HTTP 200 with the error page (not changed here).

Two causes add up:

• The measure is computed for too many rows. One approval falls into several buckets (one per pending approver). Originally the measure was computed once per approver row, so for Time in step the two correlated subqueries on approval_step ran 500 times for an approval waiting on a 500-member group. Computing it once per approval in a materialized CTE fixed that, but the CTE measured every approval in scope, including the ones nobody is pending on (with no status filter, every approval of the host). Joining that CTE to the approvers also cannot run in parallel.

• Postgres estimates the approvals in scope at about one row. Deleted, archived and outdated approvals were excluded with approval_path_deleted.deleted_date is null (and the same for archived_date / changed_date) on left-joined tables. Postgres estimates these filters from the columns of those tables, where the date is never null, so it expects almost no approvals to remain. It then picks nested loops that run once per approval or once per approver row. For example, on a large tenant a chart by Approver counting Ongoing approvals scans approval_path_archived once per pending approver and does not finish within a minute.

Affected: charts by Approver or Current step with a measure (single and grouped), count charts with a status filter on large tenants, and, to a lesser degree, approval lists and personal approvals that use the same filters.

Fix

• The approvers of each approval are collected into an array with the same join the count chart uses, grouped by approval. The materialized CTE measured_config computes the measure once per approval from that per-approval array (joined by primary key), and the outer query unnests the array into buckets. A chart by Approver only measures approvals that have a pending approver. In a grouped chart, approvals without one still land in the empty bucket.

• ApprovalPathConditions excludes deleted, archived and outdated approvals with not exists (...) and matches the Archived and Outdated statuses with exists (...), instead of testing the left-joined date columns for null. The semantics are the same, and Postgres now estimates the approvals in scope correctly.

• ChartDimensionSql joins the Approver and Current step tables on approval_config.config_id only again.

• Results are unchanged; only the query shape changes. AS MATERIALIZED requires PostgreSQL 12 or newer.

Testing

• Charts by Approver and by Current step, grouped charts with Approver or Current step on either axis, personal views and count charts return the same rows as before, for every measure and with and without a status filter.

• Approval lists (any, default, Ongoing, Archived, Outdated, Expired), personal approvals, approvals of a reference, reminders, find and reference scopes return the same rows as before.

• Measured on a local copy of the schema with generated data (two data sets: host with 300,000 approvals and 2.3 million pending approver rows; host with 600,000 approvals and small groups), best of two runs: