{"action":"created","issue":{"url":"https://api.github.com/repos/keithf4/pg_partman/issues/210","repository_url":"https://api.github.com/repos/keithf4/pg_partman","labels_url":"https://api.github.com/repos/keithf4/pg_partman/issues/210/labels{/name}","comments_url":"https://api.github.com/repos/keithf4/pg_partman/issues/210/comments","events_url":"https://api.github.com/repos/keithf4/pg_partman/issues/210/events","html_url":"https://github.com/keithf4/pg_partman/issues/210","id":306146213,"number":210,"title":"Error on insert in big, ID-partitioned table (check_name_length)","user":{"login":"ali-sattari","id":122799,"avatar_url":"https://avatars1.githubusercontent.com/u/122799?v=4","gravatar_id":"","url":"https://api.github.com/users/ali-sattari","html_url":"https://github.com/ali-sattari","followers_url":"https://api.github.com/users/ali-sattari/followers","following_url":"https://api.github.com/users/ali-sattari/following{/other_user}","gists_url":"https://api.github.com/users/ali-sattari/gists{/gist_id}","starred_url":"https://api.github.com/users/ali-sattari/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ali-sattari/subscriptions","organizations_url":"https://api.github.com/users/ali-sattari/orgs","repos_url":"https://api.github.com/users/ali-sattari/repos","events_url":"https://api.github.com/users/ali-sattari/events{/privacy}","received_events_url":"https://api.github.com/users/ali-sattari/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":1,"created_at":"2018-03-17T10:38:21Z","updated_at":"2018-03-17T15:48:40Z","closed_at":"2018-03-17T15:48:40Z","author_association":"NONE","body":"We are using partman on a table with roughly 2.5 billion rows. It was partitioned BY ID from the beginning and have been migrated through 2 major Postgres versions.\r\nWe have had some issues after last server migration and had to resort to manual partitioning of data for a while. Today I tried to dig into it and fix the issue.\r\n\r\nWhat I tried:\r\n- dropped extension\r\n- created extension again\r\n- created a new empty parent table\r\n- ran create_parent on it (`SELECT partman.create_parent('public.deliverys', 'id', 'partman', '20000000', Null, 2, 'on', '2500000000')`)\r\n- re inherited old parent and partitions (irrelevant)\r\n- tried to insert into new partitioned parent table and got this error:\r\n\r\n```\r\n2018-03-17 13:03:04.179 +0330 [4108] myuser@mydb ERROR:  permission denied for schema partman at character 8\r\n2018-03-17 13:03:04.179 +0330 [4108] myuser@mydb QUERY:  SELECT partman.check_name_length('deliverys', v_current_partition_id::text, TRUE)\r\n2018-03-17 13:03:04.179 +0330 [4108] myuser@mydb CONTEXT:  PL/pgSQL function deliverys_part_trig_func() line 25 at assignment\r\n2018-03-17 13:03:04.179 +0330 [4108] myuser@mydb STATEMENT:  insert into \"deliverys\".....\r\n```\r\nThe last ID at the time was `2488403795` and the corresponding partition should have been `deliverys_p2500000000`\r\n\r\nHaving encountered this before, I tried this:\r\n- ran `undo_partition_id` on new (empty) parent\r\n- removed inheritance of old tables\r\n- ran `create_parent` for a monthly partitioning (`SELECT partman.create_parent('public.deliverys', 'datetime', 'partman', 'monthly')`)\r\n- tried to insert some rows and it worked.\r\n\r\nMy take is although Postgres default limit on identifier name is 63 bytes, but partman encounters  some internal issues in handling 22 character table names (`deliverys_p2500000000`). I am not sure if this is the case and how to verify it. \r\n"},"comment":{"url":"https://api.github.com/repos/keithf4/pg_partman/issues/comments/373930175","html_url":"https://github.com/keithf4/pg_partman/issues/210#issuecomment-373930175","issue_url":"https://api.github.com/repos/keithf4/pg_partman/issues/210","id":373930175,"user":{"login":"ali-sattari","id":122799,"avatar_url":"https://avatars1.githubusercontent.com/u/122799?v=4","gravatar_id":"","url":"https://api.github.com/users/ali-sattari","html_url":"https://github.com/ali-sattari","followers_url":"https://api.github.com/users/ali-sattari/followers","following_url":"https://api.github.com/users/ali-sattari/following{/other_user}","gists_url":"https://api.github.com/users/ali-sattari/gists{/gist_id}","starred_url":"https://api.github.com/users/ali-sattari/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/ali-sattari/subscriptions","organizations_url":"https://api.github.com/users/ali-sattari/orgs","repos_url":"https://api.github.com/users/ali-sattari/repos","events_url":"https://api.github.com/users/ali-sattari/events{/privacy}","received_events_url":"https://api.github.com/users/ali-sattari/received_events","type":"User","site_admin":false},"created_at":"2018-03-17T15:48:40Z","updated_at":"2018-03-17T15:48:40Z","author_association":"NONE","body":"Yup, the user didn't have any permission to partman schema, added `GRANT` for it.\r\nSo I gather it is working fine with time based partitioning because it was in predefined and optimized range. It shouldn't happen often, or at all, since this is an archive table and grows predictably in term of date-time and ID.\r\n\r\nThanks for your quick response."}}
{"actor":{"display_login":"ali-sattari"}}