Skip to content

Add CI catalog resource VersionsFinder and update Version model

Leaminn Ma requested to merge ci-catalog-resource-update-versions-logic into master

What does this MR do and why?

Background:

With !134148 (merged), a catalog resource Version is created upon Release creation if the commit has valid components. A Version is essentially a Release in the context of catalog resources. The table catalog_resource_versions has a 1:1 relationship with releases.

So far, we have been using project Releases as the logic for Versions. Now we need to replace the logic from catalog_resource.project.releases to catalog_resource.versions.

This MR is the first of 3 MRs to handle the logic migration.

This MR:

  • Adds and adapts the necessary Release model methods to the Ci::Catalog::Resources::Version model.
  • Creates Ci::Catalog::Resources::VersionsFinder to eventually replace the use of ReleasesFinder when resolving the versions and latest_version fields on Types::Ci::Catalog::ResourceType.

Database queries:

Since all the Release data is in the releases table, we currently have to JOIN catalog_resource_versions with releases when sorting Versions and for obtaining the latest Version for a batch of projects. This JOIN-ing is known to cause inefficient queries. In #430117 (closed), we plan to denormalize the released_at column and entirely remove the need to join with releases. For now, we acknowledge that the performance of these queries are not ideal.

Partially resolves #429707 (closed)

Query plans

SELECT "catalog_resource_versions".*
FROM "catalog_resource_versions"
ORDER BY "catalog_resource_versions"."created_at" DESC;

Link: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/23584/commands/75822

Note: The queries below are known to be inefficient due to JOIN-ing with releases. We plan to improve the query plan in #430117 (closed). There's currently no data in catalog_resource_versions, so the plans look good at the moment.

SELECT "catalog_resource_versions".*
FROM "catalog_resource_versions"
INNER JOIN "releases" ON "releases"."id" = "catalog_resource_versions"."release_id"
ORDER BY "releases"."released_at" DESC, "releases"."id" DESC;

Link: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/23584/commands/75823

SELECT "catalog_resource_versions".*
FROM (
  VALUES (7),(6),(5),(2),(1),(8),(9),(10)
) catalog_resources(id)
INNER JOIN LATERAL (
  SELECT "catalog_resource_versions".*
  FROM "catalog_resource_versions"
  INNER JOIN releases AS rel ON rel.id = catalog_resource_versions.release_id
  WHERE "catalog_resources"."id" = "catalog_resource_versions"."catalog_resource_id"
  ORDER BY rel.released_at DESC
  LIMIT 1
) catalog_resource_versions ON TRUE;

Link: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/23611/commands/75886

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #429707 (closed)

Edited by Furkan Ayhan

Merge request reports

Loading