Odd behavior of statement triggers with transition tables on partitions
Yet another thing I noticed about transition tables is $SUBJECT:
create table parent (a text, b int) partition by list (a);
create table child partition of parent for values in ('AAA');
create function dump_insert() returns trigger language plpgsql as
$$
begin
raise notice 'trigger = %, new table = %',
TG_NAME,
(select string_agg(new_table::text, ', ' order by a)
from new_table);
return null;
end;
$$;
create trigger child_insert_trig
after insert on child referencing new table as new_table
for each statement execute procedure dump_insert();
alter table parent detach partition child;
alter table parent attach partition child for values in ('AAA');
ERROR: trigger "child_insert_trig" prevents table "child" from
becoming a partition
DETAIL: ROW triggers with transition tables are not supported on partitions.
Reattaching the partition fails, which is surprising. The DETAIL
message is surprising too, as the trigger created on the partition is
a statement trigger, not a row trigger.
I think the root cause of this is that
FindTriggerIncompatibleWithInheritance(), which is called from
ATExecAttachPartition() (or ATExecAddInherit()), fails to check that
such an incompatible trigger is a row trigger, erroneously detecting a
statement trigger. Attached is a fix for that.
Best regards,
Etsuro Fujita
Attachments:
fix-oversight-in-FindTriggerIncompatibleWithInheritance.patchapplication/octet-stream; name=fix-oversight-in-FindTriggerIncompatibleWithInheritance.patchDownload
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index ee5f27d92e4..f653766f87a 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -2284,6 +2284,8 @@ FindTriggerIncompatibleWithInheritance(TriggerDesc *trigdesc)
{
Trigger *trigger = &trigdesc->triggers[i];
+ if (!TRIGGER_FOR_ROW(trigger->tgtype))
+ continue;
if (trigger->tgoldtable != NULL || trigger->tgnewtable != NULL)
return trigger->tgname;
}
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 872b9100e1a..a71b770b537 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -2769,6 +2769,9 @@ NOTICE: trigger = child3_delete_trig, old table = (42,CCC)
-- copy into parent sees parent-format tuples
copy parent (a, b) from stdin;
NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,42)
+-- check detach/reattach behavior
+alter table parent detach partition child1;
+alter table parent attach partition child1 for values in ('AAA');
-- DML affecting parent sees tuples collected from children even if
-- there is no transition table trigger on the children
drop trigger child1_insert_trig on child1;
@@ -2966,6 +2969,9 @@ NOTICE: trigger = parent_insert_trig, new table = (AAA,42), (BBB,42), (CCC,42)
create index on parent(b);
copy parent (a, b) from stdin;
NOTICE: trigger = parent_insert_trig, new table = (DDD,42)
+-- check disinherit/reinherit behavior
+alter table child1 no inherit parent;
+alter table child1 inherit parent;
-- DML affecting parent sees tuples collected from children even if
-- there is no transition table trigger on the children
drop trigger child1_insert_trig on child1;
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index d674b25c83b..61fdabfdea4 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -1935,6 +1935,10 @@ BBB 42
CCC 42
\.
+-- check detach/reattach behavior
+alter table parent detach partition child1;
+alter table parent attach partition child1 for values in ('AAA');
+
-- DML affecting parent sees tuples collected from children even if
-- there is no transition table trigger on the children
drop trigger child1_insert_trig on child1;
@@ -2154,6 +2158,10 @@ copy parent (a, b) from stdin;
DDD 42
\.
+-- check disinherit/reinherit behavior
+alter table child1 no inherit parent;
+alter table child1 inherit parent;
+
-- DML affecting parent sees tuples collected from children even if
-- there is no transition table trigger on the children
drop trigger child1_insert_trig on child1;
On Tue, Jul 15, 2025 at 5:26 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Yet another thing I noticed about transition tables is $SUBJECT:
create table parent (a text, b int) partition by list (a);
create table child partition of parent for values in ('AAA');
create function dump_insert() returns trigger language plpgsql as
$$
begin
raise notice 'trigger = %, new table = %',
TG_NAME,
(select string_agg(new_table::text, ', ' order by a)
from new_table);
return null;
end;
$$;
create trigger child_insert_trig
after insert on child referencing new table as new_table
for each statement execute procedure dump_insert();
alter table parent detach partition child;
alter table parent attach partition child for values in ('AAA');
ERROR: trigger "child_insert_trig" prevents table "child" from
becoming a partition
DETAIL: ROW triggers with transition tables are not supported on partitions.Reattaching the partition fails, which is surprising. The DETAIL
message is surprising too, as the trigger created on the partition is
a statement trigger, not a row trigger.I think the root cause of this is that
FindTriggerIncompatibleWithInheritance(), which is called from
ATExecAttachPartition() (or ATExecAddInherit()), fails to check that
such an incompatible trigger is a row trigger, erroneously detecting a
statement trigger. Attached is a fix for that.
As there seemed to be no objections, I pushed/back-patched this as well.
Best regards,
Etsuro Fujita