Convert ml_models.project_id constraint to cascade delete
What does this MR do and why?
Convert ml_models.project_id constraint to cascade delete
- Low traffic table so no need for reverse_lock_order
- Related to #497746 (closed)
- Docs https://docs.gitlab.com/ee/development/database/foreign_keys.html#updating-foreign-keys-in-migrations
- Docs 2 https://docs.gitlab.com/ee/development/database/add_foreign_key_to_existing_column.html
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.
How to set up and validate locally
Migration output
bin/rails db:migrate:up:main VERSION=20241007071632 RAILS_ENV=development
bin/rails db:migrate:up:main VERSION=20241007071637 RAILS_ENV=development
bin/rails db:migrate:up:main VERSION=20241007071637 RAILS_ENV=development
main: == [advisory_lock_connection] object_id: 128780, pg_backend_pid: 204862
main: == 20241007071632 AddMlModelsProjectIdForeignKeyAsCascadeDelete: migrating ====
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- execute("ALTER TABLE ml_models ADD CONSTRAINT fk_51e87f7c50_new FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE NOT VALID;")
main: -> 0.0173s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0004s
main: -- execute("ALTER TABLE ml_models VALIDATE CONSTRAINT fk_51e87f7c50_new;")
main: -> 0.0018s
main: -- execute("RESET statement_timeout")
main: -> 0.0005s
main: == 20241007071632 AddMlModelsProjectIdForeignKeyAsCascadeDelete: migrated (0.0478s)
main: == [advisory_lock_connection] object_id: 128780, pg_backend_pid: 204862
main: == [advisory_lock_connection] object_id: 128820, pg_backend_pid: 205323
main: == 20241007071637 RemoveMlModelsProjectIdPlainForeignKey: migrating ===========
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- execute("LOCK TABLE projects, ml_models IN ACCESS EXCLUSIVE MODE")
main: -> 0.0008s
main: -- remove_foreign_key(:ml_models, :projects, {:name=>"fk_rails_51e87f7c50"})
main: -> 0.0036s
main: == 20241007071637 RemoveMlModelsProjectIdPlainForeignKey: migrated (0.0354s) ==
main: == [advisory_lock_connection] object_id: 128820, pg_backend_pid: 205323
Rollback output
bin/rails db:migrate:down:main VERSION=20241007071637 RAILS_ENV=development
bin/rails db:migrate:down:main VERSION=20241007071632 RAILS_ENV=development
main: == [advisory_lock_connection] object_id: 128820, pg_backend_pid: 232864
main: == 20241007071637 RemoveMlModelsProjectIdPlainForeignKey: reverting ===========
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- execute("ALTER TABLE ml_models ADD CONSTRAINT fk_rails_51e87f7c50 FOREIGN KEY (project_id) REFERENCES projects (id) NOT VALID;")
main: -> 0.0014s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0004s
main: -- execute("ALTER TABLE ml_models VALIDATE CONSTRAINT fk_rails_51e87f7c50;")
main: -> 0.0018s
main: -- execute("RESET statement_timeout")
main: -> 0.0004s
main: == 20241007071637 RemoveMlModelsProjectIdPlainForeignKey: reverted (0.0430s) ==
main: == 20241007071632 AddMlModelsProjectIdForeignKeyAsCascadeDelete: reverting ====
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- remove_foreign_key(:ml_models, :projects, {:name=>"fk_51e87f7c50_new"})
main: -> 0.0038s
main: == 20241007071632 AddMlModelsProjectIdForeignKeyAsCascadeDelete: reverted (0.0376s)
main: == [advisory_lock_connection] object_id: 128820, pg_backend_pid: 204098
Edited by Alper Akgun