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

On Wed, Jul 14, 2021 at 11:02 AM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
wrote:

> On 2020-Oct-27, Justin Pryzby wrote:
>
> > I think either way could be ok - if you assume that the trigger was
> disabled
> > with ONLY, then it'd make sense to restore it with ONLY, but I think
> it's at
> > least as common to ALTER TABLE [*]. It might look weird to the user if
> they
> > used ALTER TABLE ONLY and the pg_dump output uses ALTER TABLE for that
> table,
> > and then again for all its children (or vice versa). But it's fine as
> long as
> > the state is correctly restored.
> >
> > There are serveral issues:
> > - fail to preserve childs' tgenabled in CREATE TABLE PARTITION OF;
> > - fail to preserve childs' tgenabled in pg_dump;
> > - fail to preserve childs' comments in pg_dump;
> >
> > I'm going step away from this patch at least for awhile, so I'm
> attaching my
> > latest in case it's useful.
>
> Here's a new cut of this series. I used your pg_dump patch, but I blank
> out the CREATE TRIGGER query before stashing the ALTER TRIGGER;
> otherwise the dump has an error at restore time (because the trigger is
> created again on the partition, but it already exists because it's been
> created for the parent). Also, the new query has the "OR tgenabled <>"
> test only if the table is a partition; and apply this new query only in
> 11 and 12; keep 9.x-10 unchanged, because it cannot possibly match
> anything.
>
> I tested this by creating 10k tables with one trigger each (no
> partitioned tables). Total time to dump is the same as before. I was
> concerned that because the query now has two LEFT JOINs it would be
> slower; but it seems to be only marginally so.
>
> I'm thinking to apply my patch that changes the server behavior only to
> 14 and up. I could be persuaded to backpatch all the way to 11, if
> anybody supports the idea.
>
> --
> Álvaro Herrera 39°49'30"S 73°17'W —
> https://www.EnterpriseDB.com/
> "Puedes vivir sólo una vez, pero si lo haces bien, una vez es suficiente"
>

Hi, Alvaro:
It would be nice if this is backported to PG 11+

Thanks