Update queries to include partition_id filter for source_pipeline

Problem

We need to update queries to include partition_id filter for source_pipelines:

• pipeline.source_job&.name
• source_bridge&.dependent?

SELECT p_ci_builds.*
FROM p_ci_builds
INNER JOIN ci_sources_pipelines ON p_ci_builds.id = ci_sources_pipelines.source_job_id
WHERE ci_sources_pipelines.pipeline_id = 561
LIMIT 1

SELECT p_ci_builds.*
FROM p_ci_builds
INNER JOIN ci_sources_pipelines ON p_ci_builds.id = ci_sources_pipelines.source_job_id
WHERE p_ci_builds.type = 'Ci::Bridge'
AND ci_sources_pipelines.pipeline_id = 561
LIMIT 1

Solution

The partition_id column must be used for filtering the pipelines that are associated with pipeline_id, not the source jobs/source pipelines.

A Proposal

In the near future we will be running Rails 7.1 in production and we can use the updates to query constraints to enforce the partition_id via built-in AR code instead of our own implemention.

cc @mbobin @panoskanell