User ParticipantsService linear ancestor scopes
What does this MR do and why?
In this MR, we're switching the behavior of the method ParticipantsService#project_members_through_invited_groups to use the linear version. The new behavior is behind the linear_participants_service_ancestor_scopes feature flag.
Database queries
The queries have been tested using a group with 21 parents.
The original query was:
WITH RECURSIVE "base_and_ancestors" AS (
(SELECT "namespaces".*
FROM "namespaces"
INNER JOIN "project_group_links" ON "namespaces"."id" = "project_group_links"."group_id"
WHERE "namespaces"."type" = 'Group'
AND "project_group_links"."project_id" = 1
AND ("namespaces"."visibility_level" IN (10,
20)
OR EXISTS
(SELECT 1
FROM
(SELECT "namespaces".*
FROM (
(WITH "direct_groups" AS MATERIALIZED
(SELECT "namespaces".*
FROM (
(SELECT "namespaces".*
FROM "namespaces"
INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "namespaces"."type" = 'Group'
AND "members"."user_id" = 2
AND "members"."requested_at" IS NULL
AND (access_level >= 10))
UNION
(SELECT "namespaces".*
FROM "projects"
INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
WHERE "project_authorizations"."user_id" = 2)) namespaces
WHERE "namespaces"."type" = 'Group') SELECT "namespaces".*
FROM (
(SELECT "namespaces".*
FROM "direct_groups" "namespaces"
WHERE "namespaces"."type" = 'Group')
UNION
(SELECT "namespaces".*
FROM "namespaces"
INNER JOIN "group_group_links" ON "group_group_links"."shared_group_id" = "namespaces"."id"
WHERE "namespaces"."type" = 'Group'
AND "group_group_links"."shared_with_group_id" IN
(SELECT "namespaces"."id"
FROM "direct_groups" "namespaces"
WHERE "namespaces"."type" = 'Group'))) namespaces
WHERE "namespaces"."type" = 'Group')) namespaces
WHERE "namespaces"."type" = 'Group') authorized
WHERE authorized."id" = "namespaces"."id")))
UNION
(SELECT "namespaces".*
FROM "namespaces",
"base_and_ancestors"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = "base_and_ancestors"."parent_id"))
SELECT "namespaces".*
FROM "base_and_ancestors" AS "namespaces"This is the query plan and the times are:
Time: 16.583 ms
- planning: 15.099 ms
- execution: 1.484 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 3 (~24.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0The new query is:
SELECT "namespaces".*
FROM
(SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."id" IN
(SELECT unnest(traversal_ids)
FROM "namespaces"
INNER JOIN "project_group_links" ON "namespaces"."id" = "project_group_links"."group_id"
WHERE "project_group_links"."project_id" = 1
AND ("namespaces"."visibility_level" IN (10,
20)
OR EXISTS
(SELECT 1
FROM
(SELECT "namespaces".*
FROM (
(WITH "direct_groups" AS MATERIALIZED
(SELECT "namespaces".*
FROM (
(SELECT "namespaces".*
FROM "namespaces"
INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "namespaces"."type" = 'Group'
AND "members"."user_id" = 2
AND "members"."requested_at" IS NULL
AND (access_level >= 10))
UNION
(SELECT "namespaces".*
FROM "projects"
INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
WHERE "project_authorizations"."user_id" = 2)) namespaces
WHERE "namespaces"."type" = 'Group') SELECT "namespaces".*
FROM (
(SELECT "namespaces".*
FROM "direct_groups" "namespaces"
WHERE "namespaces"."type" = 'Group')
UNION
(SELECT "namespaces".*
FROM "namespaces"
INNER JOIN "group_group_links" ON "group_group_links"."shared_group_id" = "namespaces"."id"
WHERE "namespaces"."type" = 'Group'
AND "group_group_links"."shared_with_group_id" IN
(SELECT "namespaces"."id"
FROM "direct_groups" "namespaces"
WHERE "namespaces"."type" = 'Group'))) namespaces
WHERE "namespaces"."type" = 'Group')) namespaces
WHERE "namespaces"."type" = 'Group') authorized
WHERE authorized."id" = "namespaces"."id")))) namespacesThis is the query plan and the times are:
Time: 9.561 ms
- planning: 8.531 ms
- execution: 1.030 ms
- I/O read: 0.000 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 3 (~24.00 KiB) from the buffer pool
- reads: 0 from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0How to set up and validate locally
-
Enable the feature flag for linear scopes
Feature.enable(:use_traversal_ids) -
Enable the feature flag for linear ancestors scopes
Feature.enable(:use_traversal_ids_for_ancestor_scopes) -
Enable the feature flag for
ParticipantsServicelinear ancestors scopesFeature.enable(:linear_participants_service_ancestor_scopes) -
Execute the following in the Rails console with the proper id depending on the scope testing:
Projects::ParticipantsService.new(project, User.second).execute(note).to_sqlMR 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.
Related to #339229 (closed)
Edited by Francisco Javier López (ex-Gitlab)