From 3f4b140f41baf8b11a804c7a3d3aad6b5b6e3685 Mon Sep 17 00:00:00 2001
From: Michael Becker <11881043-wandering_person@users.noreply.gitlab.com>
Date: Tue, 19 Dec 2023 20:36:21 +0700
Subject: [PATCH] Drop `epic_id` Column From Vulnerabilities

`epic_id` is an ignored column ([source][0])

This commit drops the column for step 2 of the
[3-MR drop column process][1]

Using the command `\d+ vulnerabilities` on [postgres.ai][2]
indicates **there ARE indexes and constraints**

As such, this migration includes adding back the index and FK
constraint in the `down` method

Related to https://gitlab.com/gitlab-org/gitlab/-/issues/268154

[0]:https://gitlab.com/gitlab-org/gitlab/-/blob/0ef7ab245a0952c68232b3a3418b673b912b5115/app/models/vulnerability.rb#L8-11
[1]:https://docs.gitlab.com/ee/development/database/avoiding_downtime_in_migrations.html#the-removed-column-has-an-index-or-constraint-that-belongs-to-it
[2]:https://console.postgres.ai

Changelog: removed
---
 ...ove_epic_id_column_from_vulnerabilities.rb | 21 +++++++++++++++++++
 db/schema_migrations/20231219132423           |  1 +
 db/structure.sql                              |  6 ------
 ee/spec/models/ee/vulnerability_spec.rb       |  2 +-
 4 files changed, 23 insertions(+), 7 deletions(-)
 create mode 100644 db/post_migrate/20231219132423_remove_epic_id_column_from_vulnerabilities.rb
 create mode 100644 db/schema_migrations/20231219132423

diff --git a/db/post_migrate/20231219132423_remove_epic_id_column_from_vulnerabilities.rb b/db/post_migrate/20231219132423_remove_epic_id_column_from_vulnerabilities.rb
new file mode 100644
index 00000000000000..fa6379a409a3ad
--- /dev/null
+++ b/db/post_migrate/20231219132423_remove_epic_id_column_from_vulnerabilities.rb
@@ -0,0 +1,21 @@
+# frozen_string_literal: true
+
+class RemoveEpicIdColumnFromVulnerabilities < Gitlab::Database::Migration[2.2]
+  disable_ddl_transaction!
+
+  milestone '16.8'
+
+  def up
+    with_lock_retries do
+      remove_column :vulnerabilities, :epic_id
+    end
+  end
+
+  def down
+    add_column :vulnerabilities, :epic_id, :bigint unless column_exists?(:vulnerabilities, :epic_id)
+
+    # Add back index and constraint that were dropped in `up`
+    add_concurrent_index(:vulnerabilities, :epic_id)
+    add_concurrent_foreign_key(:vulnerabilities, :epics, column: :epic_id, on_delete: :nullify)
+  end
+end
diff --git a/db/schema_migrations/20231219132423 b/db/schema_migrations/20231219132423
new file mode 100644
index 00000000000000..82fb1923c917ac
--- /dev/null
+++ b/db/schema_migrations/20231219132423
@@ -0,0 +1 @@
+ed2b44c085d02dfb5e361f3f33dd62b9b5fed0e3ae570ff79936feadad66561a
\ No newline at end of file
diff --git a/db/structure.sql b/db/structure.sql
index 37f769f427fcf0..98b874b69513dd 100644
--- a/db/structure.sql
+++ b/db/structure.sql
@@ -25200,7 +25200,6 @@ ALTER SEQUENCE vs_code_settings_id_seq OWNED BY vs_code_settings.id;
 
 CREATE TABLE vulnerabilities (
     id bigint NOT NULL,
-    epic_id bigint,
     project_id bigint NOT NULL,
     author_id bigint NOT NULL,
     created_at timestamp with time zone NOT NULL,
@@ -35682,8 +35681,6 @@ CREATE INDEX index_vulnerabilities_on_detected_at_and_id ON vulnerabilities USIN
 
 CREATE INDEX index_vulnerabilities_on_dismissed_by_id ON vulnerabilities USING btree (dismissed_by_id);
 
-CREATE INDEX index_vulnerabilities_on_epic_id ON vulnerabilities USING btree (epic_id);
-
 CREATE INDEX index_vulnerabilities_on_finding_id ON vulnerabilities USING btree (finding_id);
 
 CREATE INDEX index_vulnerabilities_on_project_id_and_id ON vulnerabilities USING btree (project_id, id);
@@ -38095,9 +38092,6 @@ ALTER TABLE ONLY project_statistics
 ALTER TABLE ONLY agent_project_authorizations
     ADD CONSTRAINT fk_1d30bb4987 FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE;
 
-ALTER TABLE ONLY vulnerabilities
-    ADD CONSTRAINT fk_1d37cddf91 FOREIGN KEY (epic_id) REFERENCES epics(id) ON DELETE SET NULL;
-
 ALTER TABLE ONLY boards
     ADD CONSTRAINT fk_1e9a074a35 FOREIGN KEY (group_id) REFERENCES namespaces(id) ON DELETE CASCADE;
 
diff --git a/ee/spec/models/ee/vulnerability_spec.rb b/ee/spec/models/ee/vulnerability_spec.rb
index d49fba31689548..2a3fc59a13f841 100644
--- a/ee/spec/models/ee/vulnerability_spec.rb
+++ b/ee/spec/models/ee/vulnerability_spec.rb
@@ -29,7 +29,7 @@
   let_it_be(:vulnerability) { create(:vulnerability, :sast, :confirmed, :low, :with_state_transition, project: project) }
   let_it_be(:finding) { create(:vulnerabilities_finding, vulnerability: vulnerability) }
 
-  it { is_expected.to have_locked_schema('a23fdde3fd14fefb7b51dd9bb5cdb0da62e0250d94c60f6b4e6576b223a8dffe').reference('https://gitlab.com/gitlab-org/gitlab/-/issues/349315') }
+  it { is_expected.to have_locked_schema('25e469a4d319ca5f4d0fd1511150028ba05509e55c8592922f9ce8f0c0e4df6f').reference('https://gitlab.com/gitlab-org/gitlab/-/issues/349315') }
 
   it_behaves_like 'vulnerability and finding shared examples' do
     let(:transformer_method) { :itself }
-- 
GitLab