On 2018-Jun-04, Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > This kind of inconsistency is what I wanted to avoid. One of the
> > guiding principles here was that a partitioned table behaves just like a
> > regular table does; in particular, inserting directly into a partition
> > is an application-level optimization that must behave exactly like it
> > would if the insert had gone into its parent table (unless the user
> > explicitly makes it not so). If we make an insertion into the partition
> > *not* fire the trigger that would have been fired by inserting into the
> > partitioned table, that's a bug. I couldn't see a way to have the
> > BEFORE trigger handle that nicely, so I decided to punt on that feature.
>
> Reasonable, but ...
>
> > In the meantime, my inclination is to fix the documentation to point out
> > that AFTER triggers are allowed but BEFORE triggers are not.
>
> ... why doesn't the same problem apply to AFTER triggers that are attached
> to the inheritance parent?
The trigger is not executed as attached to the parent; it's only
executed as attached to the partition itself. So you create it in the
parent, and clone so that all partitions have it; when you run the
command, only the cloned trigger is run, so in effect the trigger runs
exactly once. Because the trigger runs *after* the target partition has
been selected, and the trigger cannot change that outcome (ie. it
cannot move the row to another partition) this works fine.
With a BEFORE trigger, running the trigger might change the target
partition, which has the potential for all kinds of trouble. Also,
there's room to say that the before trigger should run before partition
routing is even invoked (hence the idea of running the triggers in the
partitioned table rather than the partition). But in that case we must
not run the trigger again when executing the insertion in the chosen
partition; and we must do *something* if the user executes the command
targetting the partition rather than the parent table.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
