Skip to content

Draft: Create table for CI catalog component usage tracking

Leaminn Ma requested to merge add-catalog-component-usages-table into master

What does this MR do and why?

This MR creates a new database table to be used for tracking Ci catalog component usage.

Resolves #440382 (closed).

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Query plan

Up

main: == [advisory_lock_connection] object_id: 118860, pg_backend_pid: 97398
main: == 20240215191519 CreateCatalogResourceComponentUsagesTable: migrating ========
main: -- create_table(:catalog_resource_component_usages)
main:    -> 0.0067s
main: == 20240215191519 CreateCatalogResourceComponentUsagesTable: migrated (0.0208s) 

main: == [advisory_lock_connection] object_id: 118860, pg_backend_pid: 97398
ci: == [advisory_lock_connection] object_id: 119120, pg_backend_pid: 97402
ci: == 20240215191519 CreateCatalogResourceComponentUsagesTable: migrating ========
ci: -- create_table(:catalog_resource_component_usages)
ci:    -> 0.0066s
I, [2024-02-20T12:57:03.537772 #97140]  INFO -- : Database: 'ci', Table: 'catalog_resource_component_usages': Lock Writes
ci: == 20240215191519 CreateCatalogResourceComponentUsagesTable: migrated (0.0169s) 

ci: == [advisory_lock_connection] object_id: 119120, pg_backend_pid: 97402
main: == [advisory_lock_connection] object_id: 119460, pg_backend_pid: 97405
main: == 20240215202302 AddComponentFkToCatalogResourceComponentUsages: migrating ===
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE catalog_resource_component_usages ADD CONSTRAINT fk_0a9389c2db FOREIGN KEY (component_id) REFERENCES catalog_resource_components (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0012s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- execute("ALTER TABLE catalog_resource_component_usages VALIDATE CONSTRAINT fk_0a9389c2db;")
main:    -> 0.0014s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20240215202302 AddComponentFkToCatalogResourceComponentUsages: migrated (0.0265s) 

main: == [advisory_lock_connection] object_id: 119460, pg_backend_pid: 97405
ci: == [advisory_lock_connection] object_id: 119660, pg_backend_pid: 97407
ci: == 20240215202302 AddComponentFkToCatalogResourceComponentUsages: migrating ===
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE catalog_resource_component_usages ADD CONSTRAINT fk_0a9389c2db FOREIGN KEY (component_id) REFERENCES catalog_resource_components (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0021s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- execute("ALTER TABLE catalog_resource_component_usages VALIDATE CONSTRAINT fk_0a9389c2db;")
ci:    -> 0.0012s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0001s
ci: == 20240215202302 AddComponentFkToCatalogResourceComponentUsages: migrated (0.0190s) 

ci: == [advisory_lock_connection] object_id: 119660, pg_backend_pid: 97407
main: == [advisory_lock_connection] object_id: 121060, pg_backend_pid: 97410
main: == 20240215202412 AddResourceFkToCatalogResourceComponentUsages: migrating ====
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE catalog_resource_component_usages ADD CONSTRAINT fk_869116c793 FOREIGN KEY (catalog_resource_id) REFERENCES catalog_resources (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0007s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- execute("ALTER TABLE catalog_resource_component_usages VALIDATE CONSTRAINT fk_869116c793;")
main:    -> 0.0014s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20240215202412 AddResourceFkToCatalogResourceComponentUsages: migrated (0.0169s) 

main: == [advisory_lock_connection] object_id: 121060, pg_backend_pid: 97410
ci: == [advisory_lock_connection] object_id: 121260, pg_backend_pid: 97412
ci: == 20240215202412 AddResourceFkToCatalogResourceComponentUsages: migrating ====
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE catalog_resource_component_usages ADD CONSTRAINT fk_869116c793 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.0002s
ci: -- execute("ALTER TABLE catalog_resource_component_usages VALIDATE CONSTRAINT fk_869116c793;")
ci:    -> 0.0010s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20240215202412 AddResourceFkToCatalogResourceComponentUsages: migrated (0.0179s) 

ci: == [advisory_lock_connection] object_id: 121260, pg_backend_pid: 97412
main: == [advisory_lock_connection] object_id: 123020, pg_backend_pid: 97415
main: == 20240215202522 AddProjectFkToCatalogResourceComponentUsages: migrating =====
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- execute("ALTER TABLE catalog_resource_component_usages ADD CONSTRAINT fk_5903cb76e9 FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE NOT VALID;")
main:    -> 0.0010s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- execute("ALTER TABLE catalog_resource_component_usages VALIDATE CONSTRAINT fk_5903cb76e9;")
main:    -> 0.0047s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: == 20240215202522 AddProjectFkToCatalogResourceComponentUsages: migrated (0.0207s) 

main: == [advisory_lock_connection] object_id: 123020, pg_backend_pid: 97415
ci: == [advisory_lock_connection] object_id: 131280, pg_backend_pid: 97417
ci: == 20240215202522 AddProjectFkToCatalogResourceComponentUsages: migrating =====
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- execute("ALTER TABLE catalog_resource_component_usages ADD CONSTRAINT fk_5903cb76e9 FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE NOT VALID;")
ci:    -> 0.0012s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0001s
ci: -- execute("ALTER TABLE catalog_resource_component_usages VALIDATE CONSTRAINT fk_5903cb76e9;")
ci:    -> 0.0039s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20240215202522 AddProjectFkToCatalogResourceComponentUsages: migrated (0.0207s) 

ci: == [advisory_lock_connection] object_id: 131280, pg_backend_pid: 97417

Down

main: == [advisory_lock_connection] object_id: 117760, pg_backend_pid: 92975
main: == 20240215202522 AddProjectFkToCatalogResourceComponentUsages: reverting =====
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:catalog_resource_component_usages, {:column=>:project_id})
main:    -> 0.0126s
main: == 20240215202522 AddProjectFkToCatalogResourceComponentUsages: reverted (0.0230s) 

main: == [advisory_lock_connection] object_id: 117760, pg_backend_pid: 92975
ci: == [advisory_lock_connection] object_id: 117740, pg_backend_pid: 93493
ci: == 20240215202522 AddProjectFkToCatalogResourceComponentUsages: reverting =====
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- remove_foreign_key(:catalog_resource_component_usages, {:column=>:project_id})
ci:    -> 0.0148s
ci: == 20240215202522 AddProjectFkToCatalogResourceComponentUsages: reverted (0.0314s) 

ci: == [advisory_lock_connection] object_id: 117740, pg_backend_pid: 93493
main: == [advisory_lock_connection] object_id: 117740, pg_backend_pid: 93917
main: == 20240215202412 AddResourceFkToCatalogResourceComponentUsages: reverting ====
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:catalog_resource_component_usages, {:column=>:catalog_resource_id})
main:    -> 0.0171s
main: == 20240215202412 AddResourceFkToCatalogResourceComponentUsages: reverted (0.0305s) 

main: == [advisory_lock_connection] object_id: 117740, pg_backend_pid: 93917
ci: == [advisory_lock_connection] object_id: 117740, pg_backend_pid: 94332
ci: == 20240215202412 AddResourceFkToCatalogResourceComponentUsages: reverting ====
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- remove_foreign_key(:catalog_resource_component_usages, {:column=>:catalog_resource_id})
ci:    -> 0.0152s
ci: == 20240215202412 AddResourceFkToCatalogResourceComponentUsages: reverted (0.0320s) 

ci: == [advisory_lock_connection] object_id: 117740, pg_backend_pid: 94332
main: == [advisory_lock_connection] object_id: 117740, pg_backend_pid: 94746
main: == 20240215202302 AddComponentFkToCatalogResourceComponentUsages: reverting ===
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- remove_foreign_key(:catalog_resource_component_usages, {:column=>:component_id})
main:    -> 0.0171s
main: == 20240215202302 AddComponentFkToCatalogResourceComponentUsages: reverted (0.0298s) 

main: == [advisory_lock_connection] object_id: 117740, pg_backend_pid: 94746
ci: == [advisory_lock_connection] object_id: 117800, pg_backend_pid: 95153
ci: == 20240215202302 AddComponentFkToCatalogResourceComponentUsages: reverting ===
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- remove_foreign_key(:catalog_resource_component_usages, {:column=>:component_id})
ci:    -> 0.0155s
ci: == 20240215202302 AddComponentFkToCatalogResourceComponentUsages: reverted (0.0319s) 

ci: == [advisory_lock_connection] object_id: 117800, pg_backend_pid: 95153
main: == [advisory_lock_connection] object_id: 117740, pg_backend_pid: 95578
main: == 20240215191519 CreateCatalogResourceComponentUsagesTable: reverting ========
main: -- drop_table(:catalog_resource_component_usages)
main:    -> 0.0016s
main: == 20240215191519 CreateCatalogResourceComponentUsagesTable: reverted (0.0065s) 

main: == [advisory_lock_connection] object_id: 117740, pg_backend_pid: 95578
ci: == [advisory_lock_connection] object_id: 117740, pg_backend_pid: 95991
ci: == 20240215191519 CreateCatalogResourceComponentUsagesTable: reverting ========
ci: -- drop_table(:catalog_resource_component_usages)
ci:    -> 0.0015s
ci: == 20240215191519 CreateCatalogResourceComponentUsagesTable: reverted (0.0112s) 

ci: == [advisory_lock_connection] object_id: 117740, pg_backend_pid: 95991

Related to #440382 (closed)

Edited by Leaminn Ma

Merge request reports