Include shared from groups members in the projects members
What does this MR do and why?
The current_user was not being passed to the GroupMembersFinder and the shared_from_groups was also not being passed, leading to missing members in the project members API. Consider the below example to understand more:
flowchart TB
subgraph Group2
Project
end
subgraph Group1
Member1
Member2
end
Group1 -->|Group 2 has Group 1 as its member| Group2Group2 is shared with Group1 so when returning the members of the project we should include the members of Group1 as they can also access the project because the project's parent group (Group2) is being shared.
Query plans
Before
Raw Query
-- master
EXPLAIN
SELECT "members"."id",
"members"."access_level",
"members"."source_id",
"members"."source_type",
"members"."user_id",
"members"."notification_level",
"members"."type",
"members"."created_at",
"members"."updated_at",
"members"."created_by_id",
"members"."invite_email",
"members"."invite_token",
"members"."invite_accepted_at",
"members"."requested_at",
"members"."expires_at",
"members"."ldap",
"members"."override",
"members"."state",
"members"."invite_email_success",
"members"."member_namespace_id",
"members"."member_role_id"
FROM (SELECT DISTINCT ON (user_id, invite_email) member_union.id,
COALESCE(project_authorizations.access_level,
member_union.access_level) access_level,
member_union.source_id,
member_union.source_type,
member_union.user_id,
member_union.notification_level,
member_union.type,
member_union.created_at,
member_union.updated_at,
member_union.created_by_id,
member_union.invite_email,
member_union.invite_token,
member_union.invite_accepted_at,
member_union.requested_at,
member_union.expires_at,
member_union.ldap,
member_union.override,
member_union.state,
member_union.invite_email_success,
member_union.member_namespace_id,
member_union.member_role_id
FROM ((SELECT "members".*
FROM (SELECT DISTINCT ON (user_id, invite_email) *
FROM "members"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN (SELECT "namespaces"."id"
FROM ((SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)) namespaces
WHERE "namespaces"."type" = 'Group')
ORDER BY user_id, invite_email, access_level DESC, expires_at DESC, created_at ASC) members
WHERE "members"."type" = 'GroupMember'
AND "members"."invite_token" IS NULL
AND (members.access_level > 5))
UNION ALL
(SELECT "members".*
FROM "members"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."source_id" IN
(WITH "base_ancestors_cte" AS MATERIALIZED (
SELECT "namespaces"."traversal_ids"
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" = 278964)
SELECT "id"
FROM "namespaces"
INNER JOIN (SELECT DISTINCT UNNEST("base_ancestors_cte"."traversal_ids")
FROM base_ancestors_cte) AS ancestors(ancestor_id)
ON namespaces.id = ancestors.ancestor_id
WHERE "namespaces"."type" = 'Group'
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"."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"
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" = 10327656
AND "members"."requested_at" IS NULL
AND (access_level >= 10))
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 "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" = 10327656)) 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')
UNION
(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" = 10327656
AND "members"."access_level" = 5)) namespaces
WHERE "namespaces"."type" = 'Group') authorized
WHERE authorized."id" = "namespaces"."id")))
AND (members.access_level > 5))
UNION ALL
(SELECT "members".*
FROM "members"
WHERE "members"."type" = 'ProjectMember'
AND "members"."member_namespace_id" = 15846663
AND "members"."requested_at" IS NULL)) AS member_union
LEFT JOIN users ON users.id = member_union.user_id
LEFT JOIN project_authorizations ON project_authorizations.user_id = users.id
AND
project_authorizations.project_id = 278964
ORDER BY user_id,
invite_email,
CASE
WHEN type = 'ProjectMember' THEN 1
WHEN type = 'GroupMember' THEN 2
ELSE 3
END
) AS members
WHERE "members"."invite_token" IS NULL
AND "members"."requested_at" IS NULL
ORDER BY "members"."id" ASC
LIMIT 20 OFFSET 0;https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/18270/commands/60327
After
Raw Query
-- After changes
EXPLAIN
SELECT "members"."id",
"members"."access_level",
"members"."source_id",
"members"."source_type",
"members"."user_id",
"members"."notification_level",
"members"."type",
"members"."created_at",
"members"."updated_at",
"members"."created_by_id",
"members"."invite_email",
"members"."invite_token",
"members"."invite_accepted_at",
"members"."requested_at",
"members"."expires_at",
"members"."ldap",
"members"."override",
"members"."state",
"members"."invite_email_success",
"members"."member_namespace_id",
"members"."member_role_id"
FROM (SELECT DISTINCT ON (user_id, invite_email) member_union.id,
COALESCE(project_authorizations.access_level,
member_union.access_level) access_level,
member_union.source_id,
member_union.source_type,
member_union.user_id,
member_union.notification_level,
member_union.type,
member_union.created_at,
member_union.updated_at,
member_union.created_by_id,
member_union.invite_email,
member_union.invite_token,
member_union.invite_accepted_at,
member_union.requested_at,
member_union.expires_at,
member_union.ldap,
member_union.override,
member_union.state,
member_union.invite_email_success,
member_union.member_namespace_id,
member_union.member_role_id
FROM ((SELECT "members".*
FROM (SELECT DISTINCT ON (user_id, invite_email) *
FROM ((SELECT "members"."id",
"members"."access_level",
"members"."source_id",
"members"."source_type",
"members"."user_id",
"members"."notification_level",
"members"."type",
"members"."created_at",
"members"."updated_at",
"members"."created_by_id",
"members"."invite_email",
"members"."invite_token",
"members"."invite_accepted_at",
"members"."requested_at",
"members"."expires_at",
"members"."ldap",
"members"."override",
"members"."state",
"members"."invite_email_success",
"members"."member_namespace_id",
"members"."member_role_id"
FROM "members"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN (SELECT "namespaces"."id"
FROM ((SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)) namespaces
WHERE "namespaces"."type" = 'Group'))
UNION
(SELECT "members"."id",
LEAST("group_group_links"."group_access", "members"."access_level") AS access_level,
"members"."source_id",
"members"."source_type",
"members"."user_id",
"members"."notification_level",
"members"."type",
"members"."created_at",
"members"."updated_at",
"members"."created_by_id",
"members"."invite_email",
"members"."invite_token",
"members"."invite_accepted_at",
"members"."requested_at",
"members"."expires_at",
"members"."ldap",
"members"."override",
"members"."state",
"members"."invite_email_success",
"members"."member_namespace_id",
"members"."member_role_id"
FROM "members"
LEFT OUTER JOIN group_group_links
ON members.source_id = group_group_links.shared_with_group_id
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN (
SELECT "namespaces"."id"
FROM "namespaces"
INNER JOIN "group_group_links"
ON "group_group_links"."shared_with_group_id" = "namespaces"."id"
WHERE "namespaces"."type" = 'Group'
AND "group_group_links"."shared_group_id" IN
(SELECT "namespaces"."id"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 9970)
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"."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"
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" = 10327656
AND "members"."requested_at" IS NULL
AND (access_level >= 10))
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 "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" = 10327656)) 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')
UNION
(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" = 10327656
AND "members"."access_level" = 5)) namespaces
WHERE "namespaces"."type" = 'Group') authorized
WHERE authorized."id" = "namespaces"."id"))))) members
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
ORDER BY user_id, invite_email, access_level DESC, expires_at DESC, created_at ASC) members
WHERE "members"."type" = 'GroupMember'
AND "members"."invite_token" IS NULL
AND (members.access_level > 5))
UNION ALL
(SELECT "members".*
FROM "members"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."source_id" IN
(WITH "base_ancestors_cte" AS MATERIALIZED (SELECT "namespaces"."traversal_ids"
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" = 278964)
SELECT "id"
FROM "namespaces"
INNER JOIN (SELECT DISTINCT UNNEST("base_ancestors_cte"."traversal_ids")
FROM base_ancestors_cte) AS ancestors(ancestor_id)
ON namespaces.id = ancestors.ancestor_id
WHERE "namespaces"."type" = 'Group'
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"."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"
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" = 10327656
AND "members"."requested_at" IS NULL
AND (access_level >= 10))
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 "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" = 10327656)) 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')
UNION
(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" = 10327656
AND "members"."access_level" = 5)) namespaces
WHERE "namespaces"."type" = 'Group') authorized
WHERE authorized."id" = "namespaces"."id")))
AND (members.access_level > 5))
UNION ALL
(SELECT "members".*
FROM "members"
WHERE "members"."type" = 'ProjectMember'
AND "members"."member_namespace_id" = 15846663
AND "members"."requested_at" IS NULL)) AS member_union
LEFT JOIN users ON users.id = member_union.user_id
LEFT JOIN project_authorizations ON project_authorizations.user_id = users.id
AND
project_authorizations.project_id = 278964
ORDER BY user_id,
invite_email,
CASE
WHEN type = 'ProjectMember' THEN 1
WHEN type = 'GroupMember' THEN 2
ELSE 3
END
) AS members
WHERE "members"."invite_token" IS NULL
AND "members"."requested_at" IS NULL
ORDER BY "members"."id" ASC
LIMIT 20 OFFSET 0https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/18270/commands/60342
Note: To generate the above query plan in the After section I had to replace some column name with select table_name.* and some select list_of_columns with select table_name.* because I was getting this error:
ERROR: each UNION query must have the same number of columnsSee the related discussion here internal link.
Copying the conversation here for future reference:
AlexC:
it sounds that on production the order of columns in namespaces table may be different. Replacing the list of columns selected from namespaces table with namespaces.* seems to fix the problem.How to set up and validate locally
After checking out this MR to validate #337623 (closed), you should get the expected correct behavior.
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.
Closes #337623 (closed)
Edited by Abdul Wadood