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

CREATE TABLE t(i int) PARTITION BY RANGE(i);
CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (10);
CREATE OR REPLACE FUNCTION tgf() RETURNS trigger LANGUAGE plpgsql AS $$ begin raise exception 'except'; end $$;
CREATE TRIGGER tg AFTER INSERT ON t FOR EACH ROW EXECUTE FUNCTION tgf();
ALTER TABLE t1 DISABLE TRIGGER tg;
INSERT INTO t VALUES(1); -- inserts when trigger is disabled: good
ALTER TABLE t DISABLE TRIGGER tg;
CREATE TABLE t2 PARTITION OF t FOR VALUES FROM (10) TO (20);

postgres=# SELECT tgrelid::regclass, tgenabled FROM pg_trigger WHERE tgrelid::regclass::text IN ('t1','t2');
 tgrelid | tgenabled 
---------+-----------
 t1      | D
 t2      | O
(2 rows)

I consider this a bug,but CreateTrigStmt doesn't have any "enabled" member
(since it's impossible to CREATE TRIGGER .. DISABLED), so I'm not sure where
the fix should be.

I'm hoping that Alvaro will comment on this.

On Wed, Sep 30, 2020 at 05:34:50PM -0500, Justin Pryzby wrote:
> CREATE TABLE t(i int) PARTITION BY RANGE(i);
> CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (10);
> CREATE OR REPLACE FUNCTION tgf() RETURNS trigger LANGUAGE plpgsql AS $$ begin raise exception 'except'; end $$;
> CREATE TRIGGER tg AFTER INSERT ON t FOR EACH ROW EXECUTE FUNCTION tgf();
> ALTER TABLE t1 DISABLE TRIGGER tg;
> INSERT INTO t VALUES(1); -- inserts when trigger is disabled: good
> ALTER TABLE t DISABLE TRIGGER tg;
> CREATE TABLE t2 PARTITION OF t FOR VALUES FROM (10) TO (20);
> 
> postgres=# SELECT tgrelid::regclass, tgenabled FROM pg_trigger WHERE tgrelid::regclass::text IN ('t1','t2');
>  tgrelid | tgenabled 
> ---------+-----------
>  t1      | D
>  t2      | O
> (2 rows)
> 
> I consider this a bug,but CreateTrigStmt doesn't have any "enabled" member
> (since it's impossible to CREATE TRIGGER .. DISABLED), so I'm not sure where
> the fix should be.

On 2020-Sep-30, Justin Pryzby wrote:

> postgres=# SELECT tgrelid::regclass, tgenabled FROM pg_trigger WHERE tgrelid::regclass::text IN ('t1','t2');
>  tgrelid | tgenabled 
> ---------+-----------
>  t1      | D
>  t2      | O
> (2 rows)
> 
> I consider this a bug,

Yeah.

> but CreateTrigStmt doesn't have any "enabled" member
> (since it's impossible to CREATE TRIGGER .. DISABLED), so I'm not sure where
> the fix should be.

I suggest we add a new function, as in the attached.  It's important to
keep the ABI of CreateTrigger unchanged, for the sake of
backpatchability, but ISTM it's equally important to keep its API
unchanged, because outside callers such as ProcessUtility_slow does not
have to care about the new trigger's enabled state.

Same, with a little test.

I also just noticed that ALTER TABLE ONLY recurses to children, which it
should not.

On 2020-Oct-16, Alvaro Herrera wrote:

> I also just noticed that ALTER TABLE ONLY recurses to children, which it
> should not.

Apparently I wrote (bogus) bespoke code to handle recursion in
EnableDisableTrigger instead of using ATSimpleRecursion.  This patch
seems to fix this problem.

On 2020-Sep-30, Justin Pryzby wrote:

> CREATE TABLE t(i int) PARTITION BY RANGE(i);
> CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (10);
> CREATE OR REPLACE FUNCTION tgf() RETURNS trigger LANGUAGE plpgsql AS $$ begin raise exception 'except'; end $$;
> CREATE TRIGGER tg AFTER INSERT ON t FOR EACH ROW EXECUTE FUNCTION tgf();
> ALTER TABLE t1 DISABLE TRIGGER tg;
> INSERT INTO t VALUES(1); -- inserts when trigger is disabled: good
> ALTER TABLE t DISABLE TRIGGER tg;
> CREATE TABLE t2 PARTITION OF t FOR VALUES FROM (10) TO (20);
> 
> postgres=# SELECT tgrelid::regclass, tgenabled FROM pg_trigger WHERE tgrelid::regclass::text IN ('t1','t2');
>  tgrelid | tgenabled 
> ---------+-----------
>  t1      | D
>  t2      | O
> (2 rows)
> 
> I consider this a bug,but CreateTrigStmt doesn't have any "enabled" member
> (since it's impossible to CREATE TRIGGER .. DISABLED), so I'm not sure where
> the fix should be.

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.

This is a judgement call, and mine says to backpatch, but I've been
wrong on that.

On Tue, Oct 20, 2020 at 04:04:20PM -0300, Alvaro Herrera wrote:
> On 2020-Sep-30, Justin Pryzby wrote:
> 
> > CREATE TABLE t(i int) PARTITION BY RANGE(i);
> > CREATE TABLE t1 PARTITION OF t FOR VALUES FROM (1) TO (10);
> > CREATE OR REPLACE FUNCTION tgf() RETURNS trigger LANGUAGE plpgsql AS $$ begin raise exception 'except'; end $$;
> > CREATE TRIGGER tg AFTER INSERT ON t FOR EACH ROW EXECUTE FUNCTION tgf();
> > ALTER TABLE t1 DISABLE TRIGGER tg;
> > INSERT INTO t VALUES(1); -- inserts when trigger is disabled: good
> > ALTER TABLE t DISABLE TRIGGER tg;
> > CREATE TABLE t2 PARTITION OF t FOR VALUES FROM (10) TO (20);
> > 
> > postgres=# SELECT tgrelid::regclass, tgenabled FROM pg_trigger WHERE tgrelid::regclass::text IN ('t1','t2');
> >  tgrelid | tgenabled 
> > ---------+-----------
> >  t1      | D
> >  t2      | O
> > (2 rows)
> > 
> > I consider this a bug,but CreateTrigStmt doesn't have any "enabled" member
> > (since it's impossible to CREATE TRIGGER .. DISABLED), so I'm not sure where
> > the fix should be.
> 
> 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.

+0.8 to backpatch.  To v13 if not further.

We don't normally disable triggers, otherwise I would say +1.

For context, I ran into this issue while migrating a customer to a new server
using pg_restore and a custom backup script which loops around pg_dump, and
handles partitioned tables differently depending if they're recent or historic.

Our backup job works well, but is technically a bit of a hack.  It doesn't do
the right thing (causes sql errors and pg_restore warnings) for inherited
indexes and, apparently, triggers.  Disabling the trigger was my 4th attempt to
handle an error restoring a specific table (mismatched column type between
parent dump and child dumped several days earlier).  I eventually (5th
or 6th attempt) dropped the parent trigger, created the child tables using
--section=pre-data, ALTERed a column to match, and then ran post-data and
attached it.

-- 
Justin

Alvaro Herrera <alvherre@alvh.no-ip.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.

            regards, tom lane

On 2020-Oct-16, Alvaro Herrera wrote:

> On 2020-Oct-16, Alvaro Herrera wrote:
> 
> > I also just noticed that ALTER TABLE ONLY recurses to children, which it
> > should not.
> 
> Apparently I wrote (bogus) bespoke code to handle recursion in
> EnableDisableTrigger instead of using ATSimpleRecursion.  This patch
> seems to fix this problem.

... but it affects legacy inheritance, which would be undesirable
because it has never recursed for that case.  So it needs to have a
relkind check here and only recurse if it's a new-style partitioned
table:

> diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
> index 511f015a86..c8d6f78da2 100644
> --- a/src/backend/commands/tablecmds.c
> +++ b/src/backend/commands/tablecmds.c
> @@ -4321,6 +4321,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
>          case AT_DisableTrigAll:
>          case AT_DisableTrigUser:
>              ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
> +            ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context);
>              pass = AT_PASS_MISC;
>              break;
>          case AT_EnableRule:        /* ENABLE/DISABLE RULE variants */

I'll add tests for both cases and push to all branches 11+.

On Tue, Oct 20, 2020 at 03:56:30PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.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.

The old catalog state is still possible - it's what you'd get if you did
CREATE TABLE child PARTITION OF..; ALTER TABLE child DISABLE TRIGGER.

I don't think pg_dump needs to do anyting special, since the restore will now
do what's wanted without added commands.  Note that pg_dump switched from
"PARTITION OF" to "ATTACH PARTITION" at commit 33a53130a.  This would handles
both on the server side.

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 ?

-- 
Justin

On 2020-Oct-20, Alvaro Herrera wrote:

> > diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
> > index 511f015a86..c8d6f78da2 100644
> > --- a/src/backend/commands/tablecmds.c
> > +++ b/src/backend/commands/tablecmds.c
> > @@ -4321,6 +4321,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
> >          case AT_DisableTrigAll:
> >          case AT_DisableTrigUser:
> >              ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
> > +            ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode, context);
> >              pass = AT_PASS_MISC;
> >              break;
> >          case AT_EnableRule:        /* ENABLE/DISABLE RULE variants */
> 
> I'll add tests for both cases and push to all branches 11+.

Pushed this part.

On 2020-Oct-20, Justin Pryzby wrote:

> On Tue, Oct 20, 2020 at 03:56:30PM -0400, Tom Lane wrote:
> > Alvaro Herrera <alvherre@alvh.no-ip.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
all child triggers that doesn't have the same tgenabled as the parent,
and append ALTER .. DISABLE commands for each one to the parent table
trigger creation command.  So pg_dump.c's getTriggers would have to
obtain the list of altered child triggers, and then dumpTrigger would
have to append the ALTER TABLE ONLY <partition> .. ENABLE/DISABLE
command for that particular trigger.

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@alvh.no-ip.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

On 2020-Oct-21, Justin Pryzby wrote:

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

Hmm, there are two important changes needed on this: 1) it must not emit
CREATE lines for the child triggers; only the ALTER TABLE ONLY
<partition> lines to set disable state on the partition are needed.  2)
tgparentid does not exist prior to pg13, so you need some additional
trick to cover that case.

Also, I think the multipartition case is broken: if grandparent has
trigger enabled, parent has trigger disabled and child trigger set to
always, is that dumped correctly?  I think the right way to do this is
change only the partitions that differ from the topmost partitioned
table -- not their immediate parents; and use ONLY to ensure they don't
affect downstream children.

Change 1 also means that the test with the "this shouldn't ever get
emitted" comment remains unchanged.

I'm not sure how to tackle change 2.  You need to search pg_depend for
entries with classid=pg_trigger and refclass=pg_trigger ... (commit
1fa846f1c9af might give some clue)

On Wed, Oct 21, 2020 at 02:02:37PM -0300, Alvaro Herrera wrote:
> On 2020-Oct-21, Justin Pryzby wrote:
> 
> > I came up with this, which probably needs more than a little finesse.
> 
> Hmm, there are two important changes needed on this: 1) it must not emit
> CREATE lines for the child triggers; only the ALTER TABLE ONLY
> <partition> lines to set disable state on the partition are needed.  2)
> tgparentid does not exist prior to pg13, so you need some additional
> trick to cover that case.

