{"action":"created","issue":{"url":"https://api.github.com/repos/ManageIQ/manageiq-schema/issues/19","repository_url":"https://api.github.com/repos/ManageIQ/manageiq-schema","labels_url":"https://api.github.com/repos/ManageIQ/manageiq-schema/issues/19/labels{/name}","comments_url":"https://api.github.com/repos/ManageIQ/manageiq-schema/issues/19/comments","events_url":"https://api.github.com/repos/ManageIQ/manageiq-schema/issues/19/events","html_url":"https://github.com/ManageIQ/manageiq-schema/pull/19","id":238122719,"number":19,"title":"Add unique indexes for Containers tables","user":{"login":"Ladas","id":1737058,"avatar_url":"https://avatars0.githubusercontent.com/u/1737058?v=4","gravatar_id":"","url":"https://api.github.com/users/Ladas","html_url":"https://github.com/Ladas","followers_url":"https://api.github.com/users/Ladas/followers","following_url":"https://api.github.com/users/Ladas/following{/other_user}","gists_url":"https://api.github.com/users/Ladas/gists{/gist_id}","starred_url":"https://api.github.com/users/Ladas/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Ladas/subscriptions","organizations_url":"https://api.github.com/users/Ladas/orgs","repos_url":"https://api.github.com/users/Ladas/repos","events_url":"https://api.github.com/users/Ladas/events{/privacy}","received_events_url":"https://api.github.com/users/Ladas/received_events","type":"User","site_admin":false},"labels":[{"id":602426715,"url":"https://api.github.com/repos/ManageIQ/manageiq-schema/labels/enhancement","name":"enhancement","color":"84b6eb","default":true},{"id":654726012,"url":"https://api.github.com/repos/ManageIQ/manageiq-schema/labels/index","name":"index","color":"5319e7","default":false}],"state":"open","locked":false,"assignee":{"login":"Fryguy","id":52120,"avatar_url":"https://avatars3.githubusercontent.com/u/52120?v=4","gravatar_id":"","url":"https://api.github.com/users/Fryguy","html_url":"https://github.com/Fryguy","followers_url":"https://api.github.com/users/Fryguy/followers","following_url":"https://api.github.com/users/Fryguy/following{/other_user}","gists_url":"https://api.github.com/users/Fryguy/gists{/gist_id}","starred_url":"https://api.github.com/users/Fryguy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Fryguy/subscriptions","organizations_url":"https://api.github.com/users/Fryguy/orgs","repos_url":"https://api.github.com/users/Fryguy/repos","events_url":"https://api.github.com/users/Fryguy/events{/privacy}","received_events_url":"https://api.github.com/users/Fryguy/received_events","type":"User","site_admin":false},"assignees":[{"login":"Fryguy","id":52120,"avatar_url":"https://avatars3.githubusercontent.com/u/52120?v=4","gravatar_id":"","url":"https://api.github.com/users/Fryguy","html_url":"https://github.com/Fryguy","followers_url":"https://api.github.com/users/Fryguy/followers","following_url":"https://api.github.com/users/Fryguy/following{/other_user}","gists_url":"https://api.github.com/users/Fryguy/gists{/gist_id}","starred_url":"https://api.github.com/users/Fryguy/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Fryguy/subscriptions","organizations_url":"https://api.github.com/users/Fryguy/orgs","repos_url":"https://api.github.com/users/Fryguy/repos","events_url":"https://api.github.com/users/Fryguy/events{/privacy}","received_events_url":"https://api.github.com/users/Fryguy/received_events","type":"User","site_admin":false}],"milestone":null,"comments":25,"created_at":"2017-06-23T12:01:49Z","updated_at":"2018-02-05T11:27:53Z","closed_at":null,"author_association":"CONTRIBUTOR","pull_request":{"url":"https://api.github.com/repos/ManageIQ/manageiq-schema/pulls/19","html_url":"https://github.com/ManageIQ/manageiq-schema/pull/19","diff_url":"https://github.com/ManageIQ/manageiq-schema/pull/19.diff","patch_url":"https://github.com/ManageIQ/manageiq-schema/pull/19.patch"},"body":"Add unique indexes for Containers tables"},"comment":{"url":"https://api.github.com/repos/ManageIQ/manageiq-schema/issues/comments/363057742","html_url":"https://github.com/ManageIQ/manageiq-schema/pull/19#issuecomment-363057742","issue_url":"https://api.github.com/repos/ManageIQ/manageiq-schema/issues/19","id":363057742,"user":{"login":"Ladas","id":1737058,"avatar_url":"https://avatars0.githubusercontent.com/u/1737058?v=4","gravatar_id":"","url":"https://api.github.com/users/Ladas","html_url":"https://github.com/Ladas","followers_url":"https://api.github.com/users/Ladas/followers","following_url":"https://api.github.com/users/Ladas/following{/other_user}","gists_url":"https://api.github.com/users/Ladas/gists{/gist_id}","starred_url":"https://api.github.com/users/Ladas/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Ladas/subscriptions","organizations_url":"https://api.github.com/users/Ladas/orgs","repos_url":"https://api.github.com/users/Ladas/repos","events_url":"https://api.github.com/users/Ladas/events{/privacy}","received_events_url":"https://api.github.com/users/Ladas/received_events","type":"User","site_admin":false},"created_at":"2018-02-05T11:27:53Z","updated_at":"2018-02-05T11:27:53Z","author_association":"CONTRIBUTOR","body":"@cben @agrare @Fryguy @kbrock also, we have few indexes that can have NULL values inside. In that case postgre doesn't take those as equal.\r\n\r\ne.g. with index on :container_image_registries, that is on [:ems_id, :host, :port], we can call ```ContainerImageRegistry.create!(:ems_id => 10, :host => \"host\", :port => nil)``` multiple times and it will create the duplicates\r\n\r\nSolutions:\r\n\r\n1)  We can transform the value in index definition as e.g. ```COALESCE(\"port\", 0)```, but the index might not be used for some queries (I need to test that)\r\n\r\n2) We can define several indexes, based on what columns are nullable. So in this case we would have a second unique index for [:ems_id, :host] with ```WHERE port IS NULL``` . This will be used for queries, but then for some tables (:custom_attributes, :hardwares, :operating_systems, etc.) we would need several indexes like this defined.\r\n\r\n-----\r\n\r\nFor both, we should start to define NOT NULL constraints for columns that are not nullable.\r\n\r\n@carbonin could you also advise  ^ ?"}}
{"actor":{"display_login":"Ladas"}}