On 2018-May-03, Robert Haas wrote:
> On Wed, May 2, 2018 at 9:17 AM, Ashutosh Bapat
> <ashutosh.bapat@enterprisedb.com> wrote:
> > +1 for that. We should associate before row triggers with the
> > partitioned table and not with the partitions. Those should be
> > executed before tuple routing (for that partition level) kicks in. But
> > then that would be asymetric with AFTER row trigger behaviour. From
> > this POV, pushing AFTER row triggers to the individual partitions
> > doesn't look good.
>
> The asymmetry doesn't seem horrible to me on its own merits, but it
> would lead to some odd behavior: suppose you defined a BEFORE ROW
> trigger and an AFTER ROW trigger on the parent, and then inserted one
> row into the parent table and a second row directly into a partition.
> It seems like the BEFORE ROW trigger would fire only for the parent
> insert, but the AFTER ROW trigger would fire in both cases.
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.
So I stand by my original decision to disallow BEFORE triggers on
partitioned tables altogether -- we can add that as a new feature later,
keeping in mind the above ... namely to implement Robert's idea:
> What seems like a better idea is to have the BEFORE ROW trigger get
> cloned to each partition just as we do with AFTER ROW triggers, but
> arrange things so that if it already got fired for the parent table,
> it doesn't get fired again after tuple routing. This would be a bit
> tricky to get correct in cases where there are multiple levels of
> partitioning involved, but it seems doable.
In the meantime, my inclination is to fix the documentation to point out
that AFTER triggers are allowed but BEFORE triggers are not.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services