Adds support for deferrable foreign key constraints in PostgreSQL #41487

benedikt · 2021-02-18T13:59:45Z

Summary

By default, foreign key constraints in PostgreSQL are checked after each statement. This works for most use cases, but becomes a major limitation when creating related records before the parent record is inserted into the database.

One example of this is looking up / creating a person via one or more unique alias.

Person.transaction do
  alias = Alias
    .create_with(user_id: SecureRandom.uuid)
    .create_or_find_by(name: "DHH")

  person = Person
    .create_with(name: "David Heinemeier Hansson")
    .create_or_find_by(id: alias.user_id)
end

Using the default behavior, the transaction would fail when executing the first INSERT statement.

This pull request adds support for deferrable foreign key constraints by adding a new option to the add_foreign_key statement in migrations:

add_foreign_key :aliases, :person, deferrable: true

The deferrable: true leaves the default behavior, but allows manually deferring the checks using SET CONSTRAINTS ALL DEFERRED within a transaction. This will cause the foreign keys to be checked after the transaction.

It's also possible to adjust the default behavior from an immediate check (after the statement), to a deferred check (after the transaction).

add_foreign_key :aliases, :person, deferrable: :deferred

Other Information

There is a pull request #17094 from 2014 that implements generic SQL options on add_foreign_key. Pull request #40192 recently implemented the validate aspect of that.

rails-bot · 2021-05-30T08:58:05Z

This pull request has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
Thank you for your contributions.

zzak · 2021-05-31T14:01:38Z

@benedikt I will have to do some more research on this but is this something you're still interested in picking up?

benedikt · 2021-05-31T14:09:30Z

@zzak Yes, absolutely! Let me know how I can help with research.

zzak · 2021-05-31T14:43:56Z

@benedikt First thing I notice is that CI failed, but I'm not entirely sure it is related yet.

Failure:
--
&nbsp; | ActiveRecord::ConnectionAdapters::ConnectionPoolTest#test_checkout_fairness [/rails/activerecord/test/cases/connection_pool_test.rb:395]:
&nbsp; | --- expected
&nbsp; | +++ actual
&nbsp; | @@ -1 +1 @@
&nbsp; | -[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
&nbsp; | +[1, 2, 3, 4, 5, 6, 10, 7, 8, 9]

Will have to take a closer look at this, thank you for your patience 🙇

benedikt · 2021-05-31T14:46:15Z

@zzak Just merged the current main into this branch to resolve conflicts which caused the CI to fail. I don't think this failure is related, but I can look into it more.

zzak · 2021-05-31T14:52:14Z

@benedikt Thanks! I will check back on this issue shortly but feel free to ping me if you have any new ideas 🙇

benedikt · 2021-05-31T15:54:17Z

@zzak Tried to reproduce this locally but didn't see the failure there.

After rebasing, the CI also didn't complain didn't complain about it again.

As the failure was in the Sqlite3 tests and this pull request only touches PostgreSQL, I think it's save to assume it was an unrelated failure.

There now seems to be a failure related to memcache timeout, but I guess that's also not related to the changes of this pull request.

benedikt · 2021-06-08T08:50:56Z

@zzak It's a miracle, but all tests are passing. Even the unrelated ones. 😄

activerecord/CHANGELOG.md

activerecord/test/cases/migration/foreign_key_test.rb

benedikt · 2021-06-08T15:45:37Z

Thanks for your review, @ghiculescu. As mentioned in the individual comments, I adjusted the pull request accordingly.

Unfortunately, the build once again isn't green because of an HTTP timeout in an unrelated part.

ghiculescu

This looks great to me, I will wait for someone from the core team to review. Thanks for your work on this!

zzak · 2021-06-09T10:12:19Z

@benedikt You will have to squash the commits but maybe better to wait until another committer reviews this 🙏

benedikt · 2021-06-09T10:16:40Z

@zzak Yup, it's already just one commit.

rails-bot · 2021-09-16T08:01:19Z

This pull request has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
Thank you for your contributions.

benedikt · 2021-09-16T08:06:55Z

@zzak @ghiculescu Anything I can do to move this forward? 😅

ghiculescu · 2021-09-16T14:34:40Z

There’s a merge conflict, other than that it’s waiting on the core team to review.

benedikt · 2021-09-16T14:39:19Z

@ghiculescu Yeah, I keep fixing that particular merge conflict for the last couple of months. How do we get the core team to review this? 😄

By default, foreign key constraints in PostgreSQL are checked after each statement. This works for most use cases, but becomes a major limitation when creating related records before the parent record is inserted into the database. One example of this is looking up / creating a person via one or more unique alias. ```ruby Person.transaction do alias = Alias .create_with(user_id: SecureRandom.uuid) .create_or_find_by(name: "DHH") person = Person .create_with(name: "David Heinemeier Hansson") .create_or_find_by(id: alias.user_id) end ``` Using the default behavior, the transaction would fail when executing the first `INSERT` statement. This pull request adds support for deferrable foreign key constraints by adding a new option to the `add_foreign_key` statement in migrations: ```ruby add_foreign_key :aliases, :person, deferrable: true ``` The `deferrable: true` leaves the default behavior, but allows manually deferring the checks using `SET CONSTRAINTS ALL DEFERRED` within a transaction. This will cause the foreign keys to be checked after the transaction. It's also possible to adjust the default behavior from an immediate check (after the statement), to a deferred check (after the transaction). ```ruby add_foreign_key :aliases, :person, deferrable: :deferred ```

ghiculescu · 2021-09-16T15:44:17Z

Patience is the name of the game :) There's lots of open PRs and they're all pretty busy with Rails + real jobs. They do eventually get to most PRs but you just have to wait it out. The best advice I've heard is "make the PR easy to merge", which I think you have done a good job of here.

