Allow add_concurrent_foreign_key to support multiple columns definition
Ref: #374019 (closed)
What does this MR do and why?
This MR modifies add_concurrent_foreign_key
to support creating FK for multiple columns.
This changes is backward compatible so we don't need to update existing migrations.
Why do we need this?
This is required in order to partition a table with the list strategy, the partition key must be include in the foreign keys.
This MR simplifies the process to prepare the foreign keys referencing the table to be partitioned in order to use zero partition helper.
Migration test
In order to make this work for the example below we need to 2 things:
- Connect to your database:
gdk psql -d gitlabhq_development_ci
- Add partition column:
ALTER TABLE ci_unit_test_failures ADD COLUMN partition_id INT DEFAULT 1;
- Add a unique index:
CREATE UNIQUE INDEX idx_tmp ON ci_builds(partition_id, id);
class PrepareForeignKeysPartitioning < Gitlab::Database::Migration[2.0]
disable_ddl_transaction!
SOURCE_TABLE_NAME = :ci_builds
TARGET_TABLE_NAME = :ci_unit_test_failures
COLUMN = :build_id
TARGET_COLUMN = :id
CONSTRAINT_NAME = :fk_0f09856e1f_p
PARTITION_COLUMN = :partition_id
def up
add_concurrent_foreign_key(
SOURCE_TABLE_NAME,
TARGET_TABLE_NAME,
column: [PARTITION_COLUMN, COLUMN],
target_column: [PARTITION_COLUMN, TARGET_COLUMN],
validate: false
name: CONSTRAINT_NAME
)
validate_foreign_key(TARGET_TABLE_NAME, CONSTRAINT_NAME)
end
def down
drop_constraint(TARGET_TABLE_NAME, CONSTRAINT_NAME)
end
end
Database changes
Referenced by:
TABLE "ci_unit_test_failures" CONSTRAINT "fk_0f09856e1f" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CA
SCADE
+ TABLE "ci_unit_test_failures" CONSTRAINT "fk_0f09856e1f_p" FOREIGN KEY (partition_id, build_id) REFERENCES ci_builds(
partition_id, id) ON DELETE CASCADE
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.
Edited by Max Orefice