{"action":"created","comment":{"url":"https://api.github.com/repos/rails/rails/pulls/comments/103120627","pull_request_review_id":23897010,"id":103120627,"diff_hunk":"@@ -601,6 +601,7 @@ def foreign_keys(table_name)\n \n             options[:on_delete] = extract_foreign_key_action(row[\"on_delete\"])\n             options[:on_update] = extract_foreign_key_action(row[\"on_update\"])\n+            options[:valid] = row[\"valid\"]","path":"activerecord/lib/active_record/connection_adapters/postgresql/schema_statements.rb","position":13,"original_position":13,"commit_id":"0c517f242fa4daacf0296e05ebe19023b9e2163b","original_commit_id":"60976c9057266b06f9fc3cd95140e580f4fbb468","user":{"login":"travisofthenorth","id":1894627,"avatar_url":"https://avatars.githubusercontent.com/u/1894627?v=3","gravatar_id":"","url":"https://api.github.com/users/travisofthenorth","html_url":"https://github.com/travisofthenorth","followers_url":"https://api.github.com/users/travisofthenorth/followers","following_url":"https://api.github.com/users/travisofthenorth/following{/other_user}","gists_url":"https://api.github.com/users/travisofthenorth/gists{/gist_id}","starred_url":"https://api.github.com/users/travisofthenorth/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/travisofthenorth/subscriptions","organizations_url":"https://api.github.com/users/travisofthenorth/orgs","repos_url":"https://api.github.com/users/travisofthenorth/repos","events_url":"https://api.github.com/users/travisofthenorth/events{/privacy}","received_events_url":"https://api.github.com/users/travisofthenorth/received_events","type":"User","site_admin":false},"body":"That doesn't make much sense to me. At this point, the foreign key has already been created. I'd expect a `valid?` or `validated?` method to describe the status of an existing foreign key and `options[:validate]` to be used only during creation.\r\n\r\nEither way, I'll make the change in a subsequent commit.","created_at":"2017-02-26T23:19:56Z","updated_at":"2017-02-26T23:19:56Z","html_url":"https://github.com/rails/rails/pull/27756#discussion_r103120627","pull_request_url":"https://api.github.com/repos/rails/rails/pulls/27756","_links":{"self":{"href":"https://api.github.com/repos/rails/rails/pulls/comments/103120627"},"html":{"href":"https://github.com/rails/rails/pull/27756#discussion_r103120627"},"pull_request":{"href":"https://api.github.com/repos/rails/rails/pulls/27756"}}},"pull_request":{"url":"https://api.github.com/repos/rails/rails/pulls/27756","id":102553767,"html_url":"https://github.com/rails/rails/pull/27756","diff_url":"https://github.com/rails/rails/pull/27756.diff","patch_url":"https://github.com/rails/rails/pull/27756.patch","issue_url":"https://api.github.com/repos/rails/rails/issues/27756","number":27756,"state":"open","locked":false,"title":"Add ability to create/validate invalid foreign keys in Postgres","user":{"login":"travisofthenorth","id":1894627,"avatar_url":"https://avatars.githubusercontent.com/u/1894627?v=3","gravatar_id":"","url":"https://api.github.com/users/travisofthenorth","html_url":"https://github.com/travisofthenorth","followers_url":"https://api.github.com/users/travisofthenorth/followers","following_url":"https://api.github.com/users/travisofthenorth/following{/other_user}","gists_url":"https://api.github.com/users/travisofthenorth/gists{/gist_id}","starred_url":"https://api.github.com/users/travisofthenorth/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/travisofthenorth/subscriptions","organizations_url":"https://api.github.com/users/travisofthenorth/orgs","repos_url":"https://api.github.com/users/travisofthenorth/repos","events_url":"https://api.github.com/users/travisofthenorth/events{/privacy}","received_events_url":"https://api.github.com/users/travisofthenorth/received_events","type":"User","site_admin":false},"body":"### Summary\r\n\r\nIn Postgres, adding foreign keys can cause significant downtime because the transaction needs to acquire some very heavy locks on the table being altered as well as the table being referenced. To illustrate, if I wanted to add a foreign key on my `addresses` table referencing my `users` table:\r\n```\r\nmyapp=# BEGIN;\r\nBEGIN\r\nmyapp=# ALTER TABLE addresses ADD CONSTRAINT \"fk_rails_48c9e0c5a2\" FOREIGN KEY (\"user_id\") REFERENCES \"users\" (\"id\");\r\nALTER TABLE\r\nmyapp=# SELECT locktype, relation::regclass, mode, transactionid AS tid, virtualtransaction AS vtid, pid, granted FROM pg_locks;\r\n   locktype    |          relation          |        mode         |   tid   |  vtid   |  pid  | granted\r\n---------------+----------------------------+---------------------+---------+---------+-------+---------\r\n relation      | pg_locks                   | AccessShareLock     |         | 2/80438 | 56984 | t\r\n relation      | users_pkey                 | AccessShareLock     |         | 2/80438 | 56984 | t\r\n relation      | index_addresses_on_user_id | AccessShareLock     |         | 2/80438 | 56984 | t\r\n relation      | addresses_pkey             | AccessShareLock     |         | 2/80438 | 56984 | t\r\n virtualxid    |                            | ExclusiveLock       |         | 2/80438 | 56984 | t\r\n relation      | addresses                  | AccessShareLock     |         | 2/80438 | 56984 | t\r\n relation      | addresses                  | AccessExclusiveLock |         | 2/80438 | 56984 | t\r\n transactionid |                            | ExclusiveLock       | 3919702 | 2/80438 | 56984 | t\r\n relation      | users                      | AccessShareLock     |         | 2/80438 | 56984 | t\r\n relation      | users                      | RowShareLock        |         | 2/80438 | 56984 | t\r\n relation      | users                      | AccessExclusiveLock |         | 2/80438 | 56984 | t\r\n(11 rows)\r\n```\r\n\r\n...my transaction acquires an `AccessExclusiveLock` on `users` which is extremely detrimental on a high-traffic table, esp. when Postgres performs a potentially lengthy query to validate the check.\r\n\r\nOn the other hand, I can take a two-step approach which significantly reduces this burden; by introducing an invalid constraint in one transaction and validating it in another, the locks acquired are much less restrictive:\r\n```\r\nmyapp=# BEGIN;\r\nBEGIN\r\nmyapp=# ALTER TABLE addresses ADD CONSTRAINT \"fk_rails_48c9e0c5a2\" FOREIGN KEY (\"user_id\") REFERENCES \"users\" (\"id\") NOT VALID;\r\nALTER TABLE\r\nmyapp=# COMMIT;\r\nCOMMIT\r\nmyapp=# BEGIN;\r\nBEGIN\r\nmyapp=# ALTER TABLE addresses VALIDATE CONSTRAINT \"fk_rails_48c9e0c5a2\";\r\nALTER TABLE\r\nmyapp=# SELECT locktype, relation::regclass, mode, transactionid AS tid, virtualtransaction AS vtid, pid, granted FROM pg_locks;\r\n   locktype    |          relation          |           mode           |   tid   |  vtid   |  pid  | granted\r\n---------------+----------------------------+--------------------------+---------+---------+-------+---------\r\n relation      | users_pkey                 | AccessShareLock          |         | 2/80443 | 56984 | t\r\n relation      | index_addresses_on_user_id | AccessShareLock          |         | 2/80443 | 56984 | t\r\n relation      | addresses_pkey             | AccessShareLock          |         | 2/80443 | 56984 | t\r\n relation      | addresses                  | AccessShareLock          |         | 2/80443 | 56984 | t\r\n relation      | users                      | AccessShareLock          |         | 2/80443 | 56984 | t\r\n relation      | users                      | RowShareLock             |         | 2/80443 | 56984 | t\r\n relation      | pg_locks                   | AccessShareLock          |         | 2/80443 | 56984 | t\r\n virtualxid    |                            | ExclusiveLock            |         | 2/80443 | 56984 | t\r\n relation      | addresses                  | ShareUpdateExclusiveLock |         | 2/80443 | 56984 | t\r\n transactionid |                            | ExclusiveLock            | 3919706 | 2/80443 | 56984 | t\r\n(10 rows)\r\n```\r\n\r\nThe first transaction acquires the same `AccessExclusiveLock` on the `users` table, but \"the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped\" (source: [postgres docs](https://www.postgresql.org/docs/9.3/static/sql-altertable.html)). Subsequently, the validation step does not block reads or writes on the `users` table. 💯 \r\n\r\nSo, this PR introduces two things:\r\n\r\n- The ability to create invalid foreign keys by specifying the option `valid: false`\r\n- A `validate_foreign_key` method (which takes the same variety of params as the other foreign key methods) to validate a foreign key\r\n\r\nI've heard rumors about this being on the roadmap for the Postgres team, i.e. skipping the check if the table being altered is empty and marking the constraint valid. In any case, perhaps someday this will be more easily achieved with built-in Postgres, but for now it's an issue.","created_at":"2017-01-20T23:13:33Z","updated_at":"2017-02-26T23:19:56Z","closed_at":null,"merged_at":null,"merge_commit_sha":"b25deba32033f628156ba2a7bf7256154f5ae63c","assignee":null,"assignees":[],"milestone":null,"commits_url":"https://api.github.com/repos/rails/rails/pulls/27756/commits","review_comments_url":"https://api.github.com/repos/rails/rails/pulls/27756/comments","review_comment_url":"https://api.github.com/repos/rails/rails/pulls/comments{/number}","comments_url":"https://api.github.com/repos/rails/rails/issues/27756/comments","statuses_url":"https://api.github.com/repos/rails/rails/statuses/0c517f242fa4daacf0296e05ebe19023b9e2163b","head":{"label":"travisofthenorth:validate-foreign-keys","ref":"validate-foreign-keys","sha":"0c517f242fa4daacf0296e05ebe19023b9e2163b","user":{"login":"travisofthenorth","id":1894627,"avatar_url":"https://avatars.githubusercontent.com/u/1894627?v=3","gravatar_id":"","url":"https://api.github.com/users/travisofthenorth","html_url":"https://github.com/travisofthenorth","followers_url":"https://api.github.com/users/travisofthenorth/followers","following_url":"https://api.github.com/users/travisofthenorth/following{/other_user}","gists_url":"https://api.github.com/users/travisofthenorth/gists{/gist_id}","starred_url":"https://api.github.com/users/travisofthenorth/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/travisofthenorth/subscriptions","organizations_url":"https://api.github.com/users/travisofthenorth/orgs","repos_url":"https://api.github.com/users/travisofthenorth/repos","events_url":"https://api.github.com/users/travisofthenorth/events{/privacy}","received_events_url":"https://api.github.com/users/travisofthenorth/received_events","type":"User","site_admin":false},"repo":{"id":79579948,"name":"rails","full_name":"travisofthenorth/rails","owner":{"login":"travisofthenorth","id":1894627,"avatar_url":"https://avatars.githubusercontent.com/u/1894627?v=3","gravatar_id":"","url":"https://api.github.com/users/travisofthenorth","html_url":"https://github.com/travisofthenorth","followers_url":"https://api.github.com/users/travisofthenorth/followers","following_url":"https://api.github.com/users/travisofthenorth/following{/other_user}","gists_url":"https://api.github.com/users/travisofthenorth/gists{/gist_id}","starred_url":"https://api.github.com/users/travisofthenorth/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/travisofthenorth/subscriptions","organizations_url":"https://api.github.com/users/travisofthenorth/orgs","repos_url":"https://api.github.com/users/travisofthenorth/repos","events_url":"https://api.github.com/users/travisofthenorth/events{/privacy}","received_events_url":"https://api.github.com/users/travisofthenorth/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/travisofthenorth/rails","description":"Ruby on Rails","fork":true,"url":"https://api.github.com/repos/travisofthenorth/rails","forks_url":"https://api.github.com/repos/travisofthenorth/rails/forks","keys_url":"https://api.github.com/repos/travisofthenorth/rails/keys{/key_id}","collaborators_url":"https://api.github.com/repos/travisofthenorth/rails/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/travisofthenorth/rails/teams","hooks_url":"https://api.github.com/repos/travisofthenorth/rails/hooks","issue_events_url":"https://api.github.com/repos/travisofthenorth/rails/issues/events{/number}","events_url":"https://api.github.com/repos/travisofthenorth/rails/events","assignees_url":"https://api.github.com/repos/travisofthenorth/rails/assignees{/user}","branches_url":"https://api.github.com/repos/travisofthenorth/rails/branches{/branch}","tags_url":"https://api.github.com/repos/travisofthenorth/rails/tags","blobs_url":"https://api.github.com/repos/travisofthenorth/rails/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/travisofthenorth/rails/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/travisofthenorth/rails/git/refs{/sha}","trees_url":"https://api.github.com/repos/travisofthenorth/rails/git/trees{/sha}","statuses_url":"https://api.github.com/repos/travisofthenorth/rails/statuses/{sha}","languages_url":"https://api.github.com/repos/travisofthenorth/rails/languages","stargazers_url":"https://api.github.com/repos/travisofthenorth/rails/stargazers","contributors_url":"https://api.github.com/repos/travisofthenorth/rails/contributors","subscribers_url":"https://api.github.com/repos/travisofthenorth/rails/subscribers","subscription_url":"https://api.github.com/repos/travisofthenorth/rails/subscription","commits_url":"https://api.github.com/repos/travisofthenorth/rails/commits{/sha}","git_commits_url":"https://api.github.com/repos/travisofthenorth/rails/git/commits{/sha}","comments_url":"https://api.github.com/repos/travisofthenorth/rails/comments{/number}","issue_comment_url":"https://api.github.com/repos/travisofthenorth/rails/issues/comments{/number}","contents_url":"https://api.github.com/repos/travisofthenorth/rails/contents/{+path}","compare_url":"https://api.github.com/repos/travisofthenorth/rails/compare/{base}...{head}","merges_url":"https://api.github.com/repos/travisofthenorth/rails/merges","archive_url":"https://api.github.com/repos/travisofthenorth/rails/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/travisofthenorth/rails/downloads","issues_url":"https://api.github.com/repos/travisofthenorth/rails/issues{/number}","pulls_url":"https://api.github.com/repos/travisofthenorth/rails/pulls{/number}","milestones_url":"https://api.github.com/repos/travisofthenorth/rails/milestones{/number}","notifications_url":"https://api.github.com/repos/travisofthenorth/rails/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/travisofthenorth/rails/labels{/name}","releases_url":"https://api.github.com/repos/travisofthenorth/rails/releases{/id}","deployments_url":"https://api.github.com/repos/travisofthenorth/rails/deployments","created_at":"2017-01-20T17:16:55Z","updated_at":"2017-01-20T17:17:44Z","pushed_at":"2017-02-26T23:13:08Z","git_url":"git://github.com/travisofthenorth/rails.git","ssh_url":"git@github.com:travisofthenorth/rails.git","clone_url":"https://github.com/travisofthenorth/rails.git","svn_url":"https://github.com/travisofthenorth/rails","homepage":"http://rubyonrails.org","size":142974,"stargazers_count":0,"watchers_count":0,"language":"Ruby","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"rails:master","ref":"master","sha":"c4957f8c540698f44255402c6e1b3398da69c651","user":{"login":"rails","id":4223,"avatar_url":"https://avatars.githubusercontent.com/u/4223?v=3","gravatar_id":"","url":"https://api.github.com/users/rails","html_url":"https://github.com/rails","followers_url":"https://api.github.com/users/rails/followers","following_url":"https://api.github.com/users/rails/following{/other_user}","gists_url":"https://api.github.com/users/rails/gists{/gist_id}","starred_url":"https://api.github.com/users/rails/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rails/subscriptions","organizations_url":"https://api.github.com/users/rails/orgs","repos_url":"https://api.github.com/users/rails/repos","events_url":"https://api.github.com/users/rails/events{/privacy}","received_events_url":"https://api.github.com/users/rails/received_events","type":"Organization","site_admin":false},"repo":{"id":8514,"name":"rails","full_name":"rails/rails","owner":{"login":"rails","id":4223,"avatar_url":"https://avatars.githubusercontent.com/u/4223?v=3","gravatar_id":"","url":"https://api.github.com/users/rails","html_url":"https://github.com/rails","followers_url":"https://api.github.com/users/rails/followers","following_url":"https://api.github.com/users/rails/following{/other_user}","gists_url":"https://api.github.com/users/rails/gists{/gist_id}","starred_url":"https://api.github.com/users/rails/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rails/subscriptions","organizations_url":"https://api.github.com/users/rails/orgs","repos_url":"https://api.github.com/users/rails/repos","events_url":"https://api.github.com/users/rails/events{/privacy}","received_events_url":"https://api.github.com/users/rails/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/rails/rails","description":"Ruby on Rails","fork":false,"url":"https://api.github.com/repos/rails/rails","forks_url":"https://api.github.com/repos/rails/rails/forks","keys_url":"https://api.github.com/repos/rails/rails/keys{/key_id}","collaborators_url":"https://api.github.com/repos/rails/rails/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/rails/rails/teams","hooks_url":"https://api.github.com/repos/rails/rails/hooks","issue_events_url":"https://api.github.com/repos/rails/rails/issues/events{/number}","events_url":"https://api.github.com/repos/rails/rails/events","assignees_url":"https://api.github.com/repos/rails/rails/assignees{/user}","branches_url":"https://api.github.com/repos/rails/rails/branches{/branch}","tags_url":"https://api.github.com/repos/rails/rails/tags","blobs_url":"https://api.github.com/repos/rails/rails/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/rails/rails/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/rails/rails/git/refs{/sha}","trees_url":"https://api.github.com/repos/rails/rails/git/trees{/sha}","statuses_url":"https://api.github.com/repos/rails/rails/statuses/{sha}","languages_url":"https://api.github.com/repos/rails/rails/languages","stargazers_url":"https://api.github.com/repos/rails/rails/stargazers","contributors_url":"https://api.github.com/repos/rails/rails/contributors","subscribers_url":"https://api.github.com/repos/rails/rails/subscribers","subscription_url":"https://api.github.com/repos/rails/rails/subscription","commits_url":"https://api.github.com/repos/rails/rails/commits{/sha}","git_commits_url":"https://api.github.com/repos/rails/rails/git/commits{/sha}","comments_url":"https://api.github.com/repos/rails/rails/comments{/number}","issue_comment_url":"https://api.github.com/repos/rails/rails/issues/comments{/number}","contents_url":"https://api.github.com/repos/rails/rails/contents/{+path}","compare_url":"https://api.github.com/repos/rails/rails/compare/{base}...{head}","merges_url":"https://api.github.com/repos/rails/rails/merges","archive_url":"https://api.github.com/repos/rails/rails/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/rails/rails/downloads","issues_url":"https://api.github.com/repos/rails/rails/issues{/number}","pulls_url":"https://api.github.com/repos/rails/rails/pulls{/number}","milestones_url":"https://api.github.com/repos/rails/rails/milestones{/number}","notifications_url":"https://api.github.com/repos/rails/rails/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/rails/rails/labels{/name}","releases_url":"https://api.github.com/repos/rails/rails/releases{/id}","deployments_url":"https://api.github.com/repos/rails/rails/deployments","created_at":"2008-04-11T02:19:47Z","updated_at":"2017-02-26T22:54:12Z","pushed_at":"2017-02-26T23:13:11Z","git_url":"git://github.com/rails/rails.git","ssh_url":"git@github.com:rails/rails.git","clone_url":"https://github.com/rails/rails.git","svn_url":"https://github.com/rails/rails","homepage":"http://rubyonrails.org","size":143749,"stargazers_count":34621,"watchers_count":34621,"language":"Ruby","has_issues":true,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":14122,"mirror_url":null,"open_issues_count":1270,"forks":14122,"open_issues":1270,"watchers":34621,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/rails/rails/pulls/27756"},"html":{"href":"https://github.com/rails/rails/pull/27756"},"issue":{"href":"https://api.github.com/repos/rails/rails/issues/27756"},"comments":{"href":"https://api.github.com/repos/rails/rails/issues/27756/comments"},"review_comments":{"href":"https://api.github.com/repos/rails/rails/pulls/27756/comments"},"review_comment":{"href":"https://api.github.com/repos/rails/rails/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/rails/rails/pulls/27756/commits"},"statuses":{"href":"https://api.github.com/repos/rails/rails/statuses/0c517f242fa4daacf0296e05ebe19023b9e2163b"}}}}
{"actor":{"display_login":"travisofthenorth"}}