Fix cross-DB query in EnvironmentStatus
What does this MR do and why?
Fixes cross joins with deployments
table for environments_status feature.
We have made use of an alternate approach to fetch the same details using the ci_builds_metadata
table.
Current query
SELECT
"environments".*
FROM
"environments"
WHERE
"environments"."id" IN (
WITH RECURSIVE "base_and_descendants" AS (
(
SELECT
"ci_pipelines".*
FROM
"ci_pipelines"
WHERE
"ci_pipelines"."id" = 30
)
UNION
(
SELECT
"ci_pipelines".*
FROM
"ci_pipelines",
"base_and_descendants",
"ci_sources_pipelines"
WHERE
"ci_sources_pipelines"."pipeline_id" = "ci_pipelines"."id"
AND "ci_sources_pipelines"."source_pipeline_id" = "base_and_descendants"."id"
AND "ci_sources_pipelines"."source_project_id" = "ci_sources_pipelines"."project_id"
)
)
SELECT
"deployments"."environment_id"
FROM
"base_and_descendants" AS "ci_pipelines"
INNER JOIN "ci_builds" ON "ci_builds"."commit_id" = "ci_pipelines"."id"
AND "ci_builds"."type" = ' Ci :: Build '
INNER JOIN "deployments" ON "deployments"."deployable_type" = ' CommitStatus '
AND "deployments"."deployable_id" = "ci_builds"."id"
)
AND ("environments"."state" IN (' available '))
New queries
SELECT DISTINCT "expanded_environment_name"
FROM "ci_builds"
INNER JOIN "ci_builds_metadata"
ON "ci_builds_metadata"."build_id" = "ci_builds"."id"
WHERE "ci_builds"."type" = 'Ci::Build'
AND (
"ci_builds"."retried" = false
OR "ci_builds"."retried" IS NULL)
AND "ci_builds"."commit_id" IN (WITH recursive "base_and_descendants" AS (
(
SELECT "ci_pipelines".*
FROM "ci_pipelines"
WHERE "ci_pipelines"."id" = 96)
UNION
(
SELECT "ci_pipelines".*
FROM "ci_pipelines",
"base_and_descendants",
"ci_sources_pipelines"
WHERE "ci_sources_pipelines"."pipeline_id" = "ci_pipelines"."id"
AND "ci_sources_pipelines"."source_pipeline_id" = "base_and_descendants"."id"
AND "ci_sources_pipelines"."source_project_id" = "ci_sources_pipelines"."project_id"))SELECT id
FROM "base_and_descendants" AS "ci_pipelines")
AND "ci_builds_metadata"."expanded_environment_name" IS NOT NULL limit 100
SELECT "projects"."id", "projects"."name", "projects"."path", "projects"."description", "projects"."created_at", "projects"."updated_at", "projects"."creator_id", "projects"."namespace_id", "projects"."last_activity_at", "projects"."import_url", "projects"."visibility_level", "projects"."archived", "projects"."avatar", "projects"."merge_requests_template", "projects"."star_count", "projects"."merge_requests_rebase_enabled", "projects"."import_type", "projects"."import_source", "projects"."approvals_before_merge", "projects"."reset_approvals_on_push", "projects"."merge_requests_ff_only_enabled", "projects"."issues_template", "projects"."mirror", "projects"."mirror_user_id", "projects"."shared_runners_enabled", "projects"."runners_token", "projects"."build_coverage_regex", "projects"."build_allow_git_fetch", "projects"."build_timeout", "projects"."mirror_trigger_builds", "projects"."pending_delete", "projects"."public_builds", "projects"."last_repository_check_failed", "projects"."last_repository_check_at", "projects"."only_allow_merge_if_pipeline_succeeds", "projects"."has_external_issue_tracker", "projects"."repository_storage", "projects"."repository_read_only", "projects"."request_access_enabled", "projects"."has_external_wiki", "projects"."ci_config_path", "projects"."lfs_enabled", "projects"."description_html", "projects"."only_allow_merge_if_all_discussions_are_resolved", "projects"."repository_size_limit", "projects"."printing_merge_request_link_enabled", "projects"."auto_cancel_pending_pipelines", "projects"."service_desk_enabled", "projects"."cached_markdown_version", "projects"."delete_error", "projects"."last_repository_updated_at", "projects"."disable_overriding_approvers_per_merge_request", "projects"."storage_version", "projects"."resolve_outdated_diff_discussions", "projects"."remote_mirror_available_overridden", "projects"."only_mirror_protected_branches", "projects"."pull_mirror_available_overridden", "projects"."jobs_cache_index", "projects"."external_authorization_classification_label", "projects"."mirror_overwrites_diverged_branches", "projects"."pages_https_only", "projects"."external_webhook_token", "projects"."packages_enabled", "projects"."merge_requests_author_approval", "projects"."pool_repository_id", "projects"."runners_token_encrypted", "projects"."bfg_object_map", "projects"."detected_repository_languages", "projects"."merge_requests_disable_committers_approval", "projects"."require_password_to_approve", "projects"."emails_disabled", "projects"."max_pages_size", "projects"."max_artifacts_size", "projects"."remove_source_branch_after_merge", "projects"."marked_for_deletion_at", "projects"."marked_for_deletion_by_user_id", "projects"."autoclose_referenced_issues", "projects"."suggestion_commit_message", "projects"."project_namespace_id" FROM "projects" WHERE "projects"."id" = 20 LIMIT 1
SELECT "environments".* FROM "environments" WHERE "environments"."project_id" = 20 AND "environments"."name" IN ('Staging', 'hello', 'hello3')
Screenshots or screen recordings
Verified locally and screenshots are available in !71894 (comment 701737203)
How to set up and validate locally
> pipeline = Ci::Pipeline.last
> pipeline.environments_in_self_and_descendants
# It should return the environments and the query should not do a cross join with the ci tables.
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.
Related to #340781 (closed)
Edited by Bala Kumar