Refactor Package Pipelines endpoint

What does this MR do and why?

This extracts the query logic inside the Package Pipelines endpoint into a dedicated finder class.

This MR started as a resolution of the N+1 queries situation in the Package Pipelines endpoint introduced in !117539 (merged).

During MR review and after some investigation, it was discovered that the expected way of creating package pipelines, which is to publish a package inside a CI job, will not result in an N+1 situation:

• Publish a package named mypackage, version 1.0.0 from a pipeline in project P1 -> creates the mypackage record, version 1.0.0, with the associated pipeline
• Publish a package named mypackage, version 1.0.0 from a pipeline in project P2 -> fails with an HTTP 403 response - the package is already published
• Publish a package named mypackage, version 1.0.1 from a pipeline in project P2 -> succeeds, but creates a new package record, different from the package record created earlier

The reason we see N+1 in !117539 (merged) is because of the faulty How to validate locally steps which stuffed records into ci_pipelines in order to have a package with pipeline records. If the validation steps were written similar to the described package creation steps above, we won't see the N+1 situation.

There is also very low load on the endpoint at the moment, and there aren't excessive db queries per endpoint hit: https://log.gprd.gitlab.net/app/r/s/shjPW

With these findings, it was decided to reduce the changes in this MR - remove the N+1 changes and just retain the refactoring.

💾 Database Analysis

The queries generated by both the master branch and the MR branch are the same.

Code in master branch:

::Ci::Pipeline.id_in(results.map(&:pipeline_id)).select(PIPELINE_COLUMNS).order_id_desc

in https://gitlab.com/gitlab-org/gitlab/-/blob/98f4fb73bcca74b2eccb3e6bf97fa5871d11136b/lib/api/project_packages.rb#L113

Code in MR branch:

::Packages::PipelinesFinder.new(results.map(&:pipeline_id)).execute

in https://gitlab.com/gitlab-org/gitlab/-/blob/2edb0c195d04ed99de14e46e9c2d9f5a8f13e620/lib/api/project_packages.rb#L111

Queries

Query 1

SELECT
    "ci_pipelines"."id",
    "ci_pipelines"."iid",
    "ci_pipelines"."project_id",
    "ci_pipelines"."sha",
    "ci_pipelines"."ref",
    "ci_pipelines"."status",
    "ci_pipelines"."source",
    "ci_pipelines"."created_at",
    "ci_pipelines"."updated_at",
    "ci_pipelines"."user_id"
FROM
    "ci_pipelines"
WHERE
    "ci_pipelines"."id" IN (
        953441823,
        953425309,
        953411285,
        953398673,
        953385681,
        953372709,
        953359926,
        953343024,
        953328688,
        953316454,
        953304588,
        953291731,
        953279944,
        953265134,
        952765598,
        952761165,
        952757149,
        952753413,
        952749100,
        952741646
    )
ORDER BY
    "ci_pipelines"."id" DESC

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/21054/commands/68798

Time: 93.886 ms
  - planning: 8.038 ms
  - execution: 85.848 ms
    - I/O read: 85.178 ms
    - I/O write: 0.000 ms

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

Query 2

SELECT "projects".* FROM "projects" WHERE "projects"."id" = 30977564 LIMIT 1

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21055/commands/68799

Time: 15.155 ms
  - planning: 7.517 ms
  - execution: 7.638 ms
    - I/O read: 7.439 ms
    - I/O write: 0.000 ms

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

Query 3

SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 13941255 LIMIT 1;

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21055/commands/68804

Time: 20.712 ms  
  - planning: 8.139 ms  
  - execution: 12.573 ms  
    - I/O read: 12.272 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 0 from the buffer pool  
  - reads: 5 (~40.00 KiB) from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0 

Query 4

SELECT "routes".* FROM "routes" WHERE "routes"."source_id" = 13941255 AND "routes"."source_type" = 'Namespace' LIMIT 1

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21055/commands/68806

Time: 22.148 ms  
  - planning: 2.476 ms  
  - execution: 19.672 ms  
    - I/O read: 19.454 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 0 from the buffer pool  
  - reads: 5 (~40.00 KiB) from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0  

How to set up and validate locally

This is a refactoring and there should be no behavioral changes. The Package Pipelines endpoint should behave just like before.

1. Create an empty project
2. Add these files to the project:

package.json (replace project_id with the actual ID of the project):

{
  "name": "mypackage",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "author": "",
  "license": "ISC",
  "publishConfig": {
    "registry": "http://gdk.test:3000/api/v4/projects/<project_id>/packages/npm/"
  },
  "dependencies": {
  }
}

.gitlab-ci.yml:

image: node:19.4.0

stages:
  - deploy

deploy:
  stage: deploy
  script:
    - echo "//gdk.test:3000/api/v4/projects/${CI_PROJECT_ID}/packages/npm/:_authToken=${CI_JOB_TOKEN}">.npmrc
    - npm publish

3. Wait for the CI job in step 2 to succeed, then open the Rails console and get the package ID

::Packages::Package.last

4. Hit the package pipelines endpoint

curl --header "PRIVATE-TOKEN: <a-valid-personal-access-token>" "http://gdk.test:3000/api/v4/projects/<project_id>/packages/<package_id>/pipelines"

The response should return one package pipeline record with a JSON structure similar to what is described in https://docs.gitlab.com/ee/api/packages.html#list-package-pipelines