[Proposal] Add option to skip foreign key validation for postgres

[Tyler Witt]

I did a moderate search in other conversations, and I couldn't find any requests for this yet.

I'm working on porting over strong migrations from ruby, and it has a check to ensure that `NOT VALID` is being set for a new reference on an existing table, because validating foreign keys on creation is a rather expensive lock see the pg docs for Add table_constraint. When trying to implement this check for Ecto migrations, I found that there doesn't seem to be a way to pass validate: false or similar, like active_record does here.

I could recommend users to execute raw sql to do this on their own for foreign keys, but I think Ecto offering it would be much better long term.

I also think that having a way to validate the foreign key in a subsequent migration would be a good win, but that is easy to make raw sql for.

[José Valim]

What is the command used by Postgres to disable validation? IIRC, it is not really part of the column syntax but a separate query, right?

[Tyler Witt]

You would need to add NOT VALID to the end of the foreign key constraint command, and then a follow up migration to run the VALIDATE CONSTRAINT command (which is its own thing entirely).

[José Valim]

Ah, thanks. Given it requires passing an option to the constraint itself, a PR would be welcome. Can you please double check if TDS and MySQL do not provide a similar feature?

When it comes to testing, don't worry about integration tests. Testing the generated SQL in the test/ecto/adapters/*_test.exs will be enough.

--
You received this message because you are subscribed to the Google Groups "elixir-ecto" group.
To unsubscribe from this group and stop receiving emails from it, send an email to elixir-ecto...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/7d035574-c3d2-4aea-9532-7b199bcc2250n%40googlegroups.com.

[Tyler Witt]

Will do!

Should I also add the validate constraint query, or should I leave that alone?

[José Valim]

Leave that one alone, we can mention it in the docs for now.

To view this discussion on the web visit https://groups.google.com/d/msgid/elixir-ecto/f0c5fe01-91b8-47fa-9110-bd69669516can%40googlegroups.com.