Skip to content

Replace Namespace#self_and_hierarchy with linear version

Alex Pooley requested to merge 324748-replace-namespace-self_and_hierarchy-with-linear-version into master

What does this MR do and why?

Creates a linear version of Namespace#self_and_hierarchy.

This MR is behind a feature flag use_traversal_ids_for_self_and_hierarchy.

SQL Changes

Linear version

SELECT 
  "namespaces"."id", 
  "namespaces"."name", 
  "namespaces"."path", 
  "namespaces"."owner_id", 
  "namespaces"."created_at", 
  "namespaces"."updated_at", 
  "namespaces"."type", 
  "namespaces"."description", 
  "namespaces"."avatar", 
  "namespaces"."membership_lock", 
  "namespaces"."share_with_group_lock", 
  "namespaces"."visibility_level", 
  "namespaces"."request_access_enabled", 
  "namespaces"."ldap_sync_status", 
  "namespaces"."ldap_sync_error", 
  "namespaces"."ldap_sync_last_update_at", 
  "namespaces"."ldap_sync_last_successful_update_at", 
  "namespaces"."ldap_sync_last_sync_at", 
  "namespaces"."description_html", 
  "namespaces"."lfs_enabled", 
  "namespaces"."parent_id", 
  "namespaces"."shared_runners_minutes_limit", 
  "namespaces"."repository_size_limit", 
  "namespaces"."require_two_factor_authentication", 
  "namespaces"."two_factor_grace_period", 
  "namespaces"."cached_markdown_version", 
  "namespaces"."project_creation_level", 
  "namespaces"."runners_token", 
  "namespaces"."file_template_project_id", 
  "namespaces"."saml_discovery_token", 
  "namespaces"."runners_token_encrypted", 
  "namespaces"."custom_project_templates_group_id", 
  "namespaces"."auto_devops_enabled", 
  "namespaces"."extra_shared_runners_minutes_limit", 
  "namespaces"."last_ci_minutes_notification_at", 
  "namespaces"."last_ci_minutes_usage_notification_level", 
  "namespaces"."subgroup_creation_level", 
  "namespaces"."emails_disabled", 
  "namespaces"."max_pages_size", 
  "namespaces"."max_artifacts_size", 
  "namespaces"."mentions_disabled", 
  "namespaces"."default_branch_protection", 
  "namespaces"."unlock_membership_to_ldap", 
  "namespaces"."max_personal_access_token_lifetime", 
  "namespaces"."push_rule_id", 
  "namespaces"."shared_runners_enabled", 
  "namespaces"."allow_descendants_override_disabled_shared_runners", 
  "namespaces"."traversal_ids" 
FROM 
  "namespaces" 
WHERE 
  "namespaces"."type" = 'Group' 
  AND (
    traversal_ids @> ('{12991979}') 
    OR "namespaces"."id" IN (9970,12892338)
  )

Linear query plan - https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/7705/commands/27378

Recursive

