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

On Thu, Apr 09, 2020 at 09:46:38AM -0400, Tom Lane wrote:
> Amit Langote <amitlangote09(at)gmail(dot)com> writes:
> > On Thu, Apr 9, 2020 at 3:09 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

I haven't heard a compelling argument for or against either way.

Maybe the worst behavior might be if, during ATTACH, we searched for a matching
trigger, and "merged" it (marked it inherited) if it matched. That could be
bad if someone *wanted* two triggers, which seems unlikely, but to each their
own.

I implemented the simple way (and, as an experiment, 75% of the hard way).

It occured to me that we don't currently distinguish between a trigger on a
child table, and a trigger on a parent table which was recursively created on a
child. That makes sense for indexes and constraints, since the objects persist
if the table is detached, so it doesn't matter how it was defined.

But if we remove trigger during DETACH, then it's *not* the same as a trigger
that was defined on the child, and I suggest that in v13 we should make that
visible.

--
Justin