Skip to content

Add table to track FKs for partitioned tables

Patrick Bair requested to merge 201872-partitioning-implement-cascading-deletes-without-foreign-keys into master

What does this MR do?

This MR is the first step of implementing a new set of migration helpers, which will handle management of foreign key-like behavior for partitioned tables, which do not support some types of foreign keys natively. This MR implements the migration which will add the new table to track the foreign key information.

Related issue: #201872 (closed)

MR for part 2: !29510 (merged)

MR for part 3: !29525 (merged)

Overview

Postgres 11 does not support foreign keys that reference partitioned tables, so a similar solution needs to be provided by other means. This issue implements part of the features that are given by foreign keys, which is automatic cleanup when a record pointed to be a foreign key is deleted. The most common action taken in that scenario is a cascading delete, but also it's not unusual to nullify the foreign key.

A new set of migration helpers are added, which create a function in the database that performs the cleanup operation, and an AFTER DELETE trigger that will call the function. The foreign key definitions are tracked in a new table, so that when a key is added or removed by a migration, the database has the information to rebuild the cleanup function with the correct operations.

A sample migration might look something like:

class AddIssueToProjectForeignKey < ActiveRecord::Migration[6.0]
  include Gitlab::Database::PartitioningMigrationHelpers

  DOWNTIME = false

  def up
    add_foreign_key :issues, :projects
  end

  def down
    remove_foreign_key :issues, :projects
  end
end

Then, when the migration is run, it will do the following:

  1. Read the current foreign keys that reference projects from the partitioned_foreign_keys table
  2. Verify that the added foreign key is valid and doesn't already exist
  3. Create or replace the database function
  4. Re-create a trigger on projects to call the function

An example function for the above migration would look like:

CREATE OR REPLACE FUNCTION fk_cascade_function_b4db0d065d()
RETURNS TRIGGER AS
$$
BEGIN
DELETE FROM issues WHERE project_id = OLD.id;
RETURN OLD;
END
$$ LANGUAGE PLPGSQL

While the created trigger would look like:

CREATE TRIGGER fk_cascade_trigger_e59525315e
AFTER DELETE ON projects
FOR EACH ROW
EXECUTE PROCEDURE fk_cascade_function_b4db0d065d()

Migration Output

Up
rails db:migrate:up VERSION=20200407182205
== 20200407182205 CreatePartitionedForeignKeys: migrating =====================
-- create_table(:partitioned_foreign_keys)
   -> 0.0045s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE partitioned_foreign_keys\nADD CONSTRAINT check_741676d405\nCHECK ( char_length(from_table) <= 63 )\nNOT VALID;\n")
   -> 0.0010s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- execute("ALTER TABLE partitioned_foreign_keys VALIDATE CONSTRAINT check_741676d405;")
   -> 0.0003s
-- execute("RESET ALL")
   -> 0.0001s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE partitioned_foreign_keys\nADD CONSTRAINT check_2c2e02a62b\nCHECK ( char_length(from_column) <= 63 )\nNOT VALID;\n")
   -> 0.0002s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- execute("ALTER TABLE partitioned_foreign_keys VALIDATE CONSTRAINT check_2c2e02a62b;")
   -> 0.0004s
-- execute("RESET ALL")
   -> 0.0001s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE partitioned_foreign_keys\nADD CONSTRAINT check_40738efb57\nCHECK ( char_length(to_table) <= 63 )\nNOT VALID;\n")
   -> 0.0002s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- execute("ALTER TABLE partitioned_foreign_keys VALIDATE CONSTRAINT check_40738efb57;")
   -> 0.0003s
-- execute("RESET ALL")
   -> 0.0001s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE partitioned_foreign_keys\nADD CONSTRAINT check_7e98be694f\nCHECK ( char_length(to_column) <= 63 )\nNOT VALID;\n")
   -> 0.0002s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- execute("ALTER TABLE partitioned_foreign_keys VALIDATE CONSTRAINT check_7e98be694f;")
   -> 0.0003s
-- execute("RESET ALL")
   -> 0.0001s
-- add_index(:partitioned_foreign_keys, [:to_table, :from_table, :from_column], {:unique=>true, :name=>"index_partitioned_foreign_keys_unique_index"})
   -> 0.0016s
== 20200407182205 CreatePartitionedForeignKeys: migrated (0.0185s) ============
Down
rails db:migrate:down VERSION=20200407182205
== 20200407182205 CreatePartitionedForeignKeys: reverting =====================
-- drop_table(:partitioned_foreign_keys)
   -> 0.0024s
== 20200407182205 CreatePartitionedForeignKeys: reverted (0.0024s) ============

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Edited by 🤖 GitLab Bot 🤖

Merge request reports