Add background job to bulk enable SPP for all projects in a group

Gal Katzrequested to merge
gkatz_bulk_update_spp_for_group into master

What does this MR do and why?

Adds a new idempotent background job that asynchronously iterates over nested projects in a group and sets its project-level setting to enabled or disable SPP.
This MR is related to Option 2 from #502826 (comment 2217680840)

Related issue:

Add background job to enable SPP for all projec... (#502826 - closed) • Gal Katz • 17.7
Enable SPP for all projects in a group via a ne... (&15840 - closed) • Gal Katz

Added scopes:

Project - without_security_setting

For all projects
SQL

Generated using Project.without_security_setting.to_sql

SELECT
    "projects"."id",
    "projects"."name",
    "projects"."path",
    "projects"."description",
    "projects"."created_at",
    "projects"."updated_at",
    "projects"."creator_id",
    "projects"."namespace_id",
    "projects"."last_activity_at",
    "projects"."import_url",
    "projects"."visibility_level",
    "projects"."archived",
    "projects"."avatar",
    "projects"."merge_requests_template",
    "projects"."star_count",
    "projects"."merge_requests_rebase_enabled",
    "projects"."import_type",
    "projects"."import_source",
    "projects"."approvals_before_merge",
    "projects"."reset_approvals_on_push",
    "projects"."merge_requests_ff_only_enabled",
    "projects"."issues_template",
    "projects"."mirror",
    "projects"."mirror_last_update_at",
    "projects"."mirror_last_successful_update_at",
    "projects"."mirror_user_id",
    "projects"."shared_runners_enabled",
    "projects"."runners_token",
    "projects"."build_allow_git_fetch",
    "projects"."build_timeout",
    "projects"."mirror_trigger_builds",
    "projects"."pending_delete",
    "projects"."public_builds",
    "projects"."last_repository_check_failed",
    "projects"."last_repository_check_at",
    "projects"."only_allow_merge_if_pipeline_succeeds",
    "projects"."has_external_issue_tracker",
    "projects"."repository_storage",
    "projects"."repository_read_only",
    "projects"."request_access_enabled",
    "projects"."has_external_wiki",
    "projects"."ci_config_path",
    "projects"."lfs_enabled",
    "projects"."description_html",
    "projects"."only_allow_merge_if_all_discussions_are_resolved",
    "projects"."repository_size_limit",
    "projects"."printing_merge_request_link_enabled",
    "projects"."auto_cancel_pending_pipelines",
    "projects"."service_desk_enabled",
    "projects"."cached_markdown_version",
    "projects"."delete_error",
    "projects"."last_repository_updated_at",
    "projects"."disable_overriding_approvers_per_merge_request",
    "projects"."storage_version",
    "projects"."resolve_outdated_diff_discussions",
    "projects"."remote_mirror_available_overridden",
    "projects"."only_mirror_protected_branches",
    "projects"."pull_mirror_available_overridden",
    "projects"."jobs_cache_index",
    "projects"."external_authorization_classification_label",
    "projects"."mirror_overwrites_diverged_branches",
    "projects"."pages_https_only",
    "projects"."external_webhook_token",
    "projects"."packages_enabled",
    "projects"."merge_requests_author_approval",
    "projects"."pool_repository_id",
    "projects"."runners_token_encrypted",
    "projects"."bfg_object_map",
    "projects"."detected_repository_languages",
    "projects"."merge_requests_disable_committers_approval",
    "projects"."require_password_to_approve",
    "projects"."max_pages_size",
    "projects"."max_artifacts_size",
    "projects"."pull_mirror_branch_prefix",
    "projects"."remove_source_branch_after_merge",
    "projects"."marked_for_deletion_at",
    "projects"."marked_for_deletion_by_user_id",
    "projects"."autoclose_referenced_issues",
    "projects"."suggestion_commit_message",
    "projects"."project_namespace_id",
    "projects"."hidden",
    "projects"."organization_id"
FROM
    "projects"
    LEFT OUTER JOIN "project_security_settings" ON "project_security_settings"."project_id" = "projects"."id"
WHERE
    "project_security_settings"."project_id" IS NULL
Query Plan

link here

Gather  (cost=1001.13..5182625.02 rows=1 width=826) (actual time=185506.599..631264.683 rows=1962 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=9295621 read=33647651 dirtied=29646 written=1044
   WAL: records=31247 fpi=29596 bytes=213840661
   I/O Timings: read=1369032.853 write=21.661
   ->  Merge Anti Join  (cost=1.13..5181624.92 rows=1 width=826) (actual time=185499.256..506818.520 rows=654 loops=3)
         Merge Cond: (projects.id = project_security_settings.project_id)
         Buffers: shared hit=9295621 read=33647651 dirtied=29646 written=1044
         WAL: records=31247 fpi=29596 bytes=213840661
         I/O Timings: read=1369032.853 write=21.661
         ->  Parallel Index Scan using projects_pkey on public.projects  (cost=0.56..4074426.49 rows=16059313 width=826) (actual time=1.230..495785.602 rows=12946105 loops=3)
               Buffers: shared hit=5414255 read=33451971 dirtied=17578 written=1028
               WAL: records=18234 fpi=17476 bytes=122335393
               I/O Timings: read=1362198.714 write=21.304
         ->  Index Only Scan using project_security_settings_pkey on public.project_security_settings  (cost=0.56..809372.04 rows=38833988 width=8) (actual time=0.041..7285.652 rows=27202320 loops=3)
               Heap Fetches: 3758123
               Buffers: shared hit=3881366 read=195680 dirtied=12068 written=16
               WAL: records=13013 fpi=12120 bytes=91505268
               I/O Timings: read=6834.139 write=0.357
Settings: seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off', work_mem = '100MB', random_page_cost = '1.5'
For a specific project
SQL

Generated using Project.without_security_setting.where(id: 13083).to_sql (id from here)

SELECT
    "projects"."id",
    "projects"."name",
    "projects"."path",
    "projects"."description",
    "projects"."created_at",
    "projects"."updated_at",
    "projects"."creator_id",
    "projects"."namespace_id",
    "projects"."last_activity_at",
    "projects"."import_url",
    "projects"."visibility_level",
    "projects"."archived",
    "projects"."avatar",
    "projects"."merge_requests_template",
    "projects"."star_count",
    "projects"."merge_requests_rebase_enabled",
    "projects"."import_type",
    "projects"."import_source",
    "projects"."approvals_before_merge",
    "projects"."reset_approvals_on_push",
    "projects"."merge_requests_ff_only_enabled",
    "projects"."issues_template",
    "projects"."mirror",
    "projects"."mirror_last_update_at",
    "projects"."mirror_last_successful_update_at",
    "projects"."mirror_user_id",
    "projects"."shared_runners_enabled",
    "projects"."runners_token",
    "projects"."build_allow_git_fetch",
    "projects"."build_timeout",
    "projects"."mirror_trigger_builds",
    "projects"."pending_delete",
    "projects"."public_builds",
    "projects"."last_repository_check_failed",
    "projects"."last_repository_check_at",
    "projects"."only_allow_merge_if_pipeline_succeeds",
    "projects"."has_external_issue_tracker",
    "projects"."repository_storage",
    "projects"."repository_read_only",
    "projects"."request_access_enabled",
    "projects"."has_external_wiki",
    "projects"."ci_config_path",
    "projects"."lfs_enabled",
    "projects"."description_html",
    "projects"."only_allow_merge_if_all_discussions_are_resolved",
    "projects"."repository_size_limit",
    "projects"."printing_merge_request_link_enabled",
    "projects"."auto_cancel_pending_pipelines",
    "projects"."service_desk_enabled",
    "projects"."cached_markdown_version",
    "projects"."delete_error",
    "projects"."last_repository_updated_at",
    "projects"."disable_overriding_approvers_per_merge_request",
    "projects"."storage_version",
    "projects"."resolve_outdated_diff_discussions",
    "projects"."remote_mirror_available_overridden",
    "projects"."only_mirror_protected_branches",
    "projects"."pull_mirror_available_overridden",
    "projects"."jobs_cache_index",
    "projects"."external_authorization_classification_label",
    "projects"."mirror_overwrites_diverged_branches",
    "projects"."pages_https_only",
    "projects"."external_webhook_token",
    "projects"."packages_enabled",
    "projects"."merge_requests_author_approval",
    "projects"."pool_repository_id",
    "projects"."runners_token_encrypted",
    "projects"."bfg_object_map",
    "projects"."detected_repository_languages",
    "projects"."merge_requests_disable_committers_approval",
    "projects"."require_password_to_approve",
    "projects"."max_pages_size",
    "projects"."max_artifacts_size",
    "projects"."pull_mirror_branch_prefix",
    "projects"."remove_source_branch_after_merge",
    "projects"."marked_for_deletion_at",
    "projects"."marked_for_deletion_by_user_id",
    "projects"."autoclose_referenced_issues",
    "projects"."suggestion_commit_message",
    "projects"."project_namespace_id",
    "projects"."hidden",
    "projects"."organization_id"
FROM
    "projects"
    LEFT OUTER JOIN "project_security_settings" ON "project_security_settings"."project_id" = "projects"."id"
WHERE
    "project_security_settings"."project_id" IS NULL
    AND "projects"."id" = 13083
Query Plan

link here

Nested Loop Anti Join  (cost=1.13..7.17 rows=1 width=826) (actual time=4.587..4.588 rows=0 loops=1)
   Buffers: shared hit=10 read=6 dirtied=1
   WAL: records=1 fpi=1 bytes=409
   I/O Timings: read=4.468 write=0.000
   ->  Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects  (cost=0.56..3.58 rows=1 width=826) (actual time=4.523..4.527 rows=1 loops=1)
         Index Cond: (projects.id = 13083)
         Buffers: shared hit=2 read=6 dirtied=1
         WAL: records=1 fpi=1 bytes=409
         I/O Timings: read=4.468 write=0.000
   ->  Index Only Scan using project_security_settings_pkey on public.project_security_settings  (cost=0.56..3.58 rows=1 width=8) (actual time=0.057..0.057 rows=1 loops=1)
         Index Cond: (project_security_settings.project_id = 13083)
         Heap Fetches: 0
         Buffers: shared hit=8
         I/O Timings: read=0.000 write=0.000
Settings: jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'

ProjectSecuritySetting - for_projects

SQL

Generated using ProjectSecuritySetting.for_projects([13083,278964]).to_sql (ids from here)

SELECT
    "project_security_settings".*
FROM
    "project_security_settings"
WHERE
    "project_security_settings"."project_id" IN (13083, 278964)
Query Plan

link here

 Index Scan using project_security_settings_pkey on public.project_security_settings  (cost=0.56..6.15 rows=2 width=31) (actual time=1.095..1.977 rows=2 loops=1)
   Index Cond: (project_security_settings.project_id = ANY ('{13083,278964}'::bigint[]))
   Buffers: shared hit=5 read=8
   I/O Timings: read=1.886 write=0.000
Settings: work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', jit = 'off'
Edited by Gal Katz

Merge request reports