Allow optional: true with needs:pipeline:job

What does this MR do and why?

needs:optional could only be used with same-pipeline needs:job. Using optional: true with needs:pipeline:job (cross-pipeline needs, e.g. a child pipeline job depending on a parent pipeline job) failed CI lint with need config contains unknown keys: optional, and there was no way to depend on a job that only sometimes exists in the parent pipeline.

This adds optional to the needs:pipeline:job schema, and updates Ci::BuildDependencies so a missing optional cross-pipeline need no longer fails the build (only unmet mandatory needs do).

Resolves #349538

Screenshots or screen recordings

A failed job causes both child jobs to fail with the same errors, regardless of whether it is optional or not.

How to set up and validate locally

1. Create a parent pipeline with a job in a later stage that triggers a child pipeline, passing PARENT_PIPELINE_ID: $CI_PIPELINE_ID.
2. In the child pipeline, add a job with:

   needs:
     - pipeline: \$PARENT_PIPELINE_ID
       job: some-job-that-might-not-exist
       optional: true

3. Confirm CI lint no longer rejects the config, and the child job runs even when some-job-that-might-not-exist was not added to the parent pipeline.

Database

#	Query	Code path	Execution	Buffers (hit / read / dirtied)	Rows
1	Ci::Build.latest.in_pipelines(...) (any status)	FF on: existing_cross_pipeline_dependencies	16.2 ms	26 / 159 / 7	1
2	Ci::Build.latest.success.in_pipelines(...)	FF off: legacy_jobs_in_pipeline_hierarchy (same as master)	15.6 ms	26 / 159 / 7	1
3	same_family_pipeline_ids.where(id:).count	FF on: valid_cross_pipeline_with_optional?	6.7 ms	26 / 77 / 7	1

The new query (1) has the same plan as the legacy query (2) and reads the same number of buffers. The new COUNT query (3) is cheap and only runs when an optional need is missing. So I don't see a database concern here 👍

Test data

• Root/parent pipeline: 2887875278
• Same-project child pipelines: 2887884203, 2887883414, 2887883411, 2887891080
• Cross-project child pipeline (gitlab-org/gitlab-foss): 2887877524. It is not part of same_family_pipeline_ids because of project_condition: :same.
• Job used in queries 1 and 2: memory-on-boot (in the parent pipeline, success)
• Pipeline used in query 3: 2887884203 (a same-project child)

Notes on the plans

• Queries 1 and 2 have the same plan. Both scan the recursive CTE, then do an index scan on each p_ci_builds partition using the (commit_id, type, name, ref) index (ci_builds_1xx_commit_id_type_name_ref_idx). The index condition is commit_id, type and name in both.
• status is not part of the index. In query 2, status = 'success' is only a filter after the index scan. So removing .success does not change the plan or the rows read. Both read exactly 159 buffers.
• Query 3 runs the same recursive CTE that queries 1 and 2 already use as a subquery. It found 5 pipelines (root + 4 same-project children) and returned 1.
• No partition_id filter. None of these queries filter by partition_id, so every ci_pipelines / ci_builds partition is checked, and this is where most of the planning time goes. The legacy query on master does the same, so this MR does not change it.

Query 1: FF on, Ci::Build.latest (any status)

Joe command: 502947 (cold cache)

Query

SELECT "p_ci_builds".* FROM "p_ci_builds"
WHERE "p_ci_builds"."type" = 'Ci::Build'
  AND ("p_ci_builds"."retried" = FALSE OR "p_ci_builds"."retried" IS NULL)
  AND "p_ci_builds"."commit_id" IN (
    WITH RECURSIVE "base_and_descendants" AS (
      (SELECT "p_ci_pipelines".* FROM "p_ci_pipelines" WHERE "p_ci_pipelines"."id" = 2887875278)
      UNION
      (SELECT "p_ci_pipelines".* FROM "p_ci_pipelines", "base_and_descendants", "ci_sources_pipelines"
       WHERE "ci_sources_pipelines"."pipeline_id" = "p_ci_pipelines"."id"
         AND "ci_sources_pipelines"."partition_id" = "p_ci_pipelines"."partition_id"
         AND "ci_sources_pipelines"."source_pipeline_id" = "base_and_descendants"."id"
         AND "ci_sources_pipelines"."source_partition_id" = "base_and_descendants"."partition_id"
         AND "ci_sources_pipelines"."source_project_id" = "ci_sources_pipelines"."project_id")
    ) SELECT "id" FROM "base_and_descendants" AS "p_ci_pipelines")
  AND "p_ci_builds"."commit_id" = 2887875278
  AND "p_ci_builds"."name" = 'memory-on-boot';

