Automated child table triggers (WAS: Inherit DELETE/UPDATE triggers from parent)

[davidblewett]

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.

[keithf4]

Could you give me an example of this? Hard to follow what you're describing.

Not sure how to do any sort of automatic trigger creation on child tables right now. I don't have any plans to inherit triggers from the parent to child tables at this time since it would be very difficult to really be able to tell what triggers would and would not work properly because literally anything could be in the parent triggers. If you've got any other ideas on how to automatically create triggers on child tables, I'm open to ideas.

[jaggel]

I am having a similar situation /problem and I think inheritance on triggers would be a solution. I had a trigger on the parent table (before partitioning) that before update or insert was keeping an audit log on another table for change data capture reasons. This trigger does not execute now after partitioning. I ended up creating similar triggers on all children tables executing the same function as the parent before insert, update and it gives me the functionality I want. I think it would be good to provide a flag inherit_triggers, like inherit_fk, on the create_parent and leaving the user the flexibility to use it or not...

[davidblewett]

I use PgQ to interact with external systems (email notifications, indexing in ElasticSearch, etc). The problem is that if I want to ensure ES is always consistent with Postgres, I have to make sure that the BEFORE UPDATE/DELETE trigger is created on each partition. I've been pushing data to ES from other locations, but if those other spots have problems ES can easily become out of sync. I wanted to centralize everything through a PgQ queue, so that other pieces of the app just communicate with Postgres.

I can write a query to be run in cron to ensure the trigger appears on each partition, but I thought it would be easiest if there was some sort of facility in pg_partman.

[jaggel]

I actually started creating an apply_triggers function (like the apply_fk) to perform the automatic creation / inheritance of triggers using information_schema and pg_trigger...

[keithf4]

I think the issue you all are running into is the same one others have with inheritance in general. See https://github.com/keithf4/pg_partman/issues/18 Because the insert is never actually done on the parent (because it's a BEFORE trigger), any other triggers that would fire due to INSERT/UPDATE/DELETE will not fire. This is an issue with inheritance in general, and not an issue with pg_partman.

I'll see about a feature to create specific triggers on children. No ETA on when that will be ready, though. I'm hoping to have the subpartition feature wrapped up soon and that will let me work on more new stuff :)

Edit: Just realized, you may actually see some parent triggers fire "properly" if they are also BEFORE triggers and come before the pg_partman trigger name alphabetically. Triggers are fired in alphabetical order. May not work as you expect, though.

[davidblewett]

Yes, I know it's a problem with inheritance in general. I was trying to follow the line of thinking that the parent is considered a template that each partition would copy from. Perhaps restrict the feature to only BEFORE UPDATE / BEFORE DELETE triggers, since those are the ones that behave differently than people expect (you can find several questions/answers on the mailing lists when people ask because they didn't expect the current behavior).

[keithf4]

BEFORE INSERT also behaves this way as well (eg RETURNING does not work and it reports back that zero rows were inserted). Right now I'm thinking I'll probably have something (likely a table) where you can configure 1 or more triggers you want applied to each child table. That way they can actually be different than what's on the parent, which will likely always be the case.

[keithf4]

See if RULES can be handled here as well. See https://github.com/keithf4/pg_partman/issues/121

[1803]

Trigger inheritance:

This is still WIP.

Side 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_[tablename][suffix].

Concept:

1. Not all triggers should be inherited;
2. There is no reason to create two or more triggers with different names, but calling the same trigger function on the same condition (tgtype);
3. There is no need to use when clause in triggers since all the checks can be done inside the trigger function (just for simplicity);
4. There are situations when trigger function code might need to be adjusted (for example my case with update might use this feature);
5. Triggers may change, thus a function is needed that can remove triggers and/or functions; [not impletemented]
6. New child-tables can be added and triggers should be applied to them automatically; [not implemented]

As a result:

There'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).

Trigger 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.

