Re: FOR EACH ROW triggers on partitioned tables

On Thu, Feb 22, 2018 at 6:52 PM, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
> Alvaro Herrera wrote:
>> Another option is to rethink this feature from the ground up: instead of
>> cloning catalog rows for each children, maybe we should have the trigger
>> lookup code, when running DML on the child relation (the partition),
>> obtain trigger entries not only for the child relation itself but also
>> for its parents recursively -- so triggers defined in the parent are
>> fired for the partitions, too.
>
> I have written this, and it seems to work fine; it's attached.
>
> Generally speaking, I like this better than my previously proposed
> patch: having duplicate pg_trigger rows seems lame, in hindsight.
>
> I haven't measured the performance loss, but we now scan pg_inherits
> each time we build a relcache entry and relhastriggers=on. Can't be
> good. In general, the pg_inherits stuff looks generally unnatural --
> manually doing scans upwards (find parents) and downwards (find
> partitions). It's messy and there are no nice abstractions.
> Partitioning looks too much bolted-on still.

Elsewhere, we've put a lot of blood, sweat, and tears into making sure
that we only traverse the inheritance hierarchy from top to bottom.
Otherwise, we're adding deadlock hazards. I think it's categorically
unacceptable to do traversals in the opposite order -- if you do, then
an UPDATE on a child could deadlock with a LOCK TABLE on the parent.
That will not win us any awards.

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