Use cross pipeline dependencies

What does this MR do?

Related to #255983 (closed)

This MR is based on the syntax addition needs:pipeline from !47210 (merged) which was introduced in the backend only. Here we use the syntax to allow download artifacts from a job in a pipeline that belongs to the same pipeline hierarchy.

rspec:
  stage: test
  script: ./rspec
  needs:
    - pipeline: $PARENT_PIPELINE_ID
      job: build

With this syntax we allow rspec job to download artifacts from build job in $PARENT_PIPELINE_ID. This feature allow only pipelines within the same hierarchy. For "hierarchy" we intend the same parent-child pipelines group. For standalone pipelines (no parent-child) the hierarchy is equivalent to the same pipeline.

This syntax does not allow the current pipeline ID to be specified in needs:pipeline because standard needs:job should be used instead which refers to local needs. In essence, having the following parent-child pipeline structure: A (parent) -> B (child) -> C (child). A job in pipeline B could specify cross pipeline needs from the parent A or the child C, or any other child of A but not itself.

Specifying a pipeline ID outside the hierarchy will be considered invalid and the job will be dropped due to invalid dependencies.

Screenshots (strongly suggested)

# parent pipeline: .gitlab-ci.yml
build:
  stage: build
  script:
    - echo "building..."
    - echo "line_of_code 1000" > metrics.txt
  artifacts:
    paths: [metrics.txt]

trigger-tests:
  stage: test
  trigger:
    include: child.yml
    strategy: depend
  variables:
    UPSTREAM_PIPELINE_ID:  $CI_PIPELINE_ID

# child pipeline: child.yml
rspec:
  stage: test
  script:
    - echo "Running tests..."
    - cat metrics.txt
  needs:
    - pipeline: $UPSTREAM_PIPELINE_ID
      job: build

When invalid pipeline ID is passed we drop the job with

Query plans

Ci::BuildDependencies#jobs_in_pipeline_hierarchy generates the following query:

SQL statement

SELECT "ci_builds".* FROM "ci_builds" 
  WHERE "ci_builds"."type" = 'Ci::Build' 
  AND ("ci_builds"."retried" = false OR "ci_builds"."retried" IS NULL) 
  AND ("ci_builds"."status" IN ('success')) 
  AND "ci_builds"."commit_id" IN (
    WITH RECURSIVE "base_and_descendants" AS (
      (WITH RECURSIVE "base_and_ancestors" AS (
        (SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" = 208831043)
        UNION
        (SELECT "ci_pipelines".* FROM "ci_pipelines", "base_and_ancestors", "ci_sources_pipelines" 
          WHERE "ci_sources_pipelines"."source_pipeline_id" = "ci_pipelines"."id" 
          AND "ci_sources_pipelines"."pipeline_id" = "base_and_ancestors"."id" 
          AND "ci_sources_pipelines"."source_project_id" = "ci_sources_pipelines"."project_id")
      ) SELECT "ci_pipelines".* FROM "base_and_ancestors" AS "ci_pipelines")
      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"."commit_id" IN (208831026, 208831026) 
  AND "ci_builds"."name" IN ('gather-results', 'report-2');

• cold cache: https://explain.depesz.com/s/yGi6

Time: 53.801 ms
  - planning: 4.226 ms
  - execution: 49.575 ms
    - I/O read: 48.748 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 16 (~128.00 KiB) from the buffer pool
  - reads: 15 (~120.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

• warm cache: https://explain.depesz.com/s/2wFo

Time: 5.165 ms
  - planning: 4.730 ms
  - execution: 0.435 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 25 (~200.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Does this MR meet the acceptance criteria?

Conformity

• Changelog entry - Will be added when FF is enabled by default
• Documentation (if required) - will be added when FF is enabled by default
• Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• Separation of EE specific content

Availability and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
• Tested in all supported browsers
• Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

• Label as security and @ mention @gitlab-com/gl-security/appsec
• The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
• Security reports checked/validated by a reviewer from the AppSec team