Skip to content

Dump/Load FK data for partitioned tables

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

What does this MR do?

This MR is the third 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 in PG11. This issue implements the process of dumping and loading the foreign key data from the table in the database, so normal Rails operations like db:structure:dump and db:structure:load will work seamlessly with the FK data as it would with builtin PG constraints.

The foreign key data existing in the partitioned_foreign_keys table will be dumped (when migrations are run, or on a db:structure:dump) to a new file similar to structure.sql. When executing a db:structure:load, the data from the file will be reloaded into the table after the standard rails logic executes.

Related issue: #201872 (closed)

MR for part 1: !29064 (merged)

MR for part 2: !29510 (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 MR 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 set the 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()

Limitations

  1. At some point in the future we would likely add support for multi-column foreign keys, since we would want to be able to include the partitioning key in the foreign key.
  2. Currently only two options for ON DELETE are allowed: CASCADE and SET NULL. It was mentioned here: !29525 (comment 325426412) about needing support for other options, which is a valid point. Given that this is already a sizable change of several MR in total, I think it makes sense to try what we have now in a real scenario, and add in those additional options later once we have tested the approach.

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Edited by 🤖 GitLab Bot 🤖

Merge request reports