WITH RECURSIVE "base_and_ancestors" AS (
  (
    SELECT 
      "namespaces"."id", 
      "namespaces"."name", 
      "namespaces"."path", 
      "namespaces"."owner_id", 
      "namespaces"."created_at", 
      "namespaces"."updated_at", 
      "namespaces"."type", 
      "namespaces"."description", 
      "namespaces"."avatar", 
      "namespaces"."membership_lock", 
      "namespaces"."share_with_group_lock", 
      "namespaces"."visibility_level", 
      "namespaces"."request_access_enabled", 
      "namespaces"."ldap_sync_status", 
      "namespaces"."ldap_sync_error", 
      "namespaces"."ldap_sync_last_update_at", 
      "namespaces"."ldap_sync_last_successful_update_at", 
      "namespaces"."ldap_sync_last_sync_at", 
      "namespaces"."description_html", 
      "namespaces"."lfs_enabled", 
      "namespaces"."parent_id", 
      "namespaces"."shared_runners_minutes_limit", 
      "namespaces"."repository_size_limit", 
      "namespaces"."require_two_factor_authentication", 
      "namespaces"."two_factor_grace_period", 
      "namespaces"."cached_markdown_version", 
      "namespaces"."project_creation_level", 
      "namespaces"."runners_token", 
      "namespaces"."file_template_project_id", 
      "namespaces"."saml_discovery_token", 
      "namespaces"."runners_token_encrypted", 
      "namespaces"."custom_project_templates_group_id", 
      "namespaces"."auto_devops_enabled", 
      "namespaces"."extra_shared_runners_minutes_limit", 
      "namespaces"."last_ci_minutes_notification_at", 
      "namespaces"."last_ci_minutes_usage_notification_level", 
      "namespaces"."subgroup_creation_level", 
      "namespaces"."emails_disabled", 
      "namespaces"."max_pages_size", 
      "namespaces"."max_artifacts_size", 
      "namespaces"."mentions_disabled", 
      "namespaces"."default_branch_protection", 
      "namespaces"."unlock_membership_to_ldap", 
      "namespaces"."max_personal_access_token_lifetime", 
      "namespaces"."push_rule_id", 
      "namespaces"."shared_runners_enabled", 
      "namespaces"."allow_descendants_override_disabled_shared_runners", 
      "namespaces"."traversal_ids" 
    FROM 
      "namespaces" 
    WHERE 
      "namespaces"."type" = 'Group' 
      AND "namespaces"."id" = 29
  ) 
  UNION 
    (
      SELECT 
        "namespaces"."id", 
        "namespaces"."name", 
        "namespaces"."path", 
        "namespaces"."owner_id", 
        "namespaces"."created_at", 
        "namespaces"."updated_at", 
        "namespaces"."type", 
        "namespaces"."description", 
        "namespaces"."avatar", 
        "namespaces"."membership_lock", 
        "namespaces"."share_with_group_lock", 
        "namespaces"."visibility_level", 
        "namespaces"."request_access_enabled", 
        "namespaces"."ldap_sync_status", 
        "namespaces"."ldap_sync_error", 
        "namespaces"."ldap_sync_last_update_at", 
        "namespaces"."ldap_sync_last_successful_update_at", 
        "namespaces"."ldap_sync_last_sync_at", 
        "namespaces"."description_html", 
        "namespaces"."lfs_enabled", 
        "namespaces"."parent_id", 
        "namespaces"."shared_runners_minutes_limit", 
        "namespaces"."repository_size_limit", 
        "namespaces"."require_two_factor_authentication", 
        "namespaces"."two_factor_grace_period", 
        "namespaces"."cached_markdown_version", 
        "namespaces"."project_creation_level", 
        "namespaces"."runners_token", 
        "namespaces"."file_template_project_id", 
        "namespaces"."saml_discovery_token", 
        "namespaces"."runners_token_encrypted", 
        "namespaces"."custom_project_templates_group_id", 
        "namespaces"."auto_devops_enabled", 
        "namespaces"."extra_shared_runners_minutes_limit", 
        "namespaces"."last_ci_minutes_notification_at", 
        "namespaces"."last_ci_minutes_usage_notification_level", 
        "namespaces"."subgroup_creation_level", 
        "namespaces"."emails_disabled", 
        "namespaces"."max_pages_size", 
        "namespaces"."max_artifacts_size", 
        "namespaces"."mentions_disabled", 
        "namespaces"."default_branch_protection", 
        "namespaces"."unlock_membership_to_ldap", 
        "namespaces"."max_personal_access_token_lifetime", 
        "namespaces"."push_rule_id", 
        "namespaces"."shared_runners_enabled", 
        "namespaces"."allow_descendants_override_disabled_shared_runners", 
        "namespaces"."traversal_ids" 
      FROM 
        "namespaces", 
        "base_and_ancestors" 
      WHERE 
        "namespaces"."type" = 'Group' 
        AND "namespaces"."id" = "base_and_ancestors"."parent_id"
    )
), 
"base_and_descendants" AS (
  (
    SELECT 
      "namespaces"."id", 
      "namespaces"."name", 
      "namespaces"."path", 
      "namespaces"."owner_id", 
      "namespaces"."created_at", 
      "namespaces"."updated_at", 
      "namespaces"."type", 
      "namespaces"."description", 
      "namespaces"."avatar", 
      "namespaces"."membership_lock", 
      "namespaces"."share_with_group_lock", 
      "namespaces"."visibility_level", 
      "namespaces"."request_access_enabled", 
      "namespaces"."ldap_sync_status", 
      "namespaces"."ldap_sync_error", 
      "namespaces"."ldap_sync_last_update_at", 
      "namespaces"."ldap_sync_last_successful_update_at", 
      "namespaces"."ldap_sync_last_sync_at", 
      "namespaces"."description_html", 
      "namespaces"."lfs_enabled", 
      "namespaces"."parent_id", 
      "namespaces"."shared_runners_minutes_limit", 
      "namespaces"."repository_size_limit", 
      "namespaces"."require_two_factor_authentication", 
      "namespaces"."two_factor_grace_period", 
      "namespaces"."cached_markdown_version", 
      "namespaces"."project_creation_level", 
      "namespaces"."runners_token", 
      "namespaces"."file_template_project_id", 
      "namespaces"."saml_discovery_token", 
      "namespaces"."runners_token_encrypted", 
      "namespaces"."custom_project_templates_group_id", 
      "namespaces"."auto_devops_enabled", 
      "namespaces"."extra_shared_runners_minutes_limit", 
      "namespaces"."last_ci_minutes_notification_at", 
      "namespaces"."last_ci_minutes_usage_notification_level", 
      "namespaces"."subgroup_creation_level", 
      "namespaces"."emails_disabled", 
      "namespaces"."max_pages_size", 
      "namespaces"."max_artifacts_size", 
      "namespaces"."mentions_disabled", 
      "namespaces"."default_branch_protection", 
      "namespaces"."unlock_membership_to_ldap", 
      "namespaces"."max_personal_access_token_lifetime", 
      "namespaces"."push_rule_id", 
      "namespaces"."shared_runners_enabled", 
      "namespaces"."allow_descendants_override_disabled_shared_runners", 
      "namespaces"."traversal_ids" 
    FROM 
      "namespaces" 
    WHERE 
      "namespaces"."type" = 'Group' 
      AND "namespaces"."id" = 29
  ) 
  UNION 
    (
      SELECT 
        "namespaces"."id", 
        "namespaces"."name", 
        "namespaces"."path", 
        "namespaces"."owner_id", 
        "namespaces"."created_at", 
        "namespaces"."updated_at", 
        "namespaces"."type", 
        "namespaces"."description", 
        "namespaces"."avatar", 
        "namespaces"."membership_lock", 
        "namespaces"."share_with_group_lock", 
        "namespaces"."visibility_level", 
        "namespaces"."request_access_enabled", 
        "namespaces"."ldap_sync_status", 
        "namespaces"."ldap_sync_error", 
        "namespaces"."ldap_sync_last_update_at", 
        "namespaces"."ldap_sync_last_successful_update_at", 
        "namespaces"."ldap_sync_last_sync_at", 
        "namespaces"."description_html", 
        "namespaces"."lfs_enabled", 
        "namespaces"."parent_id", 
        "namespaces"."shared_runners_minutes_limit", 
        "namespaces"."repository_size_limit", 
        "namespaces"."require_two_factor_authentication", 
        "namespaces"."two_factor_grace_period", 
        "namespaces"."cached_markdown_version", 
        "namespaces"."project_creation_level", 
        "namespaces"."runners_token", 
        "namespaces"."file_template_project_id", 
        "namespaces"."saml_discovery_token", 
        "namespaces"."runners_token_encrypted", 
        "namespaces"."custom_project_templates_group_id", 
        "namespaces"."auto_devops_enabled", 
        "namespaces"."extra_shared_runners_minutes_limit", 
        "namespaces"."last_ci_minutes_notification_at", 
        "namespaces"."last_ci_minutes_usage_notification_level", 
        "namespaces"."subgroup_creation_level", 
        "namespaces"."emails_disabled", 
        "namespaces"."max_pages_size", 
        "namespaces"."max_artifacts_size", 
        "namespaces"."mentions_disabled", 
        "namespaces"."default_branch_protection", 
        "namespaces"."unlock_membership_to_ldap", 
        "namespaces"."max_personal_access_token_lifetime", 
        "namespaces"."push_rule_id", 
        "namespaces"."shared_runners_enabled", 
        "namespaces"."allow_descendants_override_disabled_shared_runners", 
        "namespaces"."traversal_ids" 
      FROM 
        "namespaces", 
        "base_and_descendants" 
      WHERE 
        "namespaces"."type" = 'Group' 
        AND "namespaces"."parent_id" = "base_and_descendants"."id"
    )
) 
SELECT 
  "namespaces"."id", 
  "namespaces"."name", 
  "namespaces"."path", 
  "namespaces"."owner_id", 
  "namespaces"."created_at", 
  "namespaces"."updated_at", 
  "namespaces"."type", 
  "namespaces"."description", 
  "namespaces"."avatar", 
  "namespaces"."membership_lock", 
  "namespaces"."share_with_group_lock", 
  "namespaces"."visibility_level", 
  "namespaces"."request_access_enabled", 
  "namespaces"."ldap_sync_status", 
  "namespaces"."ldap_sync_error", 
  "namespaces"."ldap_sync_last_update_at", 
  "namespaces"."ldap_sync_last_successful_update_at", 
  "namespaces"."ldap_sync_last_sync_at", 
  "namespaces"."description_html", 
  "namespaces"."lfs_enabled", 
  "namespaces"."parent_id", 
  "namespaces"."shared_runners_minutes_limit", 
  "namespaces"."repository_size_limit", 
  "namespaces"."require_two_factor_authentication", 
  "namespaces"."two_factor_grace_period", 
  "namespaces"."cached_markdown_version", 
  "namespaces"."project_creation_level", 
  "namespaces"."runners_token", 
  "namespaces"."file_template_project_id", 
  "namespaces"."saml_discovery_token", 
  "namespaces"."runners_token_encrypted", 
  "namespaces"."custom_project_templates_group_id", 
  "namespaces"."auto_devops_enabled", 
  "namespaces"."extra_shared_runners_minutes_limit", 
  "namespaces"."last_ci_minutes_notification_at", 
  "namespaces"."last_ci_minutes_usage_notification_level", 
  "namespaces"."subgroup_creation_level", 
  "namespaces"."emails_disabled", 
  "namespaces"."max_pages_size", 
  "namespaces"."max_artifacts_size", 
  "namespaces"."mentions_disabled", 
  "namespaces"."default_branch_protection", 
  "namespaces"."unlock_membership_to_ldap", 
  "namespaces"."max_personal_access_token_lifetime", 
  "namespaces"."push_rule_id", 
  "namespaces"."shared_runners_enabled", 
  "namespaces"."allow_descendants_override_disabled_shared_runners", 
  "namespaces"."traversal_ids" 