Thanks for looking.

> Also, I think the multipartition case is broken: if grandparent has
> trigger enabled, parent has trigger disabled and child trigger set to
> always, is that dumped correctly?  I think the right way to do this is
> change only the partitions that differ from the topmost partitioned
> table -- not their immediate parents; and use ONLY to ensure they don't
> affect downstream children.

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.

-- 
Justin

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"

Looking over the tests added by your (Justin's) patch, there was a
problem checking for non-existance of the CREATE TRIGGER line: it
doesn't work to use "like => {}" (empty), you need to use
"unlike => { everything }".  So your test was not catching the fact that
we were, in fact, emitting the undesirable line.  I have fixed that
here, and verified that the tests are doing what we wanted them to do.

I also verified the new test in 0001.  I was about to add a test for the
legacy inheritance case, but I eventually realized that it was already
being tested by the lines I added in commit bbb927b4db9b.


I have one vote for backpatching 0001 to pg11.  Any others?  To be
clear: the issue is that if a partitioned table has a disabled trigger,
and a partition is created, the trigger is created as enabled in the
partition.  With this patch, the trigger would be created as disabled.
Do people think that that behavior change would be unwelcome?

-- 
Álvaro Herrera              Valdivia, Chile  —  https://www.EnterpriseDB.com/
"El Maquinismo fue proscrito so pena de cosquilleo hasta la muerte"
(Ijon Tichy en Viajes, Stanislaw Lem)

Thanks for handling this.

I think there's still an issue that comments on child triggers aren't
preserved, right ?

-- 
Justin

Justin Pryzby <pryzby@telsasoft.com> writes:
> I think there's still an issue that comments on child triggers aren't
> preserved, right ?

Do we care?  That seems like messing with a system-internal object.
In general we won't promise to preserve the results of doing so.

            regards, tom lane

On Fri, Jul 16, 2021 at 02:15:26PM -0400, Tom Lane wrote:
> Justin Pryzby <pryzby@telsasoft.com> writes:
> > I think there's still an issue that comments on child triggers aren't
> > preserved, right ?
> 
> Do we care?  That seems like messing with a system-internal object.
> In general we won't promise to preserve the results of doing so.

It's fine if that's the conclusion.

Back in October, that seemed like one too many things misbehaving, which led me
to walk away from the patch and re-orient.

I was going re-check the behavior, but instead hit another bug:

CREATE TABLE p(i int) PARTITION BY RANGE(i);
CREATE TABLE p1 PARTITION OF p FOR VALUES FROM (1)TO(2);
CREATE FUNCTION foo() returns trigger LANGUAGE plpgsql AS $$begin end$$;
CREATE TRIGGER x AFTER DELETE ON p1 EXECUTE FUNCTION foo();
CREATE TRIGGER x AFTER DELETE ON p EXECUTE FUNCTION foo();
\d p1
2021-07-16 14:30:12.371 CDT client backend[6252] psql ERROR:  more than one row returned by a subquery used as an
expression
2021-07-16 14:30:12.371 CDT client backend[6252] psql STATEMENT:  SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid,
true),t.tgenabled, t.tgisinternal, (SELECT (NULLIF(a.relid, t.tgrelid))::pg_catalog.regclass FROM pg_catalog.pg_trigger
ASu,       pg_catalog.pg_partition_ancestors(t.tgrelid) AS a WHERE u.tgname = t.tgname AND u.tgrelid = a.relid
ANDu.tgparentid = 0) AS parent
 
        FROM pg_catalog.pg_trigger t
        WHERE t.tgrelid = '37718' AND (NOT t.tgisinternal OR (t.tgisinternal AND t.tgenabled = 'D') 
            OR EXISTS (SELECT 1 FROM pg_catalog.pg_depend WHERE objid = t.oid 
                AND refclassid = 'pg_catalog.pg_trigger'::pg_catalog.regclass))
        ORDER BY 1;
