Backfill triage and remediation coverage columns

Gal Katzrequested to merge
gkatz_backfill_triage_remediation_coverage into master

What does this MR do and why?

Adds backfills of triage_capabilities_on/triage_capabilities_auto on security_inventory_filters for projects with a triage-and-remediation (or dependency_scanning_post_processing) scan profile attached before the recompute-on-write hooks existed.

Batches over security_scan_profiles_projects and fully recomputes each project's bitmasks columns.

Changelog: added
EE: true

Related issue

#629986 (closed)

Query plans

winning_trigger_rows

Raw SQL 
SELECT "security_scan_profiles_projects"."project_id",
       "security_scan_profile_triggers"."trigger_type",
       "security_scan_profile_triggers"."security_scan_profile_configuration_id"
FROM "security_scan_profile_triggers"
INNER JOIN "security_scan_profiles"
  ON "security_scan_profiles"."id" = "security_scan_profile_triggers"."security_scan_profile_id"
INNER JOIN "security_scan_profiles_projects"
  ON "security_scan_profiles_projects"."security_scan_profile_id" = "security_scan_profiles"."id"
WHERE "security_scan_profiles"."deleted_at" IS NULL
  AND "security_scan_profiles"."scan_type" = 1
  AND "security_scan_profile_triggers"."trigger_type" IN (0, 1, 12, 13)
  AND "security_scan_profiles_projects"."project_id" IN (70472537, 82231679, 66212496)
ORDER BY "security_scan_profile_triggers"."security_scan_profile_id" ASC
Query plan

see - https://console.postgres.ai/gitlab/projects/gitlab-production-sec/sessions/58965/commands/164113

 Sort  (cost=25.08..25.09 rows=3 width=26) (actual time=1.768..1.770 rows=4 loops=1)
   Sort Key: security_scan_profile_triggers.security_scan_profile_id
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=47 read=1
   WAL: records=1 fpi=0 bytes=60
   I/O Timings: read=1.488 write=0.000
   ->  Nested Loop  (cost=0.98..25.06 rows=3 width=26) (actual time=1.637..1.731 rows=4 loops=1)
         Buffers: shared hit=44 read=1
         WAL: records=1 fpi=0 bytes=60
         I/O Timings: read=1.488 write=0.000
         ->  Nested Loop  (cost=0.70..22.36 rows=2 width=24) (actual time=0.055..0.134 rows=2 loops=1)
               Buffers: shared hit=33
               WAL: records=1 fpi=0 bytes=60
               I/O Timings: read=0.000 write=0.000
               ->  Index Only Scan using index_security_scan_profiles_projects_on_unique_project_profile on public.security_scan_profiles_projects  (cost=0.42..5.85 rows=5 width=16) (actual time=0.021..0.040 rows=7 loops=1)
                     Index Cond: (security_scan_profiles_projects.project_id = ANY ('{70472537,82231679,66212496}'::bigint[]))
                     Heap Fetches: 0
                     Index Searches: 3
                     Buffers: shared hit=10
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using security_scan_profiles_pkey on public.security_scan_profiles  (cost=0.28..3.30 rows=1 width=8) (actual time=0.012..0.012 rows=0.29 loops=7)
                     Index Cond: (security_scan_profiles.id = security_scan_profiles_projects.security_scan_profile_id)
                     Index Searches: 7
                     Filter: ((security_scan_profiles.deleted_at IS NULL) AND (security_scan_profiles.scan_type = 1))
                     Rows Removed by Filter: 1
                     Buffers: shared hit=23
                     WAL: records=1 fpi=0 bytes=60
                     I/O Timings: read=0.000 write=0.000
         ->  Index Scan using index_security_scan_profile_triggers_on_profile_trigger_unique on public.security_scan_profile_triggers  (cost=0.29..1.33 rows=2 width=18) (actual time=0.794..0.796 rows=2 loops=2)
               Index Cond: ((security_scan_profile_triggers.security_scan_profile_id = security_scan_profiles.id) AND (security_scan_profile_triggers.trigger_type = ANY ('{0,1,12,13}'::integer[])))
               Index Searches: 2
               Buffers: shared hit=11 read=1
               I/O Timings: read=1.488 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB'
Query ID: 8024441529165973853

upsert_all

Raw SQL 
explain INSERT INTO "security_inventory_filters"
  ("project_id", "project_name", "traversal_ids", "archived", "triage_capabilities_on", "triage_capabilities_auto")
VALUES
  (70472537, 'proj-a', '{9970,2}', FALSE, 3, 1),
  (82231679, 'proj-b', '{9970,3}', FALSE, 5, 5)
ON CONFLICT ("project_id")
DO UPDATE SET
  "triage_capabilities_on" = excluded."triage_capabilities_on",
  "triage_capabilities_auto" = excluded."triage_capabilities_auto"
RETURNING "id"
Query plan

see - https://console.postgres.ai/gitlab/projects/gitlab-production-sec/sessions/58965/commands/164139

 Insert on public.security_inventory_filters  (cost=0.00..0.03 rows=2 width=146) (actual time=25.593..30.345 rows=2 loops=1)
   Buffers: shared hit=106 read=44 dirtied=16
   WAL: records=19 fpi=13 bytes=82896
   I/O Timings: read=29.035 write=0.000
   ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=146) (actual time=1.316..1.339 rows=2 loops=1)
         Buffers: shared hit=17 read=2 dirtied=1
         WAL: records=1 fpi=0 bytes=99
         I/O Timings: read=1.145 write=0.000
Settings: seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5'
Query ID: -4484519592714636568

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.

Merge request reports