FROM 
  (
    (
      SELECT 
        "namespaces"."id", 
        "namespaces"."name", 
        "namespaces"."path", 
        "namespaces"."owner_id", 
        "namespaces"."created_at", 
        "namespaces"."updated_at", 
        "namespaces"."type", 
        "namespaces"."description", 
        "namespaces"."avatar", 
        "namespaces"."membership_lock", 
        "namespaces"."share_with_group_lock", 
        "namespaces"."visibility_level", 
        "namespaces"."request_access_enabled", 
        "namespaces"."ldap_sync_status", 
        "namespaces"."ldap_sync_error", 
        "namespaces"."ldap_sync_last_update_at", 
        "namespaces"."ldap_sync_last_successful_update_at", 
        "namespaces"."ldap_sync_last_sync_at", 
        "namespaces"."description_html", 
        "namespaces"."lfs_enabled", 
        "namespaces"."parent_id", 
        "namespaces"."shared_runners_minutes_limit", 
        "namespaces"."repository_size_limit", 
        "namespaces"."require_two_factor_authentication", 
        "namespaces"."two_factor_grace_period", 
        "namespaces"."cached_markdown_version", 
        "namespaces"."project_creation_level", 
        "namespaces"."runners_token", 
        "namespaces"."file_template_project_id", 
        "namespaces"."saml_discovery_token", 
        "namespaces"."runners_token_encrypted", 
        "namespaces"."custom_project_templates_group_id", 
        "namespaces"."auto_devops_enabled", 
        "namespaces"."extra_shared_runners_minutes_limit", 
        "namespaces"."last_ci_minutes_notification_at", 
        "namespaces"."last_ci_minutes_usage_notification_level", 
        "namespaces"."subgroup_creation_level", 
        "namespaces"."emails_disabled", 
        "namespaces"."max_pages_size", 
        "namespaces"."max_artifacts_size", 
        "namespaces"."mentions_disabled", 
        "namespaces"."default_branch_protection", 
        "namespaces"."unlock_membership_to_ldap", 
        "namespaces"."max_personal_access_token_lifetime", 
        "namespaces"."push_rule_id", 
        "namespaces"."shared_runners_enabled", 
        "namespaces"."allow_descendants_override_disabled_shared_runners", 
        "namespaces"."traversal_ids" 
      FROM 
        "base_and_ancestors" AS "namespaces" 
      WHERE 
        "namespaces"."type" = 'Group'
    ) 
    UNION 
      (
        SELECT 
          "namespaces"."id", 
          "namespaces"."name", 
          "namespaces"."path", 
          "namespaces"."owner_id", 
          "namespaces"."created_at", 
          "namespaces"."updated_at", 
          "namespaces"."type", 
          "namespaces"."description", 
          "namespaces"."avatar", 
          "namespaces"."membership_lock", 
          "namespaces"."share_with_group_lock", 
          "namespaces"."visibility_level", 
          "namespaces"."request_access_enabled", 
          "namespaces"."ldap_sync_status", 
          "namespaces"."ldap_sync_error", 
          "namespaces"."ldap_sync_last_update_at", 
          "namespaces"."ldap_sync_last_successful_update_at", 
          "namespaces"."ldap_sync_last_sync_at", 
          "namespaces"."description_html", 
          "namespaces"."lfs_enabled", 
          "namespaces"."parent_id", 
          "namespaces"."shared_runners_minutes_limit", 
          "namespaces"."repository_size_limit", 
          "namespaces"."require_two_factor_authentication", 
          "namespaces"."two_factor_grace_period", 
          "namespaces"."cached_markdown_version", 
          "namespaces"."project_creation_level", 
          "namespaces"."runners_token", 
          "namespaces"."file_template_project_id", 
          "namespaces"."saml_discovery_token", 
          "namespaces"."runners_token_encrypted", 
          "namespaces"."custom_project_templates_group_id", 
          "namespaces"."auto_devops_enabled", 
          "namespaces"."extra_shared_runners_minutes_limit", 
          "namespaces"."last_ci_minutes_notification_at", 
          "namespaces"."last_ci_minutes_usage_notification_level", 
          "namespaces"."subgroup_creation_level", 
          "namespaces"."emails_disabled", 
          "namespaces"."max_pages_size", 
          "namespaces"."max_artifacts_size", 
          "namespaces"."mentions_disabled", 
          "namespaces"."default_branch_protection", 
          "namespaces"."unlock_membership_to_ldap", 
          "namespaces"."max_personal_access_token_lifetime", 
          "namespaces"."push_rule_id", 
          "namespaces"."shared_runners_enabled", 
          "namespaces"."allow_descendants_override_disabled_shared_runners", 
          "namespaces"."traversal_ids" 
        FROM 
          "base_and_descendants" AS "namespaces" 
        WHERE 
          "namespaces"."type" = 'Group'
      )
  ) namespaces 
WHERE 
  "namespaces"."type" = 'Group'

🤕

How to set up and validate locally

root = FactoryBot.create(:group, :with_hierarchy)
Feature.enable :use_traversal_ids, root
Feature.enable :use_traversal_ids_for_self_and_hierarchy, root
Feature.enable :use_traversal_ids_for_ancestors, root

# pick a random nested group
group = Group.find(29)

group.self_and_hierarchy

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #324748 (closed)

Edited by Alex Pooley

Merge request reports