Add latest changes from gitlab-org/gitlab@16-4-stable-ee

2 files changed, 62 insertions, 0 deletions

diff --git a/db/post_migrate/20230823145126_swap_notes_id_to_bigint_for_self_managed.rb b/db/post_migrate/20230823145126_swap_notes_id_to_bigint_for_self_managed.rb
index ddfaefc452b..988adff7d79 100644
--- a/db/post_migrate/20230823145126_swap_notes_id_to_bigint_for_self_managed.rb
+++ b/db/post_migrate/20230823145126_swap_notes_id_to_bigint_for_self_managed.rb
@@ -60,6 +60,10 @@ class SwapNotesIdToBigintForSelfManaged < Gitlab::Database::Migration[2.1]
     [:timelogs, :fk_timelogs_note_id, :note_id, :nullify]
   ]
 
+  class PgForeignKeys < MigrationRecord
+    self.table_name = :postgres_foreign_keys
+  end
+
   def up
     return if com_or_dev_or_test_but_not_jh?
 
@@ -89,6 +93,8 @@ class SwapNotesIdToBigintForSelfManaged < Gitlab::Database::Migration[2.1]
 
     replace_referencing_foreign_keys
 
+    find_and_drop_faulting_foreign_keys(:system_note_metadata, :fk_d83a918cb1)
+
     with_lock_retries(raise_on_exhaustion: true) do
       # Swap the original and new column names
       execute "ALTER TABLE #{TABLE_NAME} RENAME COLUMN id TO id_tmp"
@@ -180,4 +186,24 @@ class SwapNotesIdToBigintForSelfManaged < Gitlab::Database::Migration[2.1]
       end
     end
   end
+
+  # Customers reported that some FKs are blocking the +notes_pkey+ to be dropped. This is happening, because some
+  # foreign keys listed in +REFERENCING_FOREIGN_KEYS+ have different names in customers instances.
+  # We need to find and remove this FKs to finish the swapping process.
+  #
+  # @info https://gitlab.com/gitlab-org/omnibus-gitlab/-/issues/8227
+  def find_and_drop_faulting_foreign_keys(constrained_table, referenced_fk_name)
+    foreign_keys = PgForeignKeys.select(:name)
+                     .where(constrained_table_name: constrained_table)
+                     .where(referenced_table_name: TABLE_NAME)
+                     .where.not(name: referenced_fk_name)
+
+    with_lock_retries(raise_on_exhaustion: true) do
+      execute "LOCK TABLE #{TABLE_NAME}, #{constrained_table} IN ACCESS EXCLUSIVE MODE"
+
+      foreign_keys.each do |foreign_key|
+        remove_foreign_key_if_exists(constrained_table, name: foreign_key.name)
+      end
+    end
+  end
 end
diff --git a/spec/migrations/20230726144458_swap_notes_id_to_bigint_for_self_managed_spec.rb b/spec/migrations/20230726144458_swap_notes_id_to_bigint_for_self_managed_spec.rb
index b4552cebc58..96151de1148 100644
--- a/spec/migrations/20230726144458_swap_notes_id_to_bigint_for_self_managed_spec.rb
+++ b/spec/migrations/20230726144458_swap_notes_id_to_bigint_for_self_managed_spec.rb
@@ -115,6 +115,42 @@ RSpec.describe SwapNotesIdToBigintForSelfManaged, feature_category: :database do
           end
         end
       end
+
+      context 'when having different naming for fk_d83a918cb1 foreign key' do
+        before do
+          connection.execute('ALTER TABLE system_note_metadata DROP CONSTRAINT IF EXISTS fk_d83a918cb1')
+          connection.execute('ALTER TABLE system_note_metadata DROP CONSTRAINT IF EXISTS fk_rails_d83a918cb1')
+          connection.execute('ALTER TABLE system_note_metadata ADD CONSTRAINT fk_rails_d83a918cb1 FOREIGN KEY (note_id)
+          REFERENCES notes(id) ON DELETE CASCADE')
+        end
+
+        after do
+          connection.execute('ALTER TABLE system_note_metadata DROP CONSTRAINT IF EXISTS fk_rails_d83a918cb1')
+          connection.execute('ALTER TABLE system_note_metadata DROP CONSTRAINT IF EXISTS fk_d83a918cb1_tmp')
+          connection.execute('ALTER TABLE system_note_metadata ADD CONSTRAINT fk_d83a918cb1 FOREIGN KEY (note_id)
+          REFERENCES notes(id) ON DELETE CASCADE')
+        end
+
+        it 'swaps the columns' do
+          disable_migrations_output do
+            reversible_migration do |migration|
+              migration.before -> {
+                notes_table.reset_column_information
+
+                expect(notes_table.columns.find { |c| c.name == 'id' }.sql_type).to eq('integer')
+                expect(notes_table.columns.find { |c| c.name == 'id_convert_to_bigint' }.sql_type).to eq('bigint')
+              }
+
+              migration.after -> {
+                notes_table.reset_column_information
+
+                expect(notes_table.columns.find { |c| c.name == 'id' }.sql_type).to eq('bigint')
+                expect(notes_table.columns.find { |c| c.name == 'id_convert_to_bigint' }.sql_type).to eq('integer')
+              }
+            end
+          end
+        end
+      end
     end
   end
 end