Module: Gitlab::Database::PartitioningMigrationHelpers::ForeignKeyHelpers

Includes:
Migrations::LockRetriesHelpers, SchemaHelpers
Included in:
Gitlab::Database::PartitioningMigrationHelpers
Defined in:
lib/gitlab/database/partitioning_migration_helpers/foreign_key_helpers.rb

Constant Summary collapse

ERROR_SCOPE =
'foreign keys'

Instance Method Summary collapse

Methods included from Migrations::LockRetriesHelpers

#with_lock_retries

Methods included from SchemaHelpers

#assert_not_in_transaction_block, #create_comment, #create_trigger, #create_trigger_function, #drop_function, #drop_trigger, #function_exists?, #object_name, #tmp_table_name, #trigger_exists?

Instance Method Details

#add_concurrent_partitioned_foreign_key(source, target, column:, **options) ⇒ Object

Adds a foreign key with only minimal locking on the tables involved.

In concept it works similarly to add_concurrent_foreign_key, but we have to add a special helper for partitioned tables for the following reasons:

  • add_concurrent_foreign_key sets the constraint to ‘NOT VALID` before validating it

  • Setting an FK to NOT VALID is not supported currently in Postgres (up to PG13)

  • Also, PostgreSQL will currently ignore NOT VALID constraints on partitions when adding a valid FK to the partitioned table, so they have to also be validated before we can add the final FK.

Solution:

  • Add the foreign key first to each partition by using add_concurrent_foreign_key and validating it

  • Once all partitions have a foreign key, add it also to the partitioned table (there will be no need for a validation at that level)

For those reasons, this method does not include an option to delay the validation, we have to force validate: true.

source - The source (partitioned) table containing the foreign key. target - The target table the key points to. column - The name of the column to create the foreign key on. on_delete - The action to perform when associated data is removed,

defaults to "CASCADE".

on_update - The action to perform when associated data is updated,

no default value is set.

name - The name of the foreign key. validate - Flag that controls whether the new foreign key will be

validated after creation and if it will be added on the parent table.
If the flag is not set, the constraint will only be enforced for new data
in the existing partitions. The helper will need to be called again
with the flag set to `true` to add the foreign key on the parent table
after validating it on all partitions.
`validate: false` should be paired with `prepare_partitioned_async_foreign_key_validation`

reverse_lock_order - Flag that controls whether we should attempt to acquire locks in the reverse

order of the ALTER TABLE. This can be useful in situations where the foreign
key creation could deadlock with another process.


49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
# File 'lib/gitlab/database/partitioning_migration_helpers/foreign_key_helpers.rb', line 49

def add_concurrent_partitioned_foreign_key(source, target, column:, **options)
  assert_not_in_transaction_block(scope: ERROR_SCOPE)

  options.reverse_merge!({
    target_column: :id,
    on_delete: :cascade,
    on_update: nil,
    name: nil,
    validate: true,
    reverse_lock_order: false,
    column: column
  })

  # We'll use the same FK name for all partitions and match it to
  # the name used for the partitioned table to follow the convention
  # used by PostgreSQL when adding FKs to new partitions
  options[:name] ||= concurrent_partitioned_foreign_key_name(source, column)
  check_options = options.slice(:column, :on_delete, :on_update, :name)
  check_options[:primary_key] = options[:target_column]

  if foreign_key_exists?(source, target, **check_options)
    warning_message = "Foreign key not created because it exists already " \
      "(this may be due to an aborted migration or similar): " \
      "source: #{source}, target: #{target}, column: #{options[:column]}, "\
      "name: #{options[:name]}, on_delete: #{options[:on_delete]}, "\
      "on_update: #{options[:on_update]}"

    Gitlab::AppLogger.warn warning_message

    return
  end

  Gitlab::Database::PostgresPartitionedTable.each_partition(source) do |partition|
    add_concurrent_foreign_key(partition.identifier, target, **options)
  end

  # If we are to add the FK on the parent table now, it will trigger
  # the validation on all partitions. The helper must be called one
  # more time with `validate: true` after the FK is valid on all partitions.
  return unless options[:validate]

  options[:allow_partitioned] = true
  add_concurrent_foreign_key(source, target, **options)
end

#validate_partitioned_foreign_key(source, column, name: nil) ⇒ Object



94
95
96
97
98
99
100
# File 'lib/gitlab/database/partitioning_migration_helpers/foreign_key_helpers.rb', line 94

def validate_partitioned_foreign_key(source, column, name: nil)
  assert_not_in_transaction_block(scope: ERROR_SCOPE)

  Gitlab::Database::PostgresPartitionedTable.each_partition(source) do |partition|
    validate_foreign_key(partition.identifier, column, name: name)
  end
end