[Idea] Warn on the addition of a validated foreign key

[iamvery]

I recently ran into a problem adding a new foreign key constraint at scale. It seems that by default, when adding a foreign key, the entire table is validated for the new constraint which performs some table locking.

From the PostgreSQL docs

If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped.

In my particular case, I was adding the foreign key to a new column which would be NULL for all existing records anyway thus not needing the validation at all. Here's the full example that caused me problems:

add_column :foos, :bar_id, :integer
add_foreign_key :foos, :bars

And here is the preferred way:

add_column :foos, :bar_id, :integer
add_foreign_key :foos, :bars, validate: false

Note: validate: false which results in NOT VALID option being added to the produced ALTER TABLE statement.

What do you think?

[ankane]

Hey @iamvery, thanks for the suggestion. I think this is best as a custom check. I imagine most of the time foreign keys need to be valid.

[iamvery]

Thanks for the response @ankane! I'm a little indifferent on this, but I understand your point. I'm indifferent, because I feel like if the purpose of this library is to protect users from themselves it might go so far to recommend against typical cases like this in the name of safety.

Strong Migrations detects potentially dangerous operations in migrations, prevents them from running by default, and provides instructions on safer ways to do what you want.

Either way, the custom checks are a great feature that we can take advantage of. Thank you!

[ankane]

Reading about the issue more, agree that it'd be good for the gem to handle it, so happy to accept a PR. Since the validate option was introduced in Rails 5.2, the instructions will probably need to use execute in previous versions. There will need to be instructions for validating as well.

References:

• https://travisofthenorth.com/blog/2017/2/2/postgres-adding-foreign-keys-with-zero-downtime
• https://github.com/rails/rails/pull/27756/files

[iamvery]

Thanks for the follow up here! It's going to be a few days before I can dig in, but this sounds great 👍

[ankane]

Going to close this out, but feel free to create a PR