Add denormalized columns name and description to catalog_resources

What does this MR do and why?

• Added a regular migration to add the columns name and description to catalog_resources. These columns have the same data type and trigram indexes as in the projects table.
• Added logic to keep the columns in sync with the projects table when:
  • A project is marked as a catalog resource (the record is created).
  • The project name/description is updated.
• Added a post deploy data migration to backfill the columns for existing records catalog_resources.
  • This table is relatively new and the feature is not widely used; the dataset is very small in production.

Resolves Step 1 & 2 of #427928 (closed)

How to set up and validate locally

1. Run the migrations:

bundle exec rails db:migrate

2. Create a new project with a README and a description in the UI. Then set it as a catalog resource in the console:

project = Project.find(<YOUR-PROJECT-ID>)
catalog_resource = Ci::Catalog::Resource.create(project: project)

3. First observe that your catalog resource has the name and description values that match your Project's values.

project.slice(:name, :description)
catalog_resource.slice(:name, :description)

4. In the UI, go to update your Project's name or description.

After saving the changes, observe that your catalog resource's name/description has been updated accordingly.

catalog_resource.reload.slice(:name, :description)

Migrations

Up

main: == [advisory_lock_connection] object_id: 179900, pg_backend_pid: 1892
main: == 20231019180421 AddNameDescriptionToCatalogResources: migrating =============
main: -- add_column(:catalog_resources, :name, :varchar, {:null=>true})
main:    -> 0.0015s
main: -- add_column(:catalog_resources, :description, :text, {:null=>true})
main:    -> 0.0003s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0840s
main: -- index_exists?(:catalog_resources, :name, {:name=>"index_catalog_resources_on_name_trigram", :using=>:gin, :opclass=>{:name=>:gin_trgm_ops}, :algorithm=>:concurrently})
main:    -> 0.0018s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- add_index(:catalog_resources, :name, {:name=>"index_catalog_resources_on_name_trigram", :using=>:gin, :opclass=>{:name=>:gin_trgm_ops}, :algorithm=>:concurrently})
main:    -> 0.0025s
main: -- execute("RESET statement_timeout")
main:    -> 0.0001s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0003s
main: -- index_exists?(:catalog_resources, :description, {:name=>"index_catalog_resources_on_description_trigram", :using=>:gin, :opclass=>{:description=>:gin_trgm_ops}, :algorithm=>:concurrently})
main:    -> 0.0012s
main: -- add_index(:catalog_resources, :description, {:name=>"index_catalog_resources_on_description_trigram", :using=>:gin, :opclass=>{:description=>:gin_trgm_ops}, :algorithm=>:concurrently})
main:    -> 0.0010s
main: == 20231019180421 AddNameDescriptionToCatalogResources: migrated (0.1065s) ====

main: == [advisory_lock_connection] object_id: 179900, pg_backend_pid: 1892
ci: == [advisory_lock_connection] object_id: 180200, pg_backend_pid: 1894
ci: == 20231019180421 AddNameDescriptionToCatalogResources: migrating =============
ci: -- add_column(:catalog_resources, :name, :varchar, {:null=>true})
ci:    -> 0.0006s
ci: -- add_column(:catalog_resources, :description, :text, {:null=>true})
ci:    -> 0.0003s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- index_exists?(:catalog_resources, :name, {:name=>"index_catalog_resources_on_name_trigram", :using=>:gin, :opclass=>{:name=>:gin_trgm_ops}, :algorithm=>:concurrently})
ci:    -> 0.0017s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:catalog_resources, :name, {:name=>"index_catalog_resources_on_name_trigram", :using=>:gin, :opclass=>{:name=>:gin_trgm_ops}, :algorithm=>:concurrently})
ci:    -> 0.0022s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- index_exists?(:catalog_resources, :description, {:name=>"index_catalog_resources_on_description_trigram", :using=>:gin, :opclass=>{:description=>:gin_trgm_ops}, :algorithm=>:concurrently})
ci:    -> 0.0016s
ci: -- add_index(:catalog_resources, :description, {:name=>"index_catalog_resources_on_description_trigram", :using=>:gin, :opclass=>{:description=>:gin_trgm_ops}, :algorithm=>:concurrently})
ci:    -> 0.0010s
ci: == 20231019180421 AddNameDescriptionToCatalogResources: migrated (0.0251s) ====

