Fix for CI::Runner.runner_matchers generates cross-database query
Problem
CI::Runner.runner_matchers
generates cross-database queries. Queries which join between ci_*
and non ci_*
tables which will not be allowed once ci_*
tables are moved to a new database.
Example
https://gitlab.com/gitlab-org/gitlab/-/jobs/1535681664
196) Ci::PipelineCreation::DropNotRunnableBuildsService#execute with public projects behaves like jobs allowed to run does not drop the jobs
Failure/Error:
raise CrossJoinAcrossUnsupportedTablesError,
"Unsupported cross-join across '#{tables.join(", ")}' modifying '#{schemas.to_a.join(", ")}' discovered " \
"when executing query '#{sql}'"
Database::PreventCrossJoins::CrossJoinAcrossUnsupportedTablesError:
Unsupported cross-join across 'ci_runners, namespaces, ci_runner_projects, taggings, tags, projects, ci_runner_namespaces' modifying 'gitlab_ci, gitlab_main' discovered when executing query 'SELECT array_agg(ci_runners.id), "ci_runners"."runner_type", "ci_runners"."public_projects_minutes_cost_factor", "ci_runners"."private_projects_minutes_cost_factor", "ci_runners"."run_untagged", "ci_runners"."access_level", (SELECT COALESCE(array_agg(tags.name ORDER BY name), ARRAY[]::text[]) FROM "taggings" INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE (taggings.taggable_id="ci_runners".id) AND "taggings"."context" = 'tags' AND "taggings"."taggable_type" = 'Ci::Runner') FROM ((SELECT "ci_runners".* FROM "ci_runners" INNER JOIN "ci_runner_projects" ON "ci_runners"."id" = "ci_runner_projects"."runner_id" WHERE "ci_runner_projects"."project_id" = 1820)
UNION
(SELECT "ci_runners".* FROM "ci_runners" INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id" INNER JOIN "namespaces" ON "namespaces"."id" = "ci_runner_namespaces"."namespace_id" AND "namespaces"."type" = 'Group' WHERE "namespaces"."id" IN (WITH RECURSIVE "base_and_ancestors" AS ((SELECT "namespaces".* FROM "namespaces" INNER JOIN "projects" ON "projects"."namespace_id" = "namespaces"."id" WHERE "namespaces"."type" = 'Group' AND "projects"."id" = 1820)
UNION
(SELECT "namespaces".* FROM "namespaces", "base_and_ancestors" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT "namespaces"."id" FROM "base_and_ancestors" AS "namespaces"))
UNION
(SELECT "ci_runners".* FROM "ci_runners" WHERE "ci_runners"."runner_type" = 1)) ci_runners WHERE "ci_runners"."active" = TRUE AND (contacted_at > '2021-08-26 00:18:02.642010') GROUP BY "ci_runners"."runner_type", "ci_runners"."public_projects_minutes_cost_factor", "ci_runners"."private_projects_minutes_cost_factor", "ci_runners"."run_untagged", "ci_runners"."access_level", (SELECT COALESCE(array_agg(tags.name ORDER BY name), ARRAY[]::text[]) FROM "taggings" INNER JOIN "tags" ON "tags"."id" = "taggings"."tag_id" WHERE (taggings.taggable_id="ci_runners".id) AND "taggings"."context" = 'tags' AND "taggings"."taggable_type" = 'Ci::Runner') /*application:test,correlation_id:ca0f0e2cd486873baf4653267db99102*/'
Shared Example Group: "jobs allowed to run" called from ./ee/spec/services/ci/pipeline_creation/drop_not_runnable_builds_service_spec.rb:94
# ./spec/support/database/prevent_cross_joins.rb:45:in `validate_cross_joins!'
# ./spec/support/database/prevent_cross_joins.rb:54:in `block in with_cross_joins_prevented'
# ./app/models/ci/runner.rb:211:in `runner_matchers'
# ./ee/lib/gitlab/ci/minutes/runners_availability.rb:53:in `block in runner_matchers'
# ./lib/gitlab/utils/strong_memoize.rb:30:in `strong_memoize'
# ./ee/lib/gitlab/ci/minutes/runners_availability.rb:52:in `runner_matchers'
# ./ee/lib/gitlab/ci/minutes/runners_availability.rb:41:in `block in instance_runners'
# ./lib/gitlab/utils/strong_memoize.rb:30:in `strong_memoize'
# ./ee/lib/gitlab/ci/minutes/runners_availability.rb:40:in `instance_runners'
# ./ee/lib/gitlab/ci/minutes/runners_availability.rb:29:in `matches_instance_runners_and_quota_used_up?'
# ./ee/lib/gitlab/ci/minutes/runners_availability.rb:24:in `quota_exceeded?'
# ./ee/lib/gitlab/ci/minutes/runners_availability.rb:16:in `available?'
# ./ee/app/services/ci/pipeline_creation/drop_not_runnable_builds_service.rb:30:in `block in validate_build_matchers'
# ./ee/app/services/ci/pipeline_creation/drop_not_runnable_builds_service.rb:30:in `each'
# ./ee/app/services/ci/pipeline_creation/drop_not_runnable_builds_service.rb:30:in `filter_map'
# ./ee/app/services/ci/pipeline_creation/drop_not_runnable_builds_service.rb:30:in `validate_build_matchers'
# ./ee/app/services/ci/pipeline_creation/drop_not_runnable_builds_service.rb:18:in `execute'
# ./ee/spec/services/ci/pipeline_creation/drop_not_runnable_builds_service_spec.rb:30:in `block (3 levels) in <top (required)>'
# ./ee/spec/services/ci/pipeline_creation/drop_not_runnable_builds_service_spec.rb:34:in `block (5 levels) in <top (required)>'
# ./ee/spec/services/ci/pipeline_creation/drop_not_runnable_builds_service_spec.rb:34:in `block (4 levels) in <top (required)>'
# ./spec/spec_helper.rb:390:in `block (3 levels) in <top (required)>'
# ./spec/support/sidekiq_middleware.rb:9:in `with_sidekiq_server_middleware'
# ./spec/spec_helper.rb:381:in `block (2 levels) in <top (required)>'
# ./spec/spec_helper.rb:377:in `block (3 levels) in <top (required)>'
# ./lib/gitlab/application_context.rb:31:in `with_raw_context'
# ./spec/spec_helper.rb:377:in `block (2 levels) in <top (required)>'
# ./spec/support/database/prevent_cross_joins.rb:85:in `block (3 levels) in <top (required)>'
# ./spec/support/database/prevent_cross_joins.rb:59:in `with_cross_joins_prevented'
# ./spec/support/database/prevent_cross_joins.rb:85:in `block (2 levels) in <top (required)>'
Edited by Thong Kuah