Sweep approval policy violations that stay running
What does this MR do and why?
Adds a cron sweep for merge request approval policy violations that stay running after the head pipeline has finished. A running row keeps the security_policy_violations merge check in checking and suppresses the policy bot comment. Every path that re-evaluates a merge request starts from a pipeline event, so nothing picks these rows up again.
Details
GitLab.com has about 18,000 running rows not updated in over 7 days, about 4,600 of them on open merge requests (#617804). Known causes:
- Merge requests with an empty latest diff (#638061, fixed separately).
- Rows written after the merge, after
CleanupMergeRequestViolationsWorkerhas already run.
What this MR adds:
- Index: a partial index
idx_scan_result_policy_violations_running_updated_at_idon(updated_at, id) WHERE status = 0(running), added in a post-deploy migration. - Worker:
SweepStaleRunningViolationsWorker. It runs every 30 minutes and walks rows not updated for over 1 hour, in batches of 100, up to 1,000 rows per run. It handles each merge request once per run:- Merged or closed merge request: deletes its violations, like
CleanupMergeRequestViolationsWorkerdoes. - Open merge request with a complete head pipeline: enqueues
UnblockPendingMergeRequestViolationsWorkerfor that merge request. That marksrunningrowsskipped, which fails closed, then re-evaluates. - Anything else is left alone.
- Merged or closed merge request: deletes its violations, like
- Resume cursor: when a run hits the cap, it saves
(updated_at, id)in Redis and the next run resumes from there. Without it, rows the sweep has to leave alone (flag off, pipeline still running) would fill every run's budget. - Timeout worker argument:
UnblockPendingMergeRequestViolationsWorker#performtakes an optionalparamshash with'merge_request_id'. When it is set, the worker loads that open merge request instead of looking up merge requests by head SHA. Its self-reschedule only passes params when there are any. The argument is optional and only the flagged sweep passes it, so old and new Sidekiq nodes stay compatible during deploy.- Head pipeline guard: when
'merge_request_id'is set, the worker only acts if the merge request's current head pipeline is the queued one, so a newer pipeline is never timed out on the older one's behalf. The check runs at perform time, so it also covers reschedules.
- Head pipeline guard: when
Feature flag and logging:
- Behind the
gitlab_com_deriskflagsweep_stale_running_policy_violations(default off), rollout issue: #638431. The actor is the merge request's target project, checked per row. - No changelog: the flag is off by default.
- One
approval_policy_evaluationlog line per merge request acted on. The per-run counts are in the job's done log (extra.result).
Reviewable LOC: 165 (plus 212 in tests)
References
Screenshots or screen recordings
Not applicable, backend only.
How to set up and validate locally
- Create an MR with a
scan_findingpolicy and let its pipeline finish. In a Rails console, force a stale row:v = mr.scan_result_policy_violations.first v.update_columns(status: :running, updated_at: 2.hours.ago) - Run
Security::ScanResultPolicies::SweepStaleRunningViolationsWorker.new.perform. - With the flag on, the timeout runs for the MR and the row gets a verdict. With
Feature.disable(:sweep_stale_running_policy_violations), the row staysrunning. - For a merged MR with a stale row, the sweep deletes the row.
Specs:
$ bin/rspec ee/spec/workers/security/scan_result_policies/sweep_stale_running_violations_worker_spec.rb ee/spec/workers/security/scan_result_policies/unblock_pending_merge_request_violations_worker_spec.rb
52 examples, 0 failures
$ bin/rspec ee/spec/models/security/scan_result_policy_violation_spec.rb # with the above
174 examples, 0 failuresDatabase review
- Table size:
scan_result_policy_violationsistable_size: smallindb/docs, so the index usesadd_concurrent_indexin a post-deploy migration. Migrated up and down locally (0.16 s up, 0.07 s down). - Query shape:
- The keyset iterator scans
WHERE status = 0 AND updated_at < ?on the partial index. - Per batch: one
MergeRequest.id_in(...)lookup. - Per merge request: target project, latest diff and head pipeline (CI database), about 3–4 queries, at most 1,000 merge requests per run.
- Deletes for merged or closed merge requests use the existing
merge_request_idindex.
- The keyset iterator scans
- Data deletion:
delete_allremoves all violations of a merged or closed merge request, the same asCleanupMergeRequestViolationsWorkeron merge. Those rows don't affect anything after the merge, so they don't need to be recovered. - Rows the sweep can't resolve: an open merge request with no head pipeline, or whose running rows have no matching approval rule, stays
running. The sweep revisits it every pass, but the cursor keeps it from blocking other rows.
Raw SQL (as Rails runs it; the sweep walks running_updated_before(1.hour.ago) with Gitlab::Pagination::Keyset::Iterator, of: 100, at most 1,000 rows per run):
-- Scope: running_updated_before
SELECT "scan_result_policy_violations".* FROM "scan_result_policy_violations" WHERE "scan_result_policy_violations"."status" = 0 AND "scan_result_policy_violations"."updated_at" < '<now - 1 hour>' ORDER BY "scan_result_policy_violations"."updated_at" ASC, "scan_result_policy_violations"."id" ASC;
-- Keyset batch 1 (no cursor)
SELECT "scan_result_policy_violations".* FROM "scan_result_policy_violations" WHERE "scan_result_policy_violations"."status" = 0 AND "scan_result_policy_violations"."updated_at" < '<now - 1 hour>' ORDER BY "scan_result_policy_violations"."updated_at" ASC, "scan_result_policy_violations"."id" ASC LIMIT 100;
-- Keyset batch N, and the first batch of a run that resumes from the Redis cursor
SELECT "scan_result_policy_violations".* FROM "scan_result_policy_violations" WHERE "scan_result_policy_violations"."status" = 0 AND "scan_result_policy_violations"."updated_at" < '<now - 1 hour>' AND (("scan_result_policy_violations"."updated_at", "scan_result_policy_violations"."id") > ('<cursor_updated_at>', <cursor_id>)) ORDER BY "scan_result_policy_violations"."updated_at" ASC, "scan_result_policy_violations"."id" ASC LIMIT 100;Query plans (production-size clone of the main database, 100-row batches, updated_at bound = 1 hour ago; the cursor values in the resumed batch are arbitrary):
| Query | Plan | Execution | Buffers | Console |
|---|---|---|---|---|
| Batch 1, without the new index | Parallel Seq Scan, then top-N sort, 2 workers, about 1M rows scanned | 3.47 s (cold) | 19,598 hit, 98,042 read | plan |
Batch 1, with idx_scan_result_policy_violations_running_updated_at_id |
Index Scan on the partial index, no sort, 100 rows | 1.0 ms (cold) | 70 hit, 31 read | plan |
| Resumed batch (row comparison on the cursor), with the index | Index Scan on the partial index, no sort, 100 rows | 2.2 ms (cold) | 61 hit, 40 read | plan |
The index was created on the clone with CREATE INDEX (0.7 s) followed by ANALYZE.
- Without the index, the scan is a sequential scan of the whole table (about 118,000 buffers) on every batch. A 30-minute cron would run up to 10 of these per run, so the index is required, not optional.
- With the index, both the first batch and the resumed batch are a single Index Scan on the partial index. It satisfies the
status = 0filter, theupdated_atbound and theORDER BY updated_at, id, with no sort node and about 100 buffers per batch. - Per-batch
MergeRequest.id_in(...)lookup: primary-key lookup onmerge_requests, so no separate plan.
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.
Context for LLM agents
- Rejected: enqueuing the repair from the
security_policy_violationsmerge check. It would add Sidekiq work to a read path that runs on every merge request view. - Rejected: a post-deploy migration that resyncs existing rows once. It doesn't cover rows created later by unknown causes.
- Rejected: a background operation (batched background migration). Each row needs merge request state and the head pipeline from the CI database, so this isn't a pure SQL purge.
- The timeout path fails closed (requires approval) before re-evaluating, the same as the existing 150 s timeout, so the sweep never unblocks a merge request without a verdict.
- The cron entry is in
ee/config/schedule.yml, where EE cron defaults now live.