ci: == [advisory_lock_connection] object_id: 180200, pg_backend_pid: 1894
main: == [advisory_lock_connection] object_id: 180360, pg_backend_pid: 1897
main: == 20231019223224 BackfillCatalogResourcesNameAndDescription: migrating =======
main: -- execute("      UPDATE catalog_resources\n      SET name = projects.name,\n          description = projects.description\n      FROM projects\n      WHERE catalog_resources.project_id = projects.id\n")
main:    -> 0.0064s
main: == 20231019223224 BackfillCatalogResourcesNameAndDescription: migrated (0.0095s) 

main: == [advisory_lock_connection] object_id: 180360, pg_backend_pid: 1897
ci: == [advisory_lock_connection] object_id: 180460, pg_backend_pid: 1899
ci: == 20231019223224 BackfillCatalogResourcesNameAndDescription: 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: == 20231019223224 BackfillCatalogResourcesNameAndDescription: migrated (0.0077s) 

ci: == [advisory_lock_connection] object_id: 180460, pg_backend_pid: 1899

Down

main: == [advisory_lock_connection] object_id: 179380, pg_backend_pid: 2684
main: == 20231019180421 AddNameDescriptionToCatalogResources: reverting =============
main: -- remove_column(:catalog_resources, :name)
main:    -> 0.0021s
main: -- remove_column(:catalog_resources, :description)
main:    -> 0.0009s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0782s
main: -- indexes(:catalog_resources)
main:    -> 0.0019s
main: -- current_schema(nil)
main:    -> 0.0001s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0004s
main: -- indexes(:catalog_resources)
main:    -> 0.0011s
main: -- current_schema(nil)
main:    -> 0.0001s
main: == 20231019180421 AddNameDescriptionToCatalogResources: reverted (0.0948s) ====

main: == [advisory_lock_connection] object_id: 179380, pg_backend_pid: 2684
ci: == [advisory_lock_connection] object_id: 179380, pg_backend_pid: 3100
ci: == 20231019180421 AddNameDescriptionToCatalogResources: reverting =============
ci: -- remove_column(:catalog_resources, :name)
ci:    -> 0.0019s
ci: -- remove_column(:catalog_resources, :description)
ci:    -> 0.0010s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.1128s
ci: -- indexes(:catalog_resources)
ci:    -> 0.0023s
ci: -- current_schema(nil)
ci:    -> 0.0002s
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- indexes(:catalog_resources)
ci:    -> 0.0012s
ci: -- current_schema(nil)
ci:    -> 0.0001s
ci: == 20231019180421 AddNameDescriptionToCatalogResources: reverted (0.1375s) ====

ci: == [advisory_lock_connection] object_id: 179380, pg_backend_pid: 3100

main: == [advisory_lock_connection] object_id: 179380, pg_backend_pid: 3656
main: == 20231019223224 BackfillCatalogResourcesNameAndDescription: reverting =======
main: == 20231019223224 BackfillCatalogResourcesNameAndDescription: reverted (0.0037s) 

main: == [advisory_lock_connection] object_id: 179380, pg_backend_pid: 3656

ci: == [advisory_lock_connection] object_id: 179420, pg_backend_pid: 4127
ci: == 20231019223224 BackfillCatalogResourcesNameAndDescription: reverting =======
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: == 20231019223224 BackfillCatalogResourcesNameAndDescription: reverted (0.0082s) 

ci: == [advisory_lock_connection] object_id: 179420, pg_backend_pid: 4127

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 #427928 (closed)