Create catalog_resource_versions table (!124668) · Merge requests · GitLab.org / GitLab

Leaminn Ma requested to merge create-catalog-resource-versions-table into master Jun 26, 2023

What does this MR do and why?

This MR creates a new table, catalog_resource_versions, as well as its model and associations. The purpose of this table is to contain the catalog resource versions (equivalent to project releases) that contain valid CI components.

The schema is the following:

catalog_resource_versions(
  catalog_resource_id FK to catalog_resources.id
  release_id FK to releases.id
  project_id FK to projects.id
  created_at
)

Note: Per the migration documentation (https://docs.gitlab.com/ee/development/migration_style_guide.html#creating-a-new-table-when-we-have-two-foreign-keys), the foreign keys were added as separate migrations.

Resolves #415286 (closed).

Migrations

main: == [advisory_lock_connection] object_id: 223540, pg_backend_pid: 30534
main: == 20230626211305 CreateCatalogResourceVersions: migrating ====================
main: -- create_table(:catalog_resource_versions)
main:    -> 0.0077s
main: == 20230626211305 CreateCatalogResourceVersions: migrated (0.1137s) ===========

main: == [advisory_lock_connection] object_id: 223540, pg_backend_pid: 30534
ci: == [advisory_lock_connection] object_id: 223900, pg_backend_pid: 30536
ci: == 20230626211305 CreateCatalogResourceVersions: migrating ====================
ci: -- create_table(:catalog_resource_versions)
ci:    -> 0.0068s
I, [2023-06-26T18:19:26.273943 #30355]  INFO -- : Database: 'ci', Table: 'catalog_resource_versions': Lock Writes
ci: == 20230626211305 CreateCatalogResourceVersions: migrated (0.0187s) ===========

ci: == [advisory_lock_connection] object_id: 223900, pg_backend_pid: 30536
main: == [advisory_lock_connection] object_id: 224080, pg_backend_pid: 30539
main: == 20230626215602 AddReleaseFkToCatalogResourceVersions: migrating ============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE catalog_resource_versions ADD CONSTRAINT fk_15376d917e FOREIGN KEY (release_id) REFERENCES releases (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0012s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- execute("ALTER TABLE catalog_resource_versions VALIDATE CONSTRAINT fk_15376d917e;")
main:    -> 0.0015s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20230626215602 AddReleaseFkToCatalogResourceVersions: migrated (0.0401s) ===

main: == [advisory_lock_connection] object_id: 224080, pg_backend_pid: 30539
ci: == [advisory_lock_connection] object_id: 224280, pg_backend_pid: 30541
ci: == 20230626215602 AddReleaseFkToCatalogResourceVersions: migrating ============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE catalog_resource_versions ADD CONSTRAINT fk_15376d917e FOREIGN KEY (release_id) REFERENCES releases (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0011s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- execute("ALTER TABLE catalog_resource_versions VALIDATE CONSTRAINT fk_15376d917e;")
ci:    -> 0.0018s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20230626215602 AddReleaseFkToCatalogResourceVersions: migrated (0.0277s) ===

ci: == [advisory_lock_connection] object_id: 224280, pg_backend_pid: 30541
main: == [advisory_lock_connection] object_id: 224520, pg_backend_pid: 30544
main: == 20230626215614 AddProjectFkToCatalogResourceVersions: migrating ============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE catalog_resource_versions ADD CONSTRAINT fk_7ad8849db4 FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0021s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0006s
main: -- execute("ALTER TABLE catalog_resource_versions VALIDATE CONSTRAINT fk_7ad8849db4;")
main:    -> 0.0061s
main: -- execute("RESET statement_timeout")
main:    -> 0.0007s
main: == 20230626215614 AddProjectFkToCatalogResourceVersions: migrated (0.0325s) ===

main: == [advisory_lock_connection] object_id: 224520, pg_backend_pid: 30544
ci: == [advisory_lock_connection] object_id: 224700, pg_backend_pid: 30546
ci: == 20230626215614 AddProjectFkToCatalogResourceVersions: migrating ============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE catalog_resource_versions ADD CONSTRAINT fk_7ad8849db4 FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0012s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- execute("ALTER TABLE catalog_resource_versions VALIDATE CONSTRAINT fk_7ad8849db4;")
ci:    -> 0.0051s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20230626215614 AddProjectFkToCatalogResourceVersions: migrated (0.0323s) ===

ci: == [advisory_lock_connection] object_id: 224700, pg_backend_pid: 30546
main: == [advisory_lock_connection] object_id: 224960, pg_backend_pid: 30550
main: == 20230626215638 AddCatalogResourceFkToCatalogResourceVersions: migrating ====
main: -- transaction_open?()
main:    -> 0.0000s
main: -- transaction_open?()
main:    -> 0.0000s
main: -- execute("ALTER TABLE catalog_resource_versions ADD CONSTRAINT fk_b670eae96b FOREIGN KEY (catalog_resource_id) REFERENCES catalog_resources (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0008s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- execute("ALTER TABLE catalog_resource_versions VALIDATE CONSTRAINT fk_b670eae96b;")
main:    -> 0.0022s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20230626215638 AddCatalogResourceFkToCatalogResourceVersions: migrated (0.0203s) 

main: == [advisory_lock_connection] object_id: 224960, pg_backend_pid: 30550
ci: == [advisory_lock_connection] object_id: 226120, pg_backend_pid: 30552
ci: == 20230626215638 AddCatalogResourceFkToCatalogResourceVersions: migrating ====
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE catalog_resource_versions ADD CONSTRAINT fk_b670eae96b FOREIGN KEY (catalog_resource_id) REFERENCES catalog_resources (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0007s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- execute("ALTER TABLE catalog_resource_versions VALIDATE CONSTRAINT fk_b670eae96b;")
ci:    -> 0.0014s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20230626215638 AddCatalogResourceFkToCatalogResourceVersions: migrated (0.0223s) 

ci: == [advisory_lock_connection] object_id: 226120, pg_backend_pid: 30552

Down

main: == [advisory_lock_connection] object_id: 223180, pg_backend_pid: 31220
main: == 20230626215638 AddCatalogResourceFkToCatalogResourceVersions: reverting ====
main: -- transaction_open?()
main:    -> 0.0000s
main: -- remove_foreign_key(:catalog_resource_versions, {:column=>:catalog_resource_id})
main:    -> 0.0950s
main: == 20230626215638 AddCatalogResourceFkToCatalogResourceVersions: reverted (0.1096s) 

main: == [advisory_lock_connection] object_id: 223180, pg_backend_pid: 31220
ci: == [advisory_lock_connection] object_id: 223200, pg_backend_pid: 31659
ci: == 20230626215638 AddCatalogResourceFkToCatalogResourceVersions: reverting ====
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- remove_foreign_key(:catalog_resource_versions, {:column=>:catalog_resource_id})
ci:    -> 0.1067s
ci: == 20230626215638 AddCatalogResourceFkToCatalogResourceVersions: reverted (0.1284s) 

ci: == [advisory_lock_connection] object_id: 223200, pg_backend_pid: 31659
main: == [advisory_lock_connection] object_id: 223140, pg_backend_pid: 32097
main: == 20230626215614 AddProjectFkToCatalogResourceVersions: reverting ============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- remove_foreign_key(:catalog_resource_versions, {:column=>:project_id})
main:    -> 0.1116s
main: == 20230626215614 AddProjectFkToCatalogResourceVersions: reverted (0.1532s) ===

main: == [advisory_lock_connection] object_id: 223140, pg_backend_pid: 32097
ci: == [advisory_lock_connection] object_id: 223120, pg_backend_pid: 32530
ci: == 20230626215614 AddProjectFkToCatalogResourceVersions: reverting ============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- remove_foreign_key(:catalog_resource_versions, {:column=>:project_id})
ci:    -> 0.1070s
ci: == 20230626215614 AddProjectFkToCatalogResourceVersions: reverted (0.1308s) ===

ci: == [advisory_lock_connection] object_id: 223120, pg_backend_pid: 32530
main: == [advisory_lock_connection] object_id: 223140, pg_backend_pid: 32963
main: == 20230626215602 AddReleaseFkToCatalogResourceVersions: reverting ============
main: -- transaction_open?()
main:    -> 0.0000s
main: -- remove_foreign_key(:catalog_resource_versions, {:column=>:release_id})
main:    -> 0.1055s
main: == 20230626215602 AddReleaseFkToCatalogResourceVersions: reverted (0.2128s) ===

main: == [advisory_lock_connection] object_id: 223140, pg_backend_pid: 32963
ci: == [advisory_lock_connection] object_id: 223120, pg_backend_pid: 33402
ci: == 20230626215602 AddReleaseFkToCatalogResourceVersions: reverting ============
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- remove_foreign_key(:catalog_resource_versions, {:column=>:release_id})
ci:    -> 0.1099s
ci: == 20230626215602 AddReleaseFkToCatalogResourceVersions: reverted (0.1634s) ===

ci: == [advisory_lock_connection] object_id: 223120, pg_backend_pid: 33402
main: == [advisory_lock_connection] object_id: 223120, pg_backend_pid: 33842
main: == 20230626211305 CreateCatalogResourceVersions: reverting ====================
main: -- drop_table(:catalog_resource_versions)
main:    -> 0.0022s
main: == 20230626211305 CreateCatalogResourceVersions: reverted (0.0071s) ===========

main: == [advisory_lock_connection] object_id: 223120, pg_backend_pid: 33842
ci: == [advisory_lock_connection] object_id: 223140, pg_backend_pid: 34277
ci: == 20230626211305 CreateCatalogResourceVersions: reverting ====================
ci: -- drop_table(:catalog_resource_versions)
ci:    -> 0.0019s
ci: == 20230626211305 CreateCatalogResourceVersions: reverted (0.0119s) ===========

ci: == [advisory_lock_connection] object_id: 223140, pg_backend_pid: 34277

MR acceptance checklist

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

I have evaluated the MR acceptance checklist for this MR.

Related to #415286 (closed)

Edited Jun 27, 2023 by Leaminn Ma

Create catalog_resource_versions table

What does this MR do and why?

Migrations

MR acceptance checklist

Merge request reports