Backfill security_scan_profiles_projects archived column

What does this MR do and why?

Adds a backfill to correct security_scan_profiles_projects.archived for rows written before the column existed, based on the project or ancestor group archived value.

Changelog: added
EE: true

Related issue

Exclude archived projects from security scan pr... (#629984) • rossfuhrman • 19.6

Query plans

archived lookup

Note: Duplication of application code.

Raw SQL

SELECT
    "projects"."id",
    projects.archived
    OR EXISTS (
        SELECT
            1
        FROM
            "namespace_settings"
        WHERE
            "namespace_settings"."namespace_id" = ANY (namespaces.traversal_ids)
            AND "namespace_settings"."archived" = TRUE)
FROM
    "projects"
    LEFT OUTER JOIN "namespaces" ON "namespaces"."type" = 'Group'
    AND "namespaces"."id" = "projects"."namespace_id"
WHERE
    "projects"."id" IN (80141160, 65339032, 70472537)

Query plan

Full details here

 Nested Loop Left Join  (cost=1.13..79.23 rows=3 width=5) (actual time=17.225..25.395 rows=3 loops=1)
   Buffers: shared hit=29 read=19
   I/O Timings: read=25.241 write=0.000
   ->  Index Scan using projects_pkey on public.projects  (cost=0.56..10.09 rows=3 width=9) (actual time=7.644..15.786 rows=3 loops=1)
         Index Cond: (projects.id = ANY ('{80141160,65339032,70472537}'::integer[]))
         Buffers: shared hit=4 read=11
         I/O Timings: read=15.731 write=0.000
   ->  Index Scan using namespaces_pkey on public.namespaces  (cost=0.57..3.59 rows=1 width=32) (actual time=2.252..2.252 rows=1 loops=3)
         Index Cond: (namespaces.id = projects.namespace_id)
         Filter: ((namespaces.type)::text = 'Group'::text)
         Buffers: shared hit=11 read=4
         I/O Timings: read=6.720 write=0.000
   SubPlan 1
     ->  Index Only Scan using index_namespace_settings_on_namespace_id_where_archived_true on public.namespace_settings  (cost=0.29..19.46 rows=1 width=0) (actual time=0.947..0.947 rows=0 loops=3)
           Index Cond: (namespace_settings.namespace_id = ANY (namespaces.traversal_ids))
           Heap Fetches: 0
           Buffers: shared hit=14 read=4
           I/O Timings: read=2.789 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: -1430597538075671517

archived update

Raw SQL

UPDATE
    "security_scan_profiles_projects"
SET
    "archived" = TRUE
WHERE
    "security_scan_profiles_projects"."project_id" IN (80141160, 65339032, 70472537)
    AND "security_scan_profiles_projects"."archived" = FALSE

Query plan

Full details here

 Update on public.security_scan_profiles_projects  (cost=0.42..13.13 rows=0 width=0) (actual time=8.600..8.600 rows=0 loops=1)
   Buffers: shared hit=110 read=27 dirtied=18
   WAL: records=42 fpi=17 bytes=114228
   I/O Timings: read=8.025 write=0.000
   ->  Index Scan using index_security_scan_profiles_projects_on_unique_project_profile on public.security_scan_profiles_projects  (cost=0.42..13.13 rows=2 width=7) (actual time=2.072..4.075 rows=9 loops=1)
         Index Cond: (security_scan_profiles_projects.project_id = ANY ('{80141160,65339032,70472537}'::bigint[]))
         Index Searches: 3
         Filter: (NOT security_scan_profiles_projects.archived)
         Buffers: shared hit=4 read=14 dirtied=1
         WAL: records=1 fpi=1 bytes=8169
         I/O Timings: read=3.937 write=0.000
Settings: work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off'
Query ID: 2043215662359303514

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.