Re: BEFORE trigger can cause undetected partition constraint violation

On Thu, Jun 1, 2017 at 6:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Without having checked the code, I imagine the reason for this is
> that BEFORE triggers are fired after tuple routing occurs.

Yep.

> 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 parent hasn't got any triggers; that's forbidden.

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

That's what we need to do. Until we do tuple routing, we don't know
which partition we're addressing, so we don't know which triggers
we're firing. So the only way to prevent this is to recheck. Which I
think is supposed to work already, but apparently doesn't.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company