Skip to content

Draft: Linear and recursive Namespace.all_objects scope

Alex Pooley requested to merge 349167-namespace-all_objects into master

What does this MR do and why?

In order to more easily port ObjectHierarchy calls to our linear system, we need to provide an all_objects equivalent. The ObjectHierarchy#all_objects method is a little quirky to translate as it's specific to the design of the ObjectHierarchy class which effectively "executes" the ancestor and descendant search.

When we translate this to linear scopes it's just a UNION of the input scopes.

There's a couple of reasons it's worth having an all_objects equivalent.

  1. To lower the knowledge bar to port recursive queries to linear queries.
  2. To explicitly test that a UNION of multiple ancestor and descendant queries works as expected. E.g. !76910 (merged)

Query Plan

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/7773/commands/27633

How to set up and validate locally

Feature.enable :use_traversal_ids
Feature.enable :use_traversal_ids_for_ancestor_scopes
Feature.enable :use_traversal_ids_for_all_objects

g1 = Group.where(id: [1,2]).self_and_ancestors
g2 = Group.where(id: [3,4]).self_and_descendants

Group.all_objects(g1, g2)
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 
  (
    (
      WITH "base_ancestors_cte" AS MATERIALIZED (
        SELECT 
          "namespaces"."id", 
          "namespaces"."traversal_ids" 
        FROM 
          "namespaces" 
        WHERE 
          "namespaces"."type" = 'Group' 
          AND "namespaces"."id" IN (1, 2)
      ), 
      "ancestors_cte" AS MATERIALIZED (
        SELECT 
          id as base_id, 
          unnest(traversal_ids) as ancestor_id 
        FROM 
          "base_ancestors_cte"
      ) 
      SELECT 
        DISTINCT "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 
        "ancestors_cte", 
        "namespaces" 
      WHERE 
        "namespaces"."type" = 'Group' 
        AND "namespaces"."id" = "ancestors_cte"."ancestor_id"
    ) 
    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 
          (
            SELECT 
              DISTINCT on(namespaces.id) namespaces.* 
            FROM 
              namespaces, 
              (
                SELECT 
                  "namespaces"."id" 
                FROM 
                  "namespaces" 
                WHERE 
                  "namespaces"."type" = 'Group' 
                  AND "namespaces"."id" IN (3, 4)
              ) base 
            WHERE 
              "namespaces"."type" = 'Group' 
              AND (
                namespaces.traversal_ids @> ARRAY[base.id]
              )
          ) namespaces 
        WHERE 
          "namespaces"."type" = 'Group'
      )
  ) namespaces 
WHERE 
  "namespaces"."type" = 'Group'

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 #349167 (closed)

Edited by Francisco Javier López (ex-Gitlab)

Merge request reports