Recount blocking issues counts left wrong

What does this MR do and why?

Before !265408 (merged), deleting an open blocked issue left the issues.blocking_issues_count of each issue blocking it too high: its issue_links rows go through the ON DELETE CASCADE on target_id, which runs no callback. GraphQL blockingCount, the work items REST API's blocking_count and the BLOCKING_ISSUES_* sorts read the column, so those counts stay wrong until something recounts them.

This MR adds the batched background migration agreed in that review. RecountBlockingIssuesCount walks issues by id and recounts every issue of each sub-batch the way IssueLink.blocking_issuables_for_collection does: the open issues it blocks, or 0 when no blocks link is left. One statement per sub-batch updates only the rows whose count differs, and leaves updated_at alone, as refresh_blocking_and_blocked_issues_cache! does.

Closing or reopening a blocked issue also leaves counts wrong in either direction, since refresh_blocking_and_blocked_issues_cache! recounts only its open blockers. On a production clone that causes most wrong counts. This migration repairs them, closed issues included, and !269286 (merged) fixes that path first.

Blocking issues are EE-only, so the FOSS class is a no-op and the EE module does the work. The migration is for 19.6. The job's examples fail without the EE module and pass with it; each of six mutations of the query fails at least one.

What I ran (Ruby 3.3.11)

On master at a8f1a2df with this branch:

• the job spec with the EE module removed, so perform is the FOSS no-op: 9 examples, 7 failures, every example that expects a count to change;
• the job spec and the queueing migration spec with the change: 10 examples, 0 failures;
• six mutations of the statement, each failing at least one example: no <> guard (the examples that check by ctid that a right count is not written), INNER JOIN for the LEFT JOIN (the counts with no link left), counting closed issues, counting relates_to links, skipping counts of 0 as a previous version did (the count of 0 left too low), and recounting open issues only (the closed blockers);
• RuboCop on the five Ruby files, Danger's commit linter on the six commits and the commit message linter: no offenses.

Database: query and cost

Batching is the default cursor on issues.id, with no scope: every issue of a sub-batch is read and recounted, and only the rows whose count differs are written. After the framework's query for its bounds, each sub-batch sends one statement, captured from a test run of the job (here with the ids of a later sub-batch of the batch ending at id 510000):

WITH sub_batch AS MATERIALIZED (
  SELECT "issues"."id", "issues"."blocking_issues_count" FROM "issues" WHERE ("issues"."id") <= (510000) AND ("issues"."id" > 503000) ORDER BY "issues"."id" ASC LIMIT 1000
),
blocking_counts AS (
  SELECT issue_links.source_id AS blocking_issue_id,
    COUNT(CASE WHEN issues.state_id = 1 THEN 1 ELSE NULL END) AS count
  FROM issue_links
  INNER JOIN issues ON issues.id = issue_links.target_id
  WHERE issue_links.link_type = 1
    AND issue_links.source_id IN (SELECT id FROM sub_batch)
  GROUP BY issue_links.source_id
)
UPDATE issues
SET blocking_issues_count = COALESCE(blocking_counts.count, 0)
FROM sub_batch
LEFT JOIN blocking_counts ON blocking_counts.blocking_issue_id = sub_batch.id
WHERE issues.id = sub_batch.id
  AND sub_batch.blocking_issues_count <> COALESCE(blocking_counts.count, 0)

From the database review, on a production clone: recounting the whole sub-batch adds about 30 ms per 1,000 ids on a cold cache, most of the sub-batch's time is reading it through issues_pkey, and the migration takes about 20 days on GitLab.com at the queued batch size (10,000 ids every 2 minutes), less once the batch optimizer raises it.

The column is in index_issue_on_project_id_state_id_and_blocking_issues_count, so these updates are not HOT; only rows whose count differs are written, and no trigger on issues fires on this column.

Rollback: every count written is recomputed from issue_links, the value the application writes when a link changes, so there is nothing to restore; down deletes the queued migration. If the recount were wrong, an issue would show a wrong blocking count, and sort by it, until its next link change.

References

Closes #636694 (closed)

• #636333 (closed) and !265408 (merged) refresh the counts on delete from now on.
• !37368 (merged) added the column.

I found this while working on gitlab-mcp-server, which reads blocking_issues_count through the REST API.

Screenshots or screen recordings

Not applicable: no UI change.

How to set up and validate locally

1. On Premium or Ultimate, make issue A block an open issue B, then in the Rails console leave A's count wrong: Issue.find(<A id>).update_column(:blocking_issues_count, 3) (or 0).

2. Run bin/rails db:migrate, which queues the migration, then run it inline in the console:

   migration = Gitlab::Database::BackgroundMigration::BatchedMigration.find_by!(job_class_name: 'RecountBlockingIssuesCount')
   Gitlab::Database::BackgroundMigration::BatchedMigrationRunner.new(connection: ApplicationRecord.connection).run_entire_migration(migration)

3. Issue.find(<A id>).blocking_issues_count is now 1, as is A's blockingCount in GraphQL.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.