Re: DETACH PARTITION and FOR EACH ROW triggers on partitioned tables

Amit Langote <amitlangote09@gmail.com> writes:
> On Thu, Apr 9, 2020 at 3:09 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> My point is that so long as you only allow the case of exactly one parent,
>> you can just delete the child trigger, because it must belong to that
>> parent.  As soon as there's any flexibility, you are going to end up
>> reinventing all the stuff we had to invent to manage
>> maybe-or-maybe-not-inherited columns.  So I think the "detach" idea
>> is the first step on that road, and I counsel not taking that step.

> As mentioned upthread, we have behavior #1 for indexes (attach
> existing / detach & keep), without any of the *islocal, *inhcount
> infrastructure. It is a bit complex, because we need logic to check
> the equivalence of an existing index on the partition being attached,
> so implementing the same behavior for trigger is going to have to be
> almost as complex.  Considering that #2 will be much simpler to
> implement, but would be asymmetric with everything else.

I think there is justification for jumping through some hoops for
indexes, because they can be extremely expensive to recreate.
The same argument doesn't hold even a little bit for child
triggers, though.

Also it can be expected that an index will still behave sensibly after
its table is standalone, whereas that's far from obvious for a trigger
that was meant to work on partition member tables.

            regards, tom lane