Load merge request via merge request links
What does this MR do and why?
When the :deprecate_vulnerabilities_feedback:load_merge_request_via_links feature flag is enabled then this change will load the associated merge request for the PipelineSecurityReportFinding GraphQL type via Vulnerabilities::MergeRequestLink.
The generated SQL query:
SELECT
"vulnerability_merge_request_links"."id" AS t0_r0,
"vulnerability_merge_request_links"."vulnerability_id" AS t0_r1,
"vulnerability_merge_request_links"."merge_request_id" AS t0_r2,
"vulnerability_merge_request_links"."created_at" AS t0_r3,
"vulnerability_merge_request_links"."updated_at" AS t0_r4,
"vulnerability"."id" AS t1_r0,
"vulnerability"."milestone_id" AS t1_r1,
"vulnerability"."epic_id" AS t1_r2,
"vulnerability"."project_id" AS t1_r3,
"vulnerability"."author_id" AS t1_r4,
"vulnerability"."updated_by_id" AS t1_r5,
"vulnerability"."last_edited_by_id" AS t1_r6,
"vulnerability"."start_date" AS t1_r7,
"vulnerability"."due_date" AS t1_r8,
"vulnerability"."last_edited_at" AS t1_r9,
"vulnerability"."created_at" AS t1_r10,
"vulnerability"."updated_at" AS t1_r11,
"vulnerability"."title" AS t1_r12,
"vulnerability"."title_html" AS t1_r13,
"vulnerability"."description" AS t1_r14,
"vulnerability"."description_html" AS t1_r15,
"vulnerability"."start_date_sourcing_milestone_id" AS t1_r16,
"vulnerability"."due_date_sourcing_milestone_id" AS t1_r17,
"vulnerability"."state" AS t1_r18,
"vulnerability"."severity" AS t1_r19,
"vulnerability"."severity_overridden" AS t1_r20,
"vulnerability"."confidence" AS t1_r21,
"vulnerability"."confidence_overridden" AS t1_r22,
"vulnerability"."resolved_by_id" AS t1_r23,
"vulnerability"."resolved_at" AS t1_r24,
"vulnerability"."report_type" AS t1_r25,
"vulnerability"."cached_markdown_version" AS t1_r26,
"vulnerability"."confirmed_by_id" AS t1_r27,
"vulnerability"."confirmed_at" AS t1_r28,
"vulnerability"."dismissed_at" AS t1_r29,
"vulnerability"."dismissed_by_id" AS t1_r30,
"vulnerability"."resolved_on_default_branch" AS t1_r31,
"vulnerability"."present_on_default_branch" AS t1_r32,
"vulnerability"."detected_at" AS t1_r33,
"vulnerability_occurrences"."id" AS t2_r0,
"vulnerability_occurrences"."created_at" AS t2_r1,
"vulnerability_occurrences"."updated_at" AS t2_r2,
"vulnerability_occurrences"."severity" AS t2_r3,
"vulnerability_occurrences"."confidence" AS t2_r4,
"vulnerability_occurrences"."report_type" AS t2_r5,
"vulnerability_occurrences"."project_id" AS t2_r6,
"vulnerability_occurrences"."scanner_id" AS t2_r7,
"vulnerability_occurrences"."primary_identifier_id" AS t2_r8,
"vulnerability_occurrences"."project_fingerprint" AS t2_r9,
"vulnerability_occurrences"."location_fingerprint" AS t2_r10,
"vulnerability_occurrences"."uuid" AS t2_r11,
"vulnerability_occurrences"."name" AS t2_r12,
"vulnerability_occurrences"."metadata_version" AS t2_r13,
"vulnerability_occurrences"."raw_metadata" AS t2_r14,
"vulnerability_occurrences"."vulnerability_id" AS t2_r15,
"vulnerability_occurrences"."details" AS t2_r16,
"vulnerability_occurrences"."description" AS t2_r17,
"vulnerability_occurrences"."message" AS t2_r18,
"vulnerability_occurrences"."solution" AS t2_r19,
"vulnerability_occurrences"."cve" AS t2_r20,
"vulnerability_occurrences"."location" AS t2_r21,
"vulnerability_occurrences"."detection_method" AS t2_r22,
"merge_requests"."id" AS t3_r0,
"merge_requests"."target_branch" AS t3_r1,
"merge_requests"."source_branch" AS t3_r2,
"merge_requests"."source_project_id" AS t3_r3,
"merge_requests"."author_id" AS t3_r4,
"merge_requests"."assignee_id" AS t3_r5,
"merge_requests"."title" AS t3_r6,
"merge_requests"."created_at" AS t3_r7,
"merge_requests"."updated_at" AS t3_r8,
"merge_requests"."milestone_id" AS t3_r9,
"merge_requests"."merge_status" AS t3_r10,
"merge_requests"."target_project_id" AS t3_r11,
"merge_requests"."iid" AS t3_r12,
"merge_requests"."description" AS t3_r13,
"merge_requests"."updated_by_id" AS t3_r14,
"merge_requests"."merge_error" AS t3_r15,
"merge_requests"."merge_params" AS t3_r16,
"merge_requests"."merge_when_pipeline_succeeds" AS t3_r17,
"merge_requests"."merge_user_id" AS t3_r18,
"merge_requests"."merge_commit_sha" AS t3_r19,
"merge_requests"."approvals_before_merge" AS t3_r20,
"merge_requests"."rebase_commit_sha" AS t3_r21,
"merge_requests"."in_progress_merge_commit_sha" AS t3_r22,
"merge_requests"."lock_version" AS t3_r23,
"merge_requests"."title_html" AS t3_r24,
"merge_requests"."description_html" AS t3_r25,
"merge_requests"."time_estimate" AS t3_r26,
"merge_requests"."squash" AS t3_r27,
"merge_requests"."cached_markdown_version" AS t3_r28,
"merge_requests"."last_edited_at" AS t3_r29,
"merge_requests"."last_edited_by_id" AS t3_r30,
"merge_requests"."head_pipeline_id" AS t3_r31,
"merge_requests"."merge_jid" AS t3_r32,
"merge_requests"."discussion_locked" AS t3_r33,
"merge_requests"."latest_merge_request_diff_id" AS t3_r34,
"merge_requests"."allow_maintainer_to_push" AS t3_r35,
"merge_requests"."state_id" AS t3_r36,
"merge_requests"."rebase_jid" AS t3_r37,
"merge_requests"."squash_commit_sha" AS t3_r38,
"merge_requests"."sprint_id" AS t3_r39,
"merge_requests"."merge_ref_sha" AS t3_r40,
"merge_requests"."draft" AS t3_r41,
"merge_requests"."prepared_at" AS t3_r42
FROM "vulnerability_merge_request_links"
INNER JOIN "vulnerabilities" "vulnerability" ON "vulnerability"."id" = "vulnerability_merge_request_links"."vulnerability_id"
INNER JOIN "vulnerability_occurrences" ON "vulnerability_occurrences"."vulnerability_id" = "vulnerability"."id"
LEFT OUTER JOIN "merge_requests" ON "merge_requests"."id" = "vulnerability_merge_request_links"."merge_request_id"
WHERE "vulnerability_occurrences"."uuid" = 'e97994c1-a014-51be-954b-2e3d38c5df46'; Nested Loop Left Join (cost=1.85..10.93 rows=1 width=2756) (actual time=19.697..38.803 rows=4 loops=1)
Buffers: shared hit=15 read=20
I/O Timings: read=38.555 write=0.000
-> Nested Loop (cost=1.28..7.34 rows=1 width=1923) (actual time=14.480..16.624 rows=4 loops=1)
Buffers: shared hit=5 read=10
I/O Timings: read=16.464 write=0.000
-> Nested Loop (cost=1.14..7.17 rows=1 width=1887) (actual time=14.457..14.460 rows=1 loops=1)
Buffers: shared hit=3 read=7
I/O Timings: read=14.342 write=0.000
-> Index Scan using index_vulnerability_occurrences_on_uuid on public.vulnerability_occurrences (cost=0.57..3.58 rows=1 width=1518) (actual time=8.942..8.944 rows=1 loops=1)
Index Cond: ((vulnerability_occurrences.uuid)::text = 'e97994c1-a014-51be-954b-2e3d38c5df46'::text)
Buffers: shared hit=1 read=4
I/O Timings: read=8.864 write=0.000
-> Index Scan using vulnerabilities_pkey on public.vulnerabilities vulnerability (cost=0.57..3.58 rows=1 width=369) (actual time=5.508..5.508 rows=1 loops=1)
Index Cond: (vulnerability.id = vulnerability_occurrences.vulnerability_id)
Buffers: shared hit=2 read=3
I/O Timings: read=5.478 write=0.000
-> Index Scan using unique_vuln_merge_request_link_vuln_id_and_mr_id on public.vulnerability_merge_request_links (cost=0.14..0.16 rows=1 width=36) (actual time=0.019..2.155 rows=4 loops=1)
Index Cond: (vulnerability_merge_request_links.vulnerability_id = vulnerability.id)
Buffers: shared hit=2 read=3
I/O Timings: read=2.122 write=0.000
-> Index Scan using merge_requests_pkey on public.merge_requests (cost=0.57..3.59 rows=1 width=833) (actual time=5.541..5.541 rows=1 loops=4)
Index Cond: (merge_requests.id = vulnerability_merge_request_links.merge_request_id)
Buffers: shared hit=10 read=10
I/O Timings: read=22.091 write=0.000https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/17944/commands/59709
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/18066/commands/59942
#404894 (closed) #409018 (closed)
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by mo khan