Migrate inputs to spec on CI components table
What does this MR do and why?
catalog_resource_components
was created with an inputs
column that stores the inputs value from a CI template's header. However, the components table now needs to store CI steps as well as templates, and steps have outputs as well as inputs. To handle this, a new spec
column was introduced that stores the entire header.
This commit migrates the values from the inputs
column of existing CI component records to the spec
column so that we can remove the inputs
column in the next milestone. It appends an inputs:
key to the existing inputs hash so it has the correct form for the spec
column.
Changelog: other
Issue: #443662 (closed)
Terminology
- There are 2 types of CI component: templates and steps
- A CI header contains a
spec
that has inputs for CI templates and both inputs and outputs for CI steps
Migration Output
Up
main: == [advisory_lock_connection] object_id: 122300, pg_backend_pid: 74394
main: == 20240403121221 MigrateInputsToSpecOnCatalogResourceComponents: migrating ===
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- execute("UPDATE catalog_resource_components\nSET spec = jsonb_set('{}'::jsonb, '{inputs}', inputs::jsonb)\nWHERE id BETWEEN 9 AND 18\nAND spec = '{}' AND inputs <> '{}'\n")
main: -> 0.0022s
main: == 20240403121221 MigrateInputsToSpecOnCatalogResourceComponents: migrated (0.0207s)
main: == [advisory_lock_connection] object_id: 122300, pg_backend_pid: 74394
ci: == [advisory_lock_connection] object_id: 122600, pg_backend_pid: 74396
ci: == 20240403121221 MigrateInputsToSpecOnCatalogResourceComponents: migrating ===
ci: -- The migration is skipped since it modifies the schemas: [:gitlab_main].
ci: -- This database can only apply migrations in one of the following schemas: [:gitlab_ci, :gitlab_internal, :gitlab_shared].
ci: == 20240403121221 MigrateInputsToSpecOnCatalogResourceComponents: migrated (0.0067s)
Down
No-op
How to set up and validate locally
- Create a CI component that has an inputs value and no spec value. If you have no CI catalog resources in your GDK, use all of these commands:
project = Project.first # or any project user = User.first resource = Ci::Catalog::Resource.create!(project: project) release = Release.create!(project: project, tag: 'test', author: user) version = Ci::Catalog::Resources::Version.create!(project: project, release: release, catalog_resource: resource, version: '1.0.0') component = Ci::Catalog::Resources::Component.create!( project: project, catalog_resource: resource, version: version, name: 'test', spec: {}, inputs: { test_input: nil } )
- Run the migration
- Check the component spec value:
component.reload.spec => { 'inputs' => { 'test_input' => nil } }
Related to #443662 (closed)
Merge request reports
Activity
changed milestone to %16.11
assigned to @avielle
- A deleted user
added database databasereview pending labels
1 Warning New migrations added but db/structure.sql wasn't updated Usually, when adding new migrations, db/structure.sql should be
updated too (unless the migration isn't changing the DB schema
and isn't the most recent one).Reviewer roulette
Category Reviewer Maintainer database @mwoolf
(UTC+1, 1 hour behind author)
@tigerwnz
(UTC+10, 8 hours ahead of author)
Please check reviewer's status!
Please refer to documentation page for guidance on how you can benefit from the Reviewer Roulette, or use the GitLab Review Workload Dashboard to find other available reviewers.
If needed, you can retry the
danger-review
job that generated this comment.Generated by
DangerNote: after discussion on Slack, it was determined that we should use a post-deployment migration rather than a batched background migration due to the low number of records in
catalog_resource_components
The thread: https://gitlab.slack.com/archives/C3NBYFJ6N/p1712145053628609
Database migrations (on the main database)
1 Warnings 20240403121221 - MigrateInputsToSpecOnCatalogResourceComponents had a query that exceeded
timing guidelines. Run time should not exceed 100ms, but the longest was 830.56ms, and the average
was 270.26ms. Please consider possible options to improve the query performance.UPDATE
catalog_resource_components
SET spec = jsonb_set($1::jsonb, $2, inputs::jsonb)
WHERE id BETWEEN $3 AND $4 AND spec = $5 AND inputs <> $6Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).
Migration Type Total runtime Result DB size change 20240403121221 - MigrateInputsToSpecOnCatalogResourceComponents Post deploy 6.0 s +7.98 MiB Runtime Histogram for all migrations
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 21 0.1 seconds - 1 second 7 1 second - 5 seconds 0 5 seconds - 15 seconds 0 15 seconds - 5 minutes 0 5 minutes + 0 Migration: 20240403121221 - MigrateInputsToSpecOnCatalogResourceComponents- Type: Post deploy
- Duration: 6.0 s
- Database size change: +7.98 MiB
Calls Total Time Max Time Mean Time Rows Query 8 2162.1 ms 830.6 ms 270.3 ms 6851 UPDATE catalog_resource_components
SET spec = jsonb_set($1::jsonb, $2, inputs::jsonb)
WHERE id BETWEEN $3 AND $4 AND spec = $5 AND inputs <> $68 40.0 ms 13.9 ms 5.0 ms 7 SELECT "catalog_resource_components"."id" FROM "catalog_resource_components" WHERE "catalog_resource_components"."id" >= $1
ORDER BY "catalog_resource_components"."id" ASC
LIMIT $2
OFFSET $31 2.0 ms 2.0 ms 2.0 ms 1 SELECT "catalog_resource_components"."id" FROM "catalog_resource_components" ORDER BY "catalog_resource_components"."id" ASC
LIMIT $11 1.0 ms 1.0 ms 1.0 ms 1 SELECT "feature_gates"."key", "feature_gates"."value" FROM "feature_gates" WHERE "feature_gates"."feature_key" = $1
7 0.5 ms 0.1 ms 0.1 ms 7 SELECT MIN(id), MAX(id)
FROM "catalog_resource_components" WHERE "catalog_resource_components"."id" >= $1 AND "catalog_resource_components"."id" < $2
LIMIT $31 0.1 ms 0.1 ms 0.1 ms 1 SELECT MIN(id), MAX(id)
FROM "catalog_resource_components" WHERE "catalog_resource_components"."id" >= $1
LIMIT $22 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for MigrateInputsToSpecOnCatalogResourceComponents
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 21 0.1 seconds - 1 second 7 1 second - 5 seconds 0 5 seconds - 15 seconds 0 15 seconds - 5 minutes 0 5 minutes + 0 Other information
No other migrations pending on GitLab.com
Clone details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-3070088-13402189-main
2024-04-03T14:59:46Z 2024-04-03T08:26:40Z 2024-04-04 03:04:48 +0000 database-testing-3070088-13402189-ci
2024-04-03T14:59:46Z 2024-04-03T12:46:05Z 2024-04-04 03:04:48 +0000 Database migrations (on the ci database)
Migrations included in this change have been executed on gitlab.com data for testing purposes. For details, please see the migration testing pipeline (limited access).
Migration Type Total runtime Result DB size change 20240403121221 - MigrateInputsToSpecOnCatalogResourceComponents Post deploy 3.0 s +0.00 B Runtime Histogram for all migrations
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 2 0.1 seconds - 1 second 0 1 second - 5 seconds 0 5 seconds - 15 seconds 0 15 seconds - 5 minutes 0 5 minutes + 0 Migration: 20240403121221 - MigrateInputsToSpecOnCatalogResourceComponents
- Type: Post deploy
- Duration: 3.0 s
- Database size change: +0.00 B
Calls Total Time Max Time Mean Time Rows Query 2 0.0 ms 0.0 ms 0.0 ms 2 SELECT pg_backend_pid()
Histogram for MigrateInputsToSpecOnCatalogResourceComponents
Query Runtime Count 0 seconds - 0.01 seconds 0 0.01 seconds - 0.1 seconds 2 0.1 seconds - 1 second 0 1 second - 5 seconds 0 5 seconds - 15 seconds 0 15 seconds - 5 minutes 0 5 minutes + 0 Other information
No other migrations pending on GitLab.com
Clone details
Clone ID Clone Created At Clone Data Timestamp Expected Removal Time database-testing-3070088-13402189-main
2024-04-03T14:59:46Z 2024-04-03T08:26:40Z 2024-04-04 03:04:48 +0000 database-testing-3070088-13402189-ci
2024-04-03T14:59:46Z 2024-04-03T12:46:05Z 2024-04-04 03:04:48 +0000
Brought to you by gitlab-org/database-team/gitlab-com-database-testing. Epic
mentioned in epic &7462
- A deleted user
added database-testing-automation label
added 195 commits
-
2288c30c...bb24605a - 194 commits from branch
master
- 9ff7f087 - Migrate inputs to spec on CI components table
-
2288c30c...bb24605a - 194 commits from branch
- Resolved by Tiger Watson
Hi @mwoolf!
Can you please do the first review?Note - !148547 (comment 1844129876)
requested review from @mwoolf
added databasereviewed label and removed databasereview pending label
added databaseapproved label and removed databasereviewed label
added pipeline:mr-approved label
- Resolved by Tiger Watson
@tigerwnz
, thanks for approving this merge request.This is the first time the merge request has been approved. To ensure we don't only run predictive pipelines, and we don't break
master
, a new pipeline will be started shortly.Please wait for the pipeline to start before resolving this discussion and set auto-merge for the new pipeline. See merging a merge request for more details.
enabled an automatic merge when the pipeline for 9cd7d66f succeeds
Hello @avielle
The database team is looking for ways to improve the database review process and we would love your help!
If you'd be open to someone on the database team reaching out to you for a chat, or if you'd like to leave some feedback asynchronously, just post a reply to this comment mentioning:
@gitlab-org/database-team
And someone will be by shortly!
Thanks for your help!
This message was generated automatically. You're welcome to improve it.
mentioned in commit 28b676ab
added workflowstaging-canary label
added workflowcanary label and removed workflowstaging-canary label
added workflowstaging label and removed workflowcanary label
added workflowproduction label and removed workflowstaging label
added workflowpost-deploy-db-staging label and removed workflowproduction label
added workflowpost-deploy-db-production label and removed workflowpost-deploy-db-staging label
added releasedcandidate label
mentioned in merge request kubitus-project/kubitus-installer!2945 (merged)
added releasedpublished label and removed releasedcandidate label
added pipelinetier-3 label
mentioned in epic gitlab-org#7462