Backfill catalog_resources.visibility_level with data from projects
What does this MR do and why?
Previously in !136111 (merged), we denormalized catalog_resources.visibility_level
with a lightweight syncing process that relied on model callbacks. However, since visibility_level
is a critical column that pertains to user permissions, we decided to implement a more robust background syncing process involving a DB trigger, queue table, and worker in !137238 (merged) and !138865 (merged).
Now that the background syncing process is in place, we need to backfill the visibility_level
data for existing records in catalog_resources
before we start utilizing this column in our Ci::Catalog::Listing queries.
Resolves Step 3 of #429056 (closed).
Query Plan
Up
main: == [advisory_lock_connection] object_id: 182500, pg_backend_pid: 75733
main: == 20231207194620 BackfillCatalogResourcesVisibilityLevel: migrating ==========
main: -- execute(" UPDATE catalog_resources\n SET visibility_level = projects.visibility_level\n FROM projects\n WHERE catalog_resources.project_id = projects.id\n")
main: -> 0.0037s
main: == 20231207194620 BackfillCatalogResourcesVisibilityLevel: migrated (0.0078s) =
main: == [advisory_lock_connection] object_id: 182500, pg_backend_pid: 75733
ci: == [advisory_lock_connection] object_id: 182700, pg_backend_pid: 75735
ci: == 20231207194620 BackfillCatalogResourcesVisibilityLevel: 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: == 20231207194620 BackfillCatalogResourcesVisibilityLevel: migrated (0.0305s) =
ci: == [advisory_lock_connection] object_id: 182700, pg_backend_pid: 75735
Down
main: == [advisory_lock_connection] object_id: 181820, pg_backend_pid: 78584
main: == 20231207194620 BackfillCatalogResourcesVisibilityLevel: reverting ==========
main: == 20231207194620 BackfillCatalogResourcesVisibilityLevel: reverted (0.0035s) =
main: == [advisory_lock_connection] object_id: 181820, pg_backend_pid: 78584
ci: == [advisory_lock_connection] object_id: 181660, pg_backend_pid: 79016
ci: == 20231207194620 BackfillCatalogResourcesVisibilityLevel: 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: == 20231207194620 BackfillCatalogResourcesVisibilityLevel: reverted (0.0094s) =
ci: == [advisory_lock_connection] object_id: 181660, pg_backend_pid: 79016
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 #429056 (closed)