From ceff8b8635e5341d83c61b813ecffaddb08ec211 Mon Sep 17 00:00:00 2001 From: Andy Soiron Date: Thu, 19 Nov 2020 17:52:31 +0100 Subject: [PATCH 1/4] Cascade delete inheriting services --- ...ge_services_inherit_from_id_foreign_key.rb | 19 +++++++++++++++++++ db/schema_migrations/20201119162801 | 1 + db/structure.sql | 2 +- 3 files changed, 21 insertions(+), 1 deletion(-) create mode 100644 db/migrate/20201119162801_change_services_inherit_from_id_foreign_key.rb create mode 100644 db/schema_migrations/20201119162801 diff --git a/db/migrate/20201119162801_change_services_inherit_from_id_foreign_key.rb b/db/migrate/20201119162801_change_services_inherit_from_id_foreign_key.rb new file mode 100644 index 00000000000000..f674d8d9e35529 --- /dev/null +++ b/db/migrate/20201119162801_change_services_inherit_from_id_foreign_key.rb @@ -0,0 +1,19 @@ +# frozen_string_literal: true + +class ChangeServicesInheritFromIdForeignKey < ActiveRecord::Migration[6.0] + include Gitlab::Database::MigrationHelpers + + DOWNTIME = false + + disable_ddl_transaction! + + def up + remove_foreign_key_if_exists :services, column: :inherit_from_id + add_concurrent_foreign_key :services, :services, column: :inherit_from_id, on_delete: :cascade + end + + def down + remove_foreign_key_if_exists :services, column: :inherit_from_id + add_concurrent_foreign_key :services, :services, column: :inherit_from_id, on_delete: :nullify + end +end diff --git a/db/schema_migrations/20201119162801 b/db/schema_migrations/20201119162801 new file mode 100644 index 00000000000000..b6aa67ad5f660f --- /dev/null +++ b/db/schema_migrations/20201119162801 @@ -0,0 +1 @@ +c41f4649540c23d25f0ae26c3754476409b5e77f53b96db65f2c1fd4a6caf087 \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 54888195975e4f..cbf2e0d8d627d6 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -23125,7 +23125,7 @@ ALTER TABLE ONLY ci_pipelines ADD CONSTRAINT fk_86635dbd80 FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; ALTER TABLE ONLY services - ADD CONSTRAINT fk_868a8e7ad6 FOREIGN KEY (inherit_from_id) REFERENCES services(id) ON DELETE SET NULL; + ADD CONSTRAINT fk_868a8e7ad6 FOREIGN KEY (inherit_from_id) REFERENCES services(id) ON DELETE CASCADE; ALTER TABLE ONLY geo_event_log ADD CONSTRAINT fk_86c84214ec FOREIGN KEY (repository_renamed_event_id) REFERENCES geo_repository_renamed_events(id) ON DELETE CASCADE; -- GitLab From 644fd7eb61d9898bd4c3cd29377148b1694d6867 Mon Sep 17 00:00:00 2001 From: Andy Soiron Date: Thu, 19 Nov 2020 20:40:33 +0100 Subject: [PATCH 2/4] Add changelog entry --- .../unreleased/283938_cascade_delete_inheriting_services.yml | 5 +++++ 1 file changed, 5 insertions(+) create mode 100644 changelogs/unreleased/283938_cascade_delete_inheriting_services.yml diff --git a/changelogs/unreleased/283938_cascade_delete_inheriting_services.yml b/changelogs/unreleased/283938_cascade_delete_inheriting_services.yml new file mode 100644 index 00000000000000..25989863327386 --- /dev/null +++ b/changelogs/unreleased/283938_cascade_delete_inheriting_services.yml @@ -0,0 +1,5 @@ +--- +title: Change services.inherit_from_id foreign key to ON DELETE CASCADE +merge_request: 48163 +author: +type: fixed -- GitLab From 98a552095121f50fb95f789064ea0e171340bb4f Mon Sep 17 00:00:00 2001 From: Andy Soiron Date: Fri, 20 Nov 2020 20:24:14 +0100 Subject: [PATCH 3/4] Remove and add foreign key in transaction --- ..._change_services_inherit_from_id_foreign_key.rb | 14 ++++++++------ 1 file changed, 8 insertions(+), 6 deletions(-) diff --git a/db/migrate/20201119162801_change_services_inherit_from_id_foreign_key.rb b/db/migrate/20201119162801_change_services_inherit_from_id_foreign_key.rb index f674d8d9e35529..1c83f07c6cc794 100644 --- a/db/migrate/20201119162801_change_services_inherit_from_id_foreign_key.rb +++ b/db/migrate/20201119162801_change_services_inherit_from_id_foreign_key.rb @@ -5,15 +5,17 @@ class ChangeServicesInheritFromIdForeignKey < ActiveRecord::Migration[6.0] DOWNTIME = false - disable_ddl_transaction! - def up - remove_foreign_key_if_exists :services, column: :inherit_from_id - add_concurrent_foreign_key :services, :services, column: :inherit_from_id, on_delete: :cascade + with_lock_retries do + remove_foreign_key_if_exists :services, column: :inherit_from_id + add_foreign_key :services, :services, column: :inherit_from_id, on_delete: :cascade + end end def down - remove_foreign_key_if_exists :services, column: :inherit_from_id - add_concurrent_foreign_key :services, :services, column: :inherit_from_id, on_delete: :nullify + with_lock_retries do + remove_foreign_key_if_exists :services, column: :inherit_from_id + add_foreign_key :services, :services, column: :inherit_from_id, on_delete: :nullify + end end end -- GitLab From ca4371734128623baf2990e673805e92b33dc3a4 Mon Sep 17 00:00:00 2001 From: Andy Soiron Date: Thu, 26 Nov 2020 13:13:52 +0100 Subject: [PATCH 4/4] Use explicit names for FK to add new before remove --- ..._change_services_inherit_from_id_foreign_key.rb | 14 ++++++-------- db/structure.sql | 6 +++--- 2 files changed, 9 insertions(+), 11 deletions(-) diff --git a/db/migrate/20201119162801_change_services_inherit_from_id_foreign_key.rb b/db/migrate/20201119162801_change_services_inherit_from_id_foreign_key.rb index 1c83f07c6cc794..a7f12fcf726807 100644 --- a/db/migrate/20201119162801_change_services_inherit_from_id_foreign_key.rb +++ b/db/migrate/20201119162801_change_services_inherit_from_id_foreign_key.rb @@ -5,17 +5,15 @@ class ChangeServicesInheritFromIdForeignKey < ActiveRecord::Migration[6.0] DOWNTIME = false + disable_ddl_transaction! + def up - with_lock_retries do - remove_foreign_key_if_exists :services, column: :inherit_from_id - add_foreign_key :services, :services, column: :inherit_from_id, on_delete: :cascade - end + add_concurrent_foreign_key :services, :services, column: :inherit_from_id, on_delete: :cascade, name: 'fk_services_inherit_from_id' + remove_foreign_key_if_exists :services, name: 'fk_868a8e7ad6' end def down - with_lock_retries do - remove_foreign_key_if_exists :services, column: :inherit_from_id - add_foreign_key :services, :services, column: :inherit_from_id, on_delete: :nullify - end + remove_foreign_key_if_exists :services, name: 'fk_services_inherit_from_id' + add_concurrent_foreign_key :services, :services, column: :inherit_from_id, on_delete: :nullify, name: 'fk_868a8e7ad6' end end diff --git a/db/structure.sql b/db/structure.sql index cbf2e0d8d627d6..d9e7b2ccc15640 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -23124,9 +23124,6 @@ ALTER TABLE ONLY merge_request_diffs ALTER TABLE ONLY ci_pipelines ADD CONSTRAINT fk_86635dbd80 FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; -ALTER TABLE ONLY services - ADD CONSTRAINT fk_868a8e7ad6 FOREIGN KEY (inherit_from_id) REFERENCES services(id) ON DELETE CASCADE; - ALTER TABLE ONLY geo_event_log ADD CONSTRAINT fk_86c84214ec FOREIGN KEY (repository_renamed_event_id) REFERENCES geo_repository_renamed_events(id) ON DELETE CASCADE; @@ -24792,6 +24789,9 @@ ALTER TABLE ONLY resource_label_events ALTER TABLE ONLY ci_builds_metadata ADD CONSTRAINT fk_rails_ffcf702a02 FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; +ALTER TABLE ONLY services + ADD CONSTRAINT fk_services_inherit_from_id FOREIGN KEY (inherit_from_id) REFERENCES services(id) ON DELETE CASCADE; + ALTER TABLE ONLY timelogs ADD CONSTRAINT fk_timelogs_issues_issue_id FOREIGN KEY (issue_id) REFERENCES issues(id) ON DELETE CASCADE; -- GitLab