Skip to content

Add pipeline condition to processable object hierarchy

Furkan Ayhan requested to merge 358110-fix-sql into master

What does this MR do and why?

We introduced an SQL query in !97156 (merged) to implement a fix for #358110 (closed). However, my SQL output in the MR was not correct because it includes this condition:

"ci_builds"."commit_id" = $integer

on the second part of the CTE query.

Yet, the correct SQL does not include this part, so that we had this incident: gitlab-com/gl-infra/production#7753 (closed). Thanks to the feature flag, only some of the GitLab projects' endpoints were affected.

This MR adds the "ci_builds"."commit_id" = "base_and_descendants"."commit_id" condition to the query.

Database

The previous query that was supposed to be introduced in the previous MR

-- This SQL is displayed on https://gitlab.com/gitlab-org/gitlab/-/merge_requests/97156 as a correct one. However, we did not have the commit_id condition.
-- Example pipeline: https://gitlab.com/gitlab-org/gitlab/-/pipelines/642005802
-- Example job: https://gitlab.com/gitlab-org/gitlab/-/jobs/3038254356
-- Timeout when no use of `"ci_builds"."commit_id" = 642005802` https://log.gprd.gitlab.net/app/discover#/doc/7092c4e2-4eb5-46f2-8305-a7da2edad090/pubsub-rails-inf-gprd-013603?id=noTBRYMBM3gvXWaq5c15
-- cold: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/12194/commands/43296

SELECT
  "ci_builds".*
FROM (( WITH RECURSIVE "base_and_descendants" AS ((
        SELECT
          "ci_builds".*
        FROM
          "ci_builds"
        WHERE
          "ci_builds"."type" IN ('Ci::Processable', 'Ci::Build', 'Ci::Bridge')
          AND "ci_builds"."id" = 3038254356)
      UNION (
        SELECT
          "ci_builds".*
        FROM
          "ci_builds",
          "base_and_descendants",
          "ci_build_needs"
        WHERE
          "ci_builds"."type" IN ('Ci::Processable', 'Ci::Build', 'Ci::Bridge')
          AND "ci_build_needs"."build_id" = "ci_builds"."id"
          AND "ci_build_needs"."name" = "base_and_descendants"."name"
          AND "ci_builds"."commit_id" = 642005802))
      SELECT
        "ci_builds".*
      FROM
        "base_and_descendants" AS "ci_builds"
      WHERE
        "ci_builds"."id" NOT IN (
          SELECT
            "ci_builds"."id"
          FROM
            "ci_builds"
          WHERE
            "ci_builds"."type" IN ('Ci::Processable', 'Ci::Build', 'Ci::Bridge')
            AND "ci_builds"."id" = 3038254356))
      UNION (
        SELECT
          "ci_builds".*
        FROM
          "ci_builds"
        WHERE
          "ci_builds"."type" IN ('Ci::Processable', 'Ci::Build', 'Ci::Bridge')
          AND "ci_builds"."commit_id" = 642005802
          AND (stage_idx > 1))) ci_builds
  WHERE
    "ci_builds"."type" IN ('Ci::Processable', 'Ci::Build', 'Ci::Bridge')
  AND ("ci_builds"."status" IN ('skipped'))
ORDER BY
  "ci_builds"."stage_idx" ASC;

The new query

-- This SQL is the new one.
-- Example pipeline: https://gitlab.com/gitlab-org/gitlab/-/pipelines/642005802
-- Example job: https://gitlab.com/gitlab-org/gitlab/-/jobs/3038254356
-- Timeout when no use of `"ci_builds"."commit_id" = "base_and_descendants"."commit_id"` https://log.gprd.gitlab.net/app/discover#/doc/7092c4e2-4eb5-46f2-8305-a7da2edad090/pubsub-rails-inf-gprd-013603?id=noTBRYMBM3gvXWaq5c15
-- cold: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/12194/commands/43298

SELECT
  "ci_builds".*
FROM (( WITH RECURSIVE "base_and_descendants" AS ((
        SELECT
          "ci_builds".*
        FROM
          "ci_builds"
        WHERE
          "ci_builds"."type" IN ('Ci::Processable', 'Ci::Build', 'Ci::Bridge')
          AND "ci_builds"."id" = 3038254356)
      UNION (
        SELECT
          "ci_builds".*
        FROM
          "ci_builds",
          "base_and_descendants",
          "ci_build_needs"
        WHERE
          "ci_builds"."type" IN ('Ci::Processable', 'Ci::Build', 'Ci::Bridge')
          AND "ci_build_needs"."build_id" = "ci_builds"."id"
          AND "ci_build_needs"."name" = "base_and_descendants"."name"
          AND "ci_builds"."commit_id" = "base_and_descendants"."commit_id"))
      SELECT
        "ci_builds".*
      FROM
        "base_and_descendants" AS "ci_builds"
      WHERE
        "ci_builds"."id" NOT IN (
          SELECT
            "ci_builds"."id"
          FROM
            "ci_builds"
          WHERE
            "ci_builds"."type" IN ('Ci::Processable', 'Ci::Build', 'Ci::Bridge')
            AND "ci_builds"."id" = 3038254356))
      UNION (
        SELECT
          "ci_builds".*
        FROM
          "ci_builds"
        WHERE
          "ci_builds"."type" IN ('Ci::Processable', 'Ci::Build', 'Ci::Bridge')
          AND "ci_builds"."commit_id" = 642005802
          AND (stage_idx > 1))) ci_builds
  WHERE
    "ci_builds"."type" IN ('Ci::Processable', 'Ci::Build', 'Ci::Bridge')
  AND "ci_builds"."commit_id" = 642005802
  AND ("ci_builds"."status" IN ('skipped'))
ORDER BY
  "ci_builds"."stage_idx" ASC;

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