Stats

Time: 151.335 ms
  - planning: 135.106 ms
  - execution: 16.229 ms
    - I/O read: 13.858 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 26 (~208.00 KiB) from the buffer pool
  - reads: 159 (~1.20 MiB) from the OS file cache, including disk I/O
  - dirtied: 7 (~56.00 KiB)
  - writes: 0

Query 2: FF off, Ci::Build.latest.success (legacy, same as master)

Joe command: 502951 (cold cache)

Query

SELECT "p_ci_builds".* FROM "p_ci_builds"
WHERE "p_ci_builds"."type" = 'Ci::Build'
  AND ("p_ci_builds"."retried" = FALSE OR "p_ci_builds"."retried" IS NULL)
  AND "p_ci_builds"."status" = 'success'
  AND "p_ci_builds"."commit_id" IN (
    WITH RECURSIVE "base_and_descendants" AS (
      (SELECT "p_ci_pipelines".* FROM "p_ci_pipelines" WHERE "p_ci_pipelines"."id" = 2887875278)
      UNION
      (SELECT "p_ci_pipelines".* FROM "p_ci_pipelines", "base_and_descendants", "ci_sources_pipelines"
       WHERE "ci_sources_pipelines"."pipeline_id" = "p_ci_pipelines"."id"
         AND "ci_sources_pipelines"."partition_id" = "p_ci_pipelines"."partition_id"
         AND "ci_sources_pipelines"."source_pipeline_id" = "base_and_descendants"."id"
         AND "ci_sources_pipelines"."source_partition_id" = "base_and_descendants"."partition_id"
         AND "ci_sources_pipelines"."source_project_id" = "ci_sources_pipelines"."project_id")
    ) SELECT "id" FROM "base_and_descendants" AS "p_ci_pipelines")
  AND "p_ci_builds"."commit_id" = 2887875278
  AND "p_ci_builds"."name" = 'memory-on-boot';

Stats

Time: 150.698 ms
  - planning: 135.143 ms
  - execution: 15.555 ms
    - I/O read: 13.429 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 26 (~208.00 KiB) from the buffer pool
  - reads: 159 (~1.20 MiB) from the OS file cache, including disk I/O
  - dirtied: 7 (~56.00 KiB)
  - writes: 0

Query 3: FF on, same_family_pipeline_ids.where(id:).count

Joe command: 502955 (cold cache)

Query

WITH RECURSIVE "base_and_descendants" AS (
  (SELECT "p_ci_pipelines".* FROM "p_ci_pipelines" WHERE "p_ci_pipelines"."id" = 2887875278)
  UNION
  (SELECT "p_ci_pipelines".* FROM "p_ci_pipelines", "base_and_descendants", "ci_sources_pipelines"
   WHERE "ci_sources_pipelines"."pipeline_id" = "p_ci_pipelines"."id"
     AND "ci_sources_pipelines"."partition_id" = "p_ci_pipelines"."partition_id"
     AND "ci_sources_pipelines"."source_pipeline_id" = "base_and_descendants"."id"
     AND "ci_sources_pipelines"."source_partition_id" = "base_and_descendants"."partition_id"
     AND "ci_sources_pipelines"."source_project_id" = "ci_sources_pipelines"."project_id")
) SELECT COUNT("id") FROM "base_and_descendants" AS "p_ci_pipelines"
WHERE "p_ci_pipelines"."id" = 2887884203;

Stats

Time: 48.288 ms
  - planning: 41.541 ms
  - execution: 6.747 ms
    - I/O read: 5.236 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 26 (~208.00 KiB) from the buffer pool
  - reads: 77 (~616.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 7 (~56.00 KiB)
  - writes: 0