Re: CREATE TABLE .. PARTITION OF fails to preserve tgenabled for inherited row triggers

On Tue, Oct 20, 2020 at 09:54:53PM -0300, Alvaro Herrera wrote:
> On 2020-Oct-20, Justin Pryzby wrote:
> > On Tue, Oct 20, 2020 at 03:56:30PM -0400, Tom Lane wrote:
> > > Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> > > > Hmm, next question: should we backpatch a fix for this? (This applies
> > > > all the way back to 11.) If we do, then we would change behavior of
> > > > partition creation. It's hard to see that the current behavior is
> > > > desirable ... and I think anybody who would have come across this, would
> > > > wish it behaved the other way. But still -- it would definitely be a
> > > > behavior change.
> > >
> > > The behavior change seems like it'd be an improvement in a vacuum,
> > > but I wonder how it would interact with catalog contents left behind
> > > by the old misbehavior. Also, would we expect pg_dump to try to do
> > > anything to clean up the mess? If so, allowing a back branch to have
> > > had more than one behavior would complicate that greatly.
> >
> > I don't think there's a problem with catalog content ?
> > I think it's fine if there's an enabled child trigger inheriting from a
> > disabled parent? This changes the initial tgenabled for new partitions.
>
> I don't think we'd need to do anything special here ... particularly
> considering the discovery that pg_dump does not preserve the disable
> status of trigger on partitions:
>
> > However...it looks like pg_dump should ALTER the child trigger state if it
> > differ from its parent. Or maybe it needs to CREATE child triggers with the
> > proper state before attaching the child table ?
>
> I guess *something* needs to be done, but I'm not clear on what it is.
> Creating the trigger on partition beforehand does not work: an error is
> raised on attach that the trigger already exists.
>
> The only way I see to do this, is to have pg_dump extract tgenabled for

I came up with this, which probably needs more than a little finesse.

--
Justin