benedikt · 2021-09-23T07:21:50Z

@rafaelfranca Thank you for merging this 👍

rails-bot bot added the activerecord label Feb 18, 2021

rails-bot bot added the stale label May 30, 2021

rails-bot bot removed the stale label May 31, 2021

ghiculescu reviewed Jun 8, 2021

View reviewed changes

activerecord/CHANGELOG.md Outdated Show resolved Hide resolved

ghiculescu reviewed Jun 8, 2021

View reviewed changes

activerecord/test/cases/migration/foreign_key_test.rb Show resolved Hide resolved

benedikt requested a review from ghiculescu June 8, 2021 15:45

ghiculescu approved these changes Jun 8, 2021

View reviewed changes

zzak approved these changes Jun 9, 2021

View reviewed changes

rails-bot bot added stale and removed stale labels Sep 16, 2021

ghiculescu added the ready PRs ready to merge label Sep 16, 2021

rafaelfranca mentioned this pull request Sep 22, 2021

support for db-specific options to add_foreign_key #17094

Closed

rafaelfranca merged commit 6d42731 into rails:main Sep 22, 2021

jrochkind mentioned this pull request Sep 27, 2021

Implement returning for postgresql #39968

Closed

pjmartorell mentioned this pull request Apr 14, 2022

Random failures when restoring a snapshot with children with foreign keys westonganger/active_snapshot#22

Closed

alpaca-tc mentioned this pull request Oct 4, 2022

Add support for unique constraints (PostgreSQL-only). #46192

Merged

9 tasks

alpaca-tc mentioned this pull request Mar 14, 2023

deferrable foreign key can be passed to references #47671

Merged

4 tasks

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Adds support for deferrable foreign key constraints in PostgreSQL #41487

Adds support for deferrable foreign key constraints in PostgreSQL #41487

benedikt commented Feb 18, 2021 •

edited

rails-bot bot commented May 30, 2021

zzak commented May 31, 2021

benedikt commented May 31, 2021

zzak commented May 31, 2021

benedikt commented May 31, 2021

zzak commented May 31, 2021

benedikt commented May 31, 2021 •

edited

benedikt commented Jun 8, 2021

benedikt commented Jun 8, 2021

ghiculescu left a comment

zzak commented Jun 9, 2021

benedikt commented Jun 9, 2021

rails-bot bot commented Sep 16, 2021

benedikt commented Sep 16, 2021

ghiculescu commented Sep 16, 2021

benedikt commented Sep 16, 2021

ghiculescu commented Sep 16, 2021

benedikt commented Sep 23, 2021

Adds support for deferrable foreign key constraints in PostgreSQL #41487

Adds support for deferrable foreign key constraints in PostgreSQL #41487

Conversation

benedikt commented Feb 18, 2021 • edited

Summary

Other Information

rails-bot bot commented May 30, 2021

zzak commented May 31, 2021

benedikt commented May 31, 2021

zzak commented May 31, 2021

benedikt commented May 31, 2021

zzak commented May 31, 2021

benedikt commented May 31, 2021 • edited

benedikt commented Jun 8, 2021

benedikt commented Jun 8, 2021

ghiculescu left a comment

Choose a reason for hiding this comment

zzak commented Jun 9, 2021

benedikt commented Jun 9, 2021

rails-bot bot commented Sep 16, 2021

benedikt commented Sep 16, 2021

ghiculescu commented Sep 16, 2021

benedikt commented Sep 16, 2021

ghiculescu commented Sep 16, 2021

benedikt commented Sep 23, 2021

benedikt commented Feb 18, 2021 •

edited

benedikt commented May 31, 2021 •

edited