Random failures when restoring a snapshot with children with foreign keys

[pjmartorell]

I'm experiencing random failures when restoring a snapshot with children with foreign keys, which raises an ActiveRecord::InvalidForeignKey exception when a child is restored/created in the database before the parent record has committed the transaction (that's my guess). At the beginning I thought it was related to the SnapshotItem.import, that was creating children in a random order, but after replacing this line by a simple save!s I noticed the issue was still persisting. My guess is that the ActiveRecord::Base.transaction that wraps the restore! method is causing this issue erratically. I don't have any simple example to provide at this moment. I think one possible way to prevent this issue would be to apply deferrable foreign keys, which is a feature available in Rails 7 and PostgreSQL.

[westonganger]

When using Rails with Postgres I have found that I prefer to NOT to use database level foreign key constraints. It usually much more convenient to simply use Rails validations to handle this stuff. For example MySQL doesnt use foreign keys at all. If you do a google search about this you can get some more well written opinions on this matter.

That being said I think it would be nice to be able to support db level foreign key constraints. We can look at the ordering of the restores, however I am not sure that this will even be possible given all the different relationships that can be within a record.

[pjmartorell]

I don't know much about MySQL but at first glance I see it actually supports foreign keys: https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html. Validations are not 100% reliable (specially when you have multiple threads running concurrently) since they are run at application level instead of at database level and they cannot ensure database integrity. Maybe a first improvement could be to restore children in the same order of has_snapshot_children hash, but as you said it can be tricky in some cases. It would be less performant than the current implementation which uses SnapshotItem.import, so probably it should be an optional feature. In the case of PostgreSQL + Rails7 it would be possible to use deferrable foreign keys, I haven't tested it, though.

[westonganger]

I don't envision myself creating a solution to deal with foreign keys within this gem

As this gem is meant to be easily extracted and customized, anyone can feel free to implement the necessary logic for this in their app.

If anyone does implement this it would be much appreciated if they could post a snippet of their logic here for documentation purposes.

Closing.