ERROR:  more than one row returned by a subquery used as an expression

The tgenabled issue was a contributing factor which led us to stop using
inherited triggers, so I'm not very motivated to dig into it.

-- 
Justin

I noticed that in pg_dump --clean, we were still emitting DROP lines for
the triggers in the partitions, which raises errors; so I emptied that
too.

On 2021-Jul-14, Alvaro Herrera wrote:

> Looking over the tests added by your (Justin's) patch, there was a
> problem checking for non-existance of the CREATE TRIGGER line: it
> doesn't work to use "like => {}" (empty), you need to use
> "unlike => { everything }".  So your test was not catching the fact that
> we were, in fact, emitting the undesirable line.

Actually, that is wrong; unlike is there just to be able to subtract
dumps from the set that is going to be searched for the regexp, for when
you want to use one of the hashes (%full_runs) but ignore a few of
those.

> I have fixed that here, and verified that the tests are doing what we
> wanted them to do.

... and I added some more tests, for pg_upgrade.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"La espina, desde que nace, ya pincha" (Proverbio africano)

On 2021-Jul-16, Justin Pryzby wrote:

> CREATE TABLE p(i int) PARTITION BY RANGE(i);
> CREATE TABLE p1 PARTITION OF p FOR VALUES FROM (1)TO(2);
> CREATE FUNCTION foo() returns trigger LANGUAGE plpgsql AS $$begin end$$;
> CREATE TRIGGER x AFTER DELETE ON p1 EXECUTE FUNCTION foo();
> CREATE TRIGGER x AFTER DELETE ON p EXECUTE FUNCTION foo();

Hmm, interesting -- those statement triggers are not cloned, so what is
going on here is just that the psql query to show them is tripping on
its shoelaces ... I'll try to find a fix.

I *think* the problem is that the query matches triggers by name and
parent/child relationship; we're missing to ignore triggers by tgtype.
It's not great design that tgtype is a bitmask of unrelated flags ...

-- 
Álvaro Herrera              Valdivia, Chile  —  https://www.EnterpriseDB.com/

On Fri, Jul 16, 2021 at 06:01:12PM -0400, Alvaro Herrera wrote:
> On 2021-Jul-16, Justin Pryzby wrote:
> > CREATE TABLE p(i int) PARTITION BY RANGE(i);
> > CREATE TABLE p1 PARTITION OF p FOR VALUES FROM (1)TO(2);
> > CREATE FUNCTION foo() returns trigger LANGUAGE plpgsql AS $$begin end$$;
> > CREATE TRIGGER x AFTER DELETE ON p1 EXECUTE FUNCTION foo();
> > CREATE TRIGGER x AFTER DELETE ON p EXECUTE FUNCTION foo();
> 
> Hmm, interesting -- those statement triggers are not cloned, so what is
> going on here is just that the psql query to show them is tripping on
> its shoelaces ... I'll try to find a fix.
> 
> I *think* the problem is that the query matches triggers by name and
> parent/child relationship; we're missing to ignore triggers by tgtype.
> It's not great design that tgtype is a bitmask of unrelated flags ...

I see it's the subquery Amit wrote and proposed here:
https://www.postgresql.org/message-id/CA+HiwqEiMe0tCOoPOwjQrdH5fxnZccMR7oeW=f9FmgszJQbgFg@mail.gmail.com

.. and I realize that I've accidentally succeeded in breaking what I first
attempted to break 15 months ago:

On Mon, Apr 20, 2020 at 02:57:40PM -0500, Justin Pryzby wrote:
> I'm happy to see that this doesn't require a recursive cte, at least.
> I was trying to think how to break it by returning multiple results or results
> out of order, but I think that can't happen.

If you assume that pg_partition_ancestors returns its results in order, I think
you can fix it by adding LIMIT 1.  Otherwise I think you need a recursive CTE,
as I'd feared.

Note also that I'd sent a patch to add newlines, to make psql -E look pretty.
v6-0001-fixups-c33869cc3bfc42bce822251f2fa1a2a346f86cc5.patch 

-- 
Justin