Skip to content

Add self_and_ancestors linear query upto parameter

Alex Pooley requested to merge 349178-self_and_ancestors-linear-query-with-upto-param into master

What does this MR do and why?

We have replaced ObjectHierarchy#base_and_ancestor calls with linear query scopes requests such as Group.where(id: [1,2,3]).self_and_ancestors). This MR further adds support for the upto parameter that ObjectHierarchy#base_and_ancestor accepts.

SQL Changes

Query plan https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/7787/commands/27707

WITH "base_ancestors_cte" AS MATERIALIZED (
  SELECT 
    "namespaces"."id", 
    "namespaces"."traversal_ids" 
  FROM 
    "namespaces" 
  WHERE 
    "namespaces"."id" = 14090875
), 
"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"."id" = "ancestors_cte"."ancestor_id" 
  AND "namespaces"."id" NOT IN (
    SELECT 
      unnest(traversal_ids) 
    FROM 
      "namespaces" 
    WHERE 
      "namespaces"."id" = 14059225
  )

How to set up and validate locally

Feature.enable :use_traversal_ids
Feature.enable :use_traversal_ids_for_ancestor_scopes

FactoryBot.create(:group, :with_hierarchy)

Group.where(id: [35, 36]).self_and_ancestors(upto: 1)
=> [#<Group id:23 @group1/group23>, #<Group id:34 @group1/group23/group34>, #<Group id:35 @group1/group23/group34/group35>, #<Group id:36 @group1/group23/group34/group36>]

Group.find(35)
=> #<Group id:35 @group1/group23/group34/group35>

Group.find(36)
=> #<Group id:36 @group1/group23/group34/group36>

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

Edited by Alex Pooley

Merge request reports