{"action":"created","issue":{"url":"https://api.github.com/repos/keithf4/pg_partman/issues/42","repository_url":"https://api.github.com/repos/keithf4/pg_partman","labels_url":"https://api.github.com/repos/keithf4/pg_partman/issues/42/labels{/name}","comments_url":"https://api.github.com/repos/keithf4/pg_partman/issues/42/comments","events_url":"https://api.github.com/repos/keithf4/pg_partman/issues/42/events","html_url":"https://github.com/keithf4/pg_partman/issues/42","id":52187602,"number":42,"title":"Automated child table triggers (WAS: Inherit DELETE/UPDATE triggers from parent)","user":{"login":"davidblewett","id":141716,"avatar_url":"https://avatars.githubusercontent.com/u/141716?v=3","gravatar_id":"","url":"https://api.github.com/users/davidblewett","html_url":"https://github.com/davidblewett","followers_url":"https://api.github.com/users/davidblewett/followers","following_url":"https://api.github.com/users/davidblewett/following{/other_user}","gists_url":"https://api.github.com/users/davidblewett/gists{/gist_id}","starred_url":"https://api.github.com/users/davidblewett/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/davidblewett/subscriptions","organizations_url":"https://api.github.com/users/davidblewett/orgs","repos_url":"https://api.github.com/users/davidblewett/repos","events_url":"https://api.github.com/users/davidblewett/events{/privacy}","received_events_url":"https://api.github.com/users/davidblewett/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/keithf4/pg_partman/labels/feature%20request","name":"feature request","color":"84b6eb"}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":9,"created_at":"2014-12-17T00:42:34Z","updated_at":"2016-07-01T09:39:40Z","closed_at":null,"body":"Currently, DELETE and UPDATE triggers that are defined on the parent will be silently ignored if they trigger action in a child table. INSERT triggers fire correctly, but from what I can tell, DELETE/UPDATE do not at all. I'm trying to automate updating an external system on changes to any data in the child tables. I can create them manually, but it would be great if pg_partman did it when it created new partitions."},"comment":{"url":"https://api.github.com/repos/keithf4/pg_partman/issues/comments/229904136","html_url":"https://github.com/keithf4/pg_partman/issues/42#issuecomment-229904136","issue_url":"https://api.github.com/repos/keithf4/pg_partman/issues/42","id":229904136,"user":{"login":"1803","id":3829383,"avatar_url":"https://avatars.githubusercontent.com/u/3829383?v=3","gravatar_id":"","url":"https://api.github.com/users/1803","html_url":"https://github.com/1803","followers_url":"https://api.github.com/users/1803/followers","following_url":"https://api.github.com/users/1803/following{/other_user}","gists_url":"https://api.github.com/users/1803/gists{/gist_id}","starred_url":"https://api.github.com/users/1803/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/1803/subscriptions","organizations_url":"https://api.github.com/users/1803/orgs","repos_url":"https://api.github.com/users/1803/repos","events_url":"https://api.github.com/users/1803/events{/privacy}","received_events_url":"https://api.github.com/users/1803/received_events","type":"User","site_admin":false},"created_at":"2016-07-01T09:39:40Z","updated_at":"2016-07-01T09:39:40Z","body":"**Trigger inheritance:**\r\n\r\n_This is still WIP._\r\n\r\nSide note: to make sure the `before insert` partitioning trigger fires last (we don't want to override any default value triggers etc.) it is suggested to name it z_[table_name]_[suffix].\r\n\r\n**Concept:**\r\n\r\n1. Not all triggers should be inherited;\r\n2. There is no reason to create two or more triggers with different names, but calling the same trigger function on the same condition (`tgtype`);\r\n3. There is no need to use `when` clause in triggers since all the checks can be done inside the trigger function (just for simplicity);\r\n_4. There are situations when trigger function code might need to be adjusted (for example my case with `update` might use this feature); [not impletemented]_\r\n_5. Triggers may change, thus a function is needed that can remove triggers and/or functions; [not impletemented]_\r\n_6. New child-tables can be added and triggers should be applied to them automatically; [not implemented]_\r\n\r\nAs a result:\r\n\r\nThere's a table `referencing part_config(parent_name)` that keeps list of triggers for each parent table. It also stores the trigger type (`consistent` - same trigger function for all tables, `custom` - trigger function should be created for each table). For custom triggers an additional field is used that stores function source and name prefix. The point of custom functions is to adjust trigger code for each table (in my case for performance reasons).\r\n\r\nTrigger creation is done in a stored procedure that uses a for cycle that checks if the trigger with same tgtype and function exists on each table of parittion.\r\n\r\nSome changes need to be done to `run_maintenance` function to reflect the need to create triggers for newly created child-tables\r\n\r\nUser should specify the triggers that need to be inherited by adding a corresponding row to the `part_trigger` table.\r\n\r\nHere's the code:\r\n\r\nhttp://pastebin.com/w3jzQBDu\r\n\r\nKeep in mind: for test case you need to create extension, then apply the patch and then proceed."}}
{"actor":{"display_login":"1803"}}