{"action":"created","issue":{"url":"https://api.github.com/repos/keithf4/pg_partman/issues/162","repository_url":"https://api.github.com/repos/keithf4/pg_partman","labels_url":"https://api.github.com/repos/keithf4/pg_partman/issues/162/labels{/name}","comments_url":"https://api.github.com/repos/keithf4/pg_partman/issues/162/comments","events_url":"https://api.github.com/repos/keithf4/pg_partman/issues/162/events","html_url":"https://github.com/keithf4/pg_partman/issues/162","id":203577861,"number":162,"title":"UPSERT DO NOTHING throws an exception.","user":{"login":"Freyr666","id":10967023,"avatar_url":"https://avatars.githubusercontent.com/u/10967023?v=3","gravatar_id":"","url":"https://api.github.com/users/Freyr666","html_url":"https://github.com/Freyr666","followers_url":"https://api.github.com/users/Freyr666/followers","following_url":"https://api.github.com/users/Freyr666/following{/other_user}","gists_url":"https://api.github.com/users/Freyr666/gists{/gist_id}","starred_url":"https://api.github.com/users/Freyr666/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Freyr666/subscriptions","organizations_url":"https://api.github.com/users/Freyr666/orgs","repos_url":"https://api.github.com/users/Freyr666/repos","events_url":"https://api.github.com/users/Freyr666/events{/privacy}","received_events_url":"https://api.github.com/users/Freyr666/received_events","type":"User","site_admin":false},"labels":[{"id":88906243,"url":"https://api.github.com/repos/keithf4/pg_partman/labels/pending%20review","name":"pending review","color":"fbca04","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":3,"created_at":"2017-01-27T08:10:19Z","updated_at":"2017-02-01T15:25:41Z","closed_at":null,"body":"Hi there. I have a table, partitioned by time column, which has unique constrain. Sometimes dublicate values appear, one in parent table and one in child partition. So when i call partman.partition_data_time it throws an exception:\r\n\r\nParent table is created with p_upsert := 'ON CONFLICT (time) DO NOTHING'\r\n\r\n```\r\nERROR:  duplicate key value violates unique constraint \"table_p2017_01_time_key\"\r\nDETAIL:  Key (time)=(2017-01-26 15:07:43) already exists.\r\nCONTEXT:  SQL statement \"WITH partition_data AS (\r\n                        DELETE FROM ONLY public.table WHERE time >= '2017-01-01 00:00:00+03' AND time < '2017-02-01 00:00:00+03' RETURNING *)\r\n                     INSERT INTO public.table_p2017_01 SELECT * FROM partition_data\"\r\nPL/pgSQL function partman.partition_data_time(text,integer,interval,numeric,text,boolean) line 181 at EXECUTE\r\n```\r\n\r\nI want values to move into child tables when they are unique and to be deleted if they are already in the child table. What should i do? \r\n\r\nThough\r\n```\r\nWITH partition_data AS (DELETE FROM ONLY public.table WHERE time >= '2017-01-01 00:00:00+03' AND time < '2017-02-01 00:00:00+03' RETURNING *)\r\nINSERT INTO public.table_p2017_01 SELECT * FROM partition_data ON CONFLICT (time) DO NOTHING;\r\n```\r\nworks fine, it seems that `partman.partition_data_time` does not even use ON CONFLICT statement. What is wrong with that?\r\n\r\nI use pg_partman95.x86_64 version 2.6.2-1.f24 for postgres 9.5 from yum postgres repo on fedora 24.\r\n\r\nThanx."},"comment":{"url":"https://api.github.com/repos/keithf4/pg_partman/issues/comments/276686480","html_url":"https://github.com/keithf4/pg_partman/issues/162#issuecomment-276686480","issue_url":"https://api.github.com/repos/keithf4/pg_partman/issues/162","id":276686480,"user":{"login":"keithf4","id":825402,"avatar_url":"https://avatars.githubusercontent.com/u/825402?v=3","gravatar_id":"","url":"https://api.github.com/users/keithf4","html_url":"https://github.com/keithf4","followers_url":"https://api.github.com/users/keithf4/followers","following_url":"https://api.github.com/users/keithf4/following{/other_user}","gists_url":"https://api.github.com/users/keithf4/gists{/gist_id}","starred_url":"https://api.github.com/users/keithf4/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/keithf4/subscriptions","organizations_url":"https://api.github.com/users/keithf4/orgs","repos_url":"https://api.github.com/users/keithf4/repos","events_url":"https://api.github.com/users/keithf4/events{/privacy}","received_events_url":"https://api.github.com/users/keithf4/received_events","type":"User","site_admin":false},"created_at":"2017-02-01T15:25:41Z","updated_at":"2017-02-01T15:25:41Z","body":"The trigger on the parent table reroutes data immediately to the target table, it does not insert there then move it. If you insert the same data twice, and you have a unique constraint, you should get a conflict error on the child table (unless you have UPSERT turned on to handle it). If you don't have a constraint, your data will be duplicated in the child (or kept in the parent if the child is removed in the mean time). The only time data should be going into the parent table is when the child table for that data does not exist. \r\n\r\nDo keep in mind that constraints are not enforced across an entire partition set. They are only enforced per child table and only on the parent. This is a limitation of Postgres itself and there's not much that can be done about it at this time without some pretty severe performance implications.\r\n\r\nIf you could give me something where I could reproduce what you're seeing, I could try to investigate what the problem could be. "}}
{"actor":{"display_login":"keithf4"}}