Allow project owners to list & restore their projects pending deletion
What does this MR do and why?
Using a feature flag, it displays the "Pending deletion" tab to both users with administrator access and assigned the owner role with projects that have been deleted if the instance is using GitLab Premium
Database
Raw 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_user_id",
"projects"."shared_runners_enabled",
"projects"."runners_token",
"projects"."build_coverage_regex",
"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"."emails_disabled",
"projects"."max_pages_size",
"projects"."max_artifacts_size",
"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"
FROM ((
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_user_id",
"projects"."shared_runners_enabled",
"projects"."runners_token",
"projects"."build_coverage_regex",
"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"."emails_disabled",
"projects"."max_pages_size",
"projects"."max_artifacts_size",
"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"
FROM
"projects"
WHERE
"projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND (EXISTS (
SELECT
1
FROM
"plans"
INNER JOIN "gitlab_subscriptions" ON "gitlab_subscriptions"."hosted_plan_id" = "plans"."id"
WHERE
"plans"."name" IN ('silver', 'premium', 'premium_trial', 'gold', 'ultimate', 'ultimate_trial')
AND (gitlab_subscriptions.namespace_id = namespaces.id)))))
UNION (
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_user_id",
"projects"."shared_runners_enabled",
"projects"."runners_token",
"projects"."build_coverage_regex",
"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"."emails_disabled",
"projects"."max_pages_size",
"projects"."max_artifacts_size",
"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"
FROM
"projects"
WHERE
"projects"."visibility_level" = 20
AND "projects"."namespace_id" IN (
SELECT
"namespaces"."id"
FROM
"namespaces"
WHERE
"namespaces"."type" = 'Group'
AND "namespaces"."visibility_level" = 20))) projects
INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
WHERE
"project_authorizations"."user_id" = 64248
AND (project_authorizations.access_level >= 50)
AND "projects"."pending_delete" = FALSE
AND (marked_for_deletion_at <= '2022-01-04')
AND "projects"."pending_delete" = FALSE
ORDER BY
LOWER("projects"."name") ASC
LIMIT 20 OFFSET 1
Query plan
Limit (cost=10292.19..10292.19 rows=1 width=4221) (actual time=826.923..827.191 rows=0 loops=1)
Buffers: shared hit=7646 read=2484 dirtied=92
I/O Timings: read=1132.713 write=0.000
-> Sort (cost=10292.18..10292.19 rows=1 width=4221) (actual time=826.920..827.187 rows=0 loops=1)
Sort Key: (lower((projects.name)::text))
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=7646 read=2484 dirtied=92
I/O Timings: read=1132.713 write=0.000
-> Hash Join (cost=10144.68..10292.17 rows=1 width=4221) (actual time=826.885..827.152 rows=0 loops=1)
Hash Cond: (project_authorizations.project_id = projects.id)
Buffers: shared hit=7643 read=2484 dirtied=92
I/O Timings: read=1132.713 write=0.000
-> Index Only Scan using project_authorizations_pkey on public.project_authorizations (cost=0.57..111.06 rows=296 width=4) (actual time=0.390..57.019 rows=4517 loops=1)
Index Cond: ((project_authorizations.user_id = 64248) AND (project_authorizations.access_level >= 50))
Heap Fetches: 213
Buffers: shared hit=1854 read=318 dirtied=25
I/O Timings: read=53.838 write=0.000
-> Hash (cost=10131.83..10131.83 rows=982 width=4189) (actual time=769.052..769.317 rows=340 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 139kB
Buffers: shared hit=5789 read=2166 dirtied=67
I/O Timings: read=1078.875 write=0.000
-> HashAggregate (cost=10112.19..10122.01 rows=982 width=4189) (actual time=768.032..768.861 rows=340 loops=1)
Group Key: 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_user_id, projects.shared_runners_enabled, projects.runners_token, projects.build_coverage_regex, 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.emails_disabled, projects.max_pages_size, projects.max_artifacts_size, 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
Buffers: shared hit=5789 read=2166 dirtied=67
I/O Timings: read=1078.875 write=0.000
-> Append (cost=1053.42..9918.25 rows=982 width=4189) (actual time=7.057..765.694 rows=345 loops=1)
Buffers: shared hit=5789 read=2166 dirtied=67
I/O Timings: read=1078.875 write=0.000
-> Gather (cost=1053.42..6894.99 rows=943 width=596) (actual time=7.056..364.846 rows=147 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=4580 read=1786 dirtied=65
I/O Timings: read=684.833 write=0.000
-> Nested Loop Semi Join (cost=53.42..5800.69 rows=555 width=596) (actual time=4.415..357.710 rows=74 loops=2)
Buffers: shared hit=4580 read=1786 dirtied=65
I/O Timings: read=684.833 write=0.000
-> Parallel Bitmap Heap Scan on public.projects (cost=52.29..2849.83 rows=1017 width=596) (actual time=2.350..284.452 rows=306 loops=2)
Buffers: shared read=634 dirtied=49
I/O Timings: read=559.154 write=0.000
-> Bitmap Index Scan using index_projects_aimed_for_deletion (cost=0.00..51.86 rows=1729 width=0) (actual time=1.058..1.058 rows=613 loops=1)
Index Cond: (projects.marked_for_deletion_at <= '2022-01-04'::date)
Buffers: shared read=30
I/O Timings: read=0.662 write=0.000
-> Nested Loop Semi Join (cost=1.13..2.89 rows=1 width=8) (actual time=0.237..0.237 rows=0 loops=612)
Buffers: shared hit=4580 read=1152 dirtied=16
I/O Timings: read=125.680 write=0.000
-> Index Only Scan using index_namespaces_on_type_and_id on public.namespaces (cost=0.56..2.22 rows=1 width=4) (actual time=0.187..0.188 rows=1 loops=612)
Index Cond: ((namespaces.type = 'Group'::text) AND (namespaces.id = projects.namespace_id))
Heap Fetches: 165
Buffers: shared hit=2406 read=584 dirtied=15
I/O Timings: read=103.615 write=0.000
-> Nested Loop (cost=0.57..0.66 rows=1 width=4) (actual time=0.051..0.051 rows=0 loops=560)
Buffers: shared hit=2174 read=568
I/O Timings: read=22.065 write=0.000
-> Index Scan using index_gitlab_subscriptions_on_namespace_id on public.gitlab_subscriptions (cost=0.43..0.50 rows=1 width=8) (actual time=0.047..0.047 rows=1 loops=560)
Index Cond: (gitlab_subscriptions.namespace_id = namespaces.id)
Buffers: shared hit=1472 read=567
I/O Timings: read=22.042 write=0.000
-> Index Scan using plans_pkey on public.plans (cost=0.14..0.16 rows=1 width=4) (actual time=0.004..0.004 rows=0 loops=351)
Index Cond: (plans.id = gitlab_subscriptions.hosted_plan_id)
Filter: ((plans.name)::text = ANY ('{silver,premium,premium_trial,gold,ultimate,ultimate_trial}'::text[]))
Rows Removed by Filter: 1
Buffers: shared hit=702 read=1
I/O Timings: read=0.022 write=0.000
-> Nested Loop (cost=0.71..3008.53 rows=39 width=596) (actual time=20.651..400.721 rows=198 loops=1)
Buffers: shared hit=1209 read=380 dirtied=2
I/O Timings: read=394.042 write=0.000
-> Index Scan using index_projects_aimed_for_deletion on public.projects projects_1 (cost=0.28..2223.68 rows=227 width=596) (actual time=0.056..2.518 rows=237 loops=1)
Index Cond: (projects_1.marked_for_deletion_at <= '2022-01-04'::date)
Filter: (projects_1.visibility_level = 20)
Rows Removed by Filter: 375
Buffers: shared hit=641 dirtied=1
I/O Timings: read=0.000 write=0.000
-> Index Scan using namespaces_pkey on public.namespaces namespaces_1 (cost=0.43..3.46 rows=1 width=4) (actual time=1.677..1.677 rows=1 loops=237)
Index Cond: (namespaces_1.id = projects_1.namespace_id)
Filter: ((namespaces_1.visibility_level = 20) AND ((namespaces_1.type)::text = 'Group'::text))
Rows Removed by Filter: 0
Buffers: shared hit=568 read=380 dirtied=1
I/O Timings: read=394.042 write=0.000
Database lab
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7826/commands/27997explain.depesz
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7826/commands/27997#visualize-depeszComparison with existing query
Existing Query | New Query | |
---|---|---|
DB Lab link | https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7900/commands/28280 | https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7826/commands/27997 |
Time | 3.478 s | 837.79 ms |
Screenshots or screen recordings
https://www.loom.com/share/261011f3edd14ba99bbb3c0b611b7d38
🚩
Feature flag enabled
With feature flag disabled
How to set up and validate locally
- Enable the feature flag
echo "Feature.enable(:project_owners_list_project_pending_deletion)" | rails c
- Log in as an administrator with at least GitLab Premium
- Enabled delayed project deletion
- Delete a project not in a personal namespace
- View the Menu dropdown
- View the pending deletion tab at
/dashboard/projects/removed
- Impersonate or log in as a user assigned the owner role
- View the pending deletion tab again at
/dashboard/projects/removed
- View the Menu dropdown
- As an administrator, remove the GitLab Premium license to revert back to GitLab Core
- View the home page
- View the Menu dropdown
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.
Relates to #346976 (closed)
Edited by Huzaifa Iftikhar