Some changes need to be done to run_maintenance function to reflect the need to create triggers for newly created child-tables

User should specify the triggers that need to be inherited by adding a corresponding row to the part_trigger table.

Here's the code:

http://pastebin.com/8V2Ssn9T

Keep in mind: for test case you need to create extension, then apply the patch and then proceed.

Update 1: updated pastebin, now there's custom trigger support.

[keithf4]

Looks very interesting so far! Just did a quick overview so far and didn't really dig into exactly how it all works, but just some notes for now.

• Please separate out your timezone changes from this. That is a different issue and any changes related to that should go into their own change set.
• Use the same naming patterns for variables I have been doing already. Just makes it easier to see how things fit together if the same naming conventions are used.
• One thing that was a concern for me that has delayed my working on this is that the ordering of trigger firing should not be based solely on the trigger name. Yes, that just happens to be the convention of how postgres fires the triggers, but if you read any developer comments on the topic, it's not something that should be relied upon because it is not any sort of set standard for how postgres internals are always intended to work. That functionality could go away at any time. The response to this is generally to rewrite your triggers so one's firing does not depend on another's firing before it. When you have control of your trigger function source, this is generally pretty easy. But in the case of partitioning, and how it has to be done with triggers in postgres, it's much harder because you never know what the end user will do. Honestly don't have a solution for it myself either, and your use of a custom name does present a possible solution using the alphabetical ordering currently in place. But it isn't ideal long term. Just something to keep in mind and why I've been hesitant on trying to support this. As you've seen, it brings an incredible amount of complexity and may just be the cause of more issues for people which I then have to be able to support since I'm the maintainer of the project.

[1803]

Yeah at first I was trying to follow the same naming convention but I'm just too used to the one I use. Anyway I will edit it sometime tomorrow morning also removing timezone related stuff just to make it less confusing. I just needed an example of custom trigger and I couldn't think of anything that is simple enough both to implement and to understand. Will also add more comments just to make it easier to maintain/debug.

[agentgt]

For what its worth for others that land here I got my trigger to work by making it lexically before the partman triggers based on Keith's earlier comment

Edit: Just realized, you may actually see some parent triggers fire "properly" if they are also BEFORE triggers and come before the pg_partman trigger name alphabetically. Triggers are fired in alphabetical order. May not work as you expect, though.

For some reason I find the reliance on lexical ordering a little disturbing but it works for now. That is the partman triggers get called after mine and seem to work.

[keithf4]

Just to keep anyone that's looking for this feature informed, I'm hoping to revist this once PostgreSQL 10 comes out. Since there is no longer a user-facing trigger that controls where data goes, I think this will work much better there. There is still discussion on the final implementation of how triggers on native partition sets will actually be handled, so I'm waiting to see how that turns out. At this time, I have no plans on supporting triggers without native partitioning. The firing order when using a trigger for partitioning just seems like to much of a hassle to try and handle.

[bclennox]

Is this still planned to be supported in a future release? In our case, we would like triggers on the template table for native partitioning to be copied to the child tables, similar to indexes and foreign keys. If not, we can handle it in a separate maintenance task.

[keithf4]

I'll see if I can look into this again, but I just haven't had the time unfortunately. If your own separate maintenance task to apply the triggers from the template table works, I would be curious to see it. Might be able to incorporate it.

[WhiteWind]

I would be curious to see it

Me too!

[ignaski]

Apologies if this is not the correct place for this message. I am trying to move from no partitioning to native partitioning and struggling with 'before' trigger. Postgres only allows 'after' triggers on partitioned tables. It is only possible to add 'before' trigger on the child table. I need this trigger to update the audit column 'updated_at'. Maybe anyone here have already found a way to achieve this? Many thanks

[keithf4]

Closing this issue for now. Will see how things are with triggers during my refactor when trigger based partitioning is working, but this is a bit out of scope for pg_partman at this time.