Skip to content

Fix cross join in User#ci_owned_runners

Furkan Ayhan requested to merge 336436-user-ci_owned_runners into master

What does this MR do and why?

Related to #336436

This MR resolves the cross DB join in User#ci_owned_runners by fetching project and namespace ids from the members table and using them in new queries.

Original idea by @ayufan: https://gitlab.com/gitlab-org/gitlab/-/issues/336436#note_801854428

It is behind a feature flag ci_owned_runners_cross_joins_fix #350322 (closed).

Database

Previous query

SELECT "ci_runners".*
FROM (
  (
    SELECT ci_runners.*
    FROM "ci_runner_projects"
    INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_projects"."runner_id"
    WHERE "ci_runner_projects"."project_id" IN (
      SELECT "projects"."id"
      FROM "projects"
      INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
      WHERE "project_authorizations"."user_id" = USER_ID AND (project_authorizations.access_level >= 40)
    )
  )
  UNION
  (
    SELECT ci_runners.*
    FROM "ci_runner_namespaces"
    INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_namespaces"."runner_id"
    WHERE "ci_runner_namespaces"."namespace_id" IN (
      SELECT DISTINCT namespaces.id
      FROM namespaces, (
        SELECT "namespaces"."id"
        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" = USER_ID
          AND "members"."requested_at" IS NULL
          AND (access_level >= 10)
          AND "members"."access_level" = 50
      ) base
      WHERE "namespaces"."type" = 'Group'
        AND (namespaces.traversal_ids @> ARRAY[base.id])
    )
  )
) ci_runners;

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8047/commands/28707

New queries

First, let's fill our tables:

INSERT INTO ci_project_mirrors (project_id, namespace_id)
SELECT "projects"."id", "projects"."namespace_id" FROM "projects"
ON CONFLICT (project_id) DO NOTHING;

INSERT INTO ci_namespace_mirrors (namespace_id, traversal_ids)
SELECT "namespaces"."id", "namespaces"."traversal_ids" FROM "namespaces"
ON CONFLICT (namespace_id) DO NOTHING;

ANALYZE ci_project_mirrors;
ANALYZE ci_namespace_mirrors;

The queries that we fetch project/namespace membership of the user:

-- returns PROJECT_IDS
SELECT "members"."source_id"
FROM "members"
WHERE "members"."source_type" = 'Project'
  AND "members"."type" = 'ProjectMember'
  AND "members"."user_id" = USER_ID
  AND "members"."requested_at" IS NULL
  AND (access_level >= 40);

-- https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8047/commands/28708


-- returns MAINTAINER_GROUP_IDS
SELECT "members"."source_id"
FROM "members"
WHERE "members"."source_type" = 'Namespace'
  AND "members"."type" = 'GroupMember'
  AND "members"."user_id" = USER_ID
  AND "members"."requested_at" IS NULL
  AND (access_level >= 10)
  AND (access_level >= 40);

-- https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8047/commands/28709


-- returns OWNER_GROUP_IDS
SELECT "members"."source_id"
FROM "members"
WHERE "members"."source_type" = 'Namespace'
  AND "members"."type" = 'GroupMember'
  AND "members"."user_id" = USER_ID
  AND "members"."requested_at" IS NULL
  AND (access_level >= 10)
  AND (access_level >= 50);

-- https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8047/commands/28710

The actual query that returns runners:

SELECT "ci_runners".*
FROM (
  (
    SELECT ci_runners.*
    FROM "ci_runner_projects"
    INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_projects"."runner_id"
    WHERE "ci_runner_projects"."project_id" IN (PROJECT_IDS)
  )
  UNION
  (
    SELECT ci_runners.*
    FROM "ci_runner_projects"
    INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_projects"."runner_id"
    JOIN ci_project_mirrors ON ci_project_mirrors.project_id = ci_runner_projects.project_id
    JOIN ci_namespace_mirrors ON ci_namespace_mirrors.namespace_id = ci_project_mirrors.namespace_id
    WHERE (traversal_ids && ARRAY[MAINTAINER_GROUP_IDS]::int[])
  )
  UNION
  (
    SELECT ci_runners.*
    FROM "ci_runner_namespaces"
    INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_namespaces"."runner_id"
    JOIN ci_namespace_mirrors ON ci_namespace_mirrors.namespace_id = ci_runner_namespaces.namespace_id
    WHERE (traversal_ids && ARRAY[OWNER_GROUP_IDS]::int[])
  )
) ci_runners;

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/8047/commands/28717

MR acceptance checklist

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

Edited by Furkan Ayhan

Merge request reports