Re: BEFORE trigger can cause undetected partition constraint violation

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I just discovered that a BEFORE trigger can allow data into a
> partition that violates the relevant partition constraint. This is
> bad.

Without having checked the code, I imagine the reason for this is
that BEFORE triggers are fired after tuple routing occurs.

Re-ordering that seems problematic, because what if you have different
triggers on different partitions? We'd have to forbid that, probably
by saying that only the parent table's BEFORE ROW triggers are fired,
but that seems not very nice.

The alternative is to forbid BEFORE triggers on partitions to alter
the routing result, probably by rechecking the partition constraint
after trigger firing.

We have always checked ordinary CHECK constraints after BEFORE
triggers fire, precisely because a trigger might change the data
to make it fail (or pass!) a constraint. I take it somebody
decided that wasn't necessary for partition constraints. Penny
wise and pound foolish?

regards, tom lane