{"action":"created","issue":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/19141","repository_url":"https://api.github.com/repos/cockroachdb/cockroach","labels_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/19141/labels{/name}","comments_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/19141/comments","events_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/19141/events","html_url":"https://github.com/cockroachdb/cockroach/issues/19141","id":264035525,"number":19141,"title":"sql: primary keys cannot be changed","user":{"login":"benesch","id":882976,"avatar_url":"https://avatars1.githubusercontent.com/u/882976?v=4","gravatar_id":"","url":"https://api.github.com/users/benesch","html_url":"https://github.com/benesch","followers_url":"https://api.github.com/users/benesch/followers","following_url":"https://api.github.com/users/benesch/following{/other_user}","gists_url":"https://api.github.com/users/benesch/gists{/gist_id}","starred_url":"https://api.github.com/users/benesch/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/benesch/subscriptions","organizations_url":"https://api.github.com/users/benesch/orgs","repos_url":"https://api.github.com/users/benesch/repos","events_url":"https://api.github.com/users/benesch/events{/privacy}","received_events_url":"https://api.github.com/users/benesch/received_events","type":"User","site_admin":false},"labels":[{"id":728643548,"url":"https://api.github.com/repos/cockroachdb/cockroach/labels/partitioning","name":"partitioning","color":"8a61c6","default":false}],"state":"open","locked":false,"assignee":{"login":"mjibson","id":41181,"avatar_url":"https://avatars1.githubusercontent.com/u/41181?v=4","gravatar_id":"","url":"https://api.github.com/users/mjibson","html_url":"https://github.com/mjibson","followers_url":"https://api.github.com/users/mjibson/followers","following_url":"https://api.github.com/users/mjibson/following{/other_user}","gists_url":"https://api.github.com/users/mjibson/gists{/gist_id}","starred_url":"https://api.github.com/users/mjibson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mjibson/subscriptions","organizations_url":"https://api.github.com/users/mjibson/orgs","repos_url":"https://api.github.com/users/mjibson/repos","events_url":"https://api.github.com/users/mjibson/events{/privacy}","received_events_url":"https://api.github.com/users/mjibson/received_events","type":"User","site_admin":false},"assignees":[{"login":"mjibson","id":41181,"avatar_url":"https://avatars1.githubusercontent.com/u/41181?v=4","gravatar_id":"","url":"https://api.github.com/users/mjibson","html_url":"https://github.com/mjibson","followers_url":"https://api.github.com/users/mjibson/followers","following_url":"https://api.github.com/users/mjibson/following{/other_user}","gists_url":"https://api.github.com/users/mjibson/gists{/gist_id}","starred_url":"https://api.github.com/users/mjibson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mjibson/subscriptions","organizations_url":"https://api.github.com/users/mjibson/orgs","repos_url":"https://api.github.com/users/mjibson/repos","events_url":"https://api.github.com/users/mjibson/events{/privacy}","received_events_url":"https://api.github.com/users/mjibson/received_events","type":"User","site_admin":false},{"login":"bobvawter","id":1158548,"avatar_url":"https://avatars3.githubusercontent.com/u/1158548?v=4","gravatar_id":"","url":"https://api.github.com/users/bobvawter","html_url":"https://github.com/bobvawter","followers_url":"https://api.github.com/users/bobvawter/followers","following_url":"https://api.github.com/users/bobvawter/following{/other_user}","gists_url":"https://api.github.com/users/bobvawter/gists{/gist_id}","starred_url":"https://api.github.com/users/bobvawter/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bobvawter/subscriptions","organizations_url":"https://api.github.com/users/bobvawter/orgs","repos_url":"https://api.github.com/users/bobvawter/repos","events_url":"https://api.github.com/users/bobvawter/events{/privacy}","received_events_url":"https://api.github.com/users/bobvawter/received_events","type":"User","site_admin":false},{"login":"awoods187","id":22278911,"avatar_url":"https://avatars1.githubusercontent.com/u/22278911?v=4","gravatar_id":"","url":"https://api.github.com/users/awoods187","html_url":"https://github.com/awoods187","followers_url":"https://api.github.com/users/awoods187/followers","following_url":"https://api.github.com/users/awoods187/following{/other_user}","gists_url":"https://api.github.com/users/awoods187/gists{/gist_id}","starred_url":"https://api.github.com/users/awoods187/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/awoods187/subscriptions","organizations_url":"https://api.github.com/users/awoods187/orgs","repos_url":"https://api.github.com/users/awoods187/repos","events_url":"https://api.github.com/users/awoods187/events{/privacy}","received_events_url":"https://api.github.com/users/awoods187/received_events","type":"User","site_admin":false}],"milestone":{"url":"https://api.github.com/repos/cockroachdb/cockroach/milestones/13","html_url":"https://github.com/cockroachdb/cockroach/milestone/13","labels_url":"https://api.github.com/repos/cockroachdb/cockroach/milestones/13/labels","id":2745639,"number":13,"title":"2.1","description":"","creator":{"login":"knz","id":642886,"avatar_url":"https://avatars3.githubusercontent.com/u/642886?v=4","gravatar_id":"","url":"https://api.github.com/users/knz","html_url":"https://github.com/knz","followers_url":"https://api.github.com/users/knz/followers","following_url":"https://api.github.com/users/knz/following{/other_user}","gists_url":"https://api.github.com/users/knz/gists{/gist_id}","starred_url":"https://api.github.com/users/knz/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/knz/subscriptions","organizations_url":"https://api.github.com/users/knz/orgs","repos_url":"https://api.github.com/users/knz/repos","events_url":"https://api.github.com/users/knz/events{/privacy}","received_events_url":"https://api.github.com/users/knz/received_events","type":"User","site_admin":false},"open_issues":415,"closed_issues":29,"state":"open","created_at":"2017-09-05T15:30:49Z","updated_at":"2018-03-13T19:05:17Z","due_on":null,"closed_at":null},"comments":8,"created_at":"2017-10-09T21:43:59Z","updated_at":"2018-03-13T19:54:31Z","closed_at":null,"author_association":"OWNER","body":"It is not currently possible to change a table's primary key, e.g. via\r\n\r\n```sql\r\nALTER TABLE foo DROP CONSTRAINT old_pkey;\r\nALTER TABLE foo ADD PRIMARY KEY (new, pkey, cols)\r\n```\r\n\r\nThis becomes more important in 1.2 because the upcoming partitioning feature (#18683) will only allow partitioning by prefixes of the primary key. For example, suppose you want to partition this table by country:\r\n\r\n```sql\r\nCREATE TABLE users (\r\n  id INT PRIMARY KEY,\r\n  name STRING,\r\n  country STRING,\r\n  ...\r\n)\r\n```\r\n\r\nThe table needs to have a composite primary key that lists country first:\r\n\r\n```sql\r\nCREATE TABLE users (\r\n  id INT,\r\n  name STRING,\r\n  country STRING,\r\n  ...,\r\n  PRIMARY KEY (int, country)\r\n) PARTITION BY LIST (country)...\r\n```\r\n\r\nThis essentially requires that the schema be designed with partitioning in mind from the get-go until we can alter primary keys."},"comment":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/comments/372797149","html_url":"https://github.com/cockroachdb/cockroach/issues/19141#issuecomment-372797149","issue_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/19141","id":372797149,"user":{"login":"petermattis","id":6201335,"avatar_url":"https://avatars0.githubusercontent.com/u/6201335?v=4","gravatar_id":"","url":"https://api.github.com/users/petermattis","html_url":"https://github.com/petermattis","followers_url":"https://api.github.com/users/petermattis/followers","following_url":"https://api.github.com/users/petermattis/following{/other_user}","gists_url":"https://api.github.com/users/petermattis/gists{/gist_id}","starred_url":"https://api.github.com/users/petermattis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/petermattis/subscriptions","organizations_url":"https://api.github.com/users/petermattis/orgs","repos_url":"https://api.github.com/users/petermattis/repos","events_url":"https://api.github.com/users/petermattis/events{/privacy}","received_events_url":"https://api.github.com/users/petermattis/received_events","type":"User","site_admin":false},"created_at":"2018-03-13T19:54:31Z","updated_at":"2018-03-13T19:54:31Z","author_association":"CONTRIBUTOR","body":"Has there been any thought on how this would be implemented? Changing the primary key is difficult because it is stored in indexes. One thought is that we make a copy of all of the secondary indexes and backfill both them and the new primary index and afterwards delete the old secondary indexes and primary index. Seems like that would conceptually work, though it would be slow.\r\n\r\n@mjibson, @danhhz, @vivekmenezes Thoughts?"}}
{"actor":{"display_login":"petermattis"}}