Odd behavior of statement triggers with transition tables on partitions

From: Etsuro Fujita <etsuro(dot)fujita(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Odd behavior of statement triggers with transition tables on partitions
Date: 2025-07-15 08:26:03
Message-ID: CAPmGK167mXzwzzmJ_0YZ3EZrbwiCxtM1vogH_8drqsE6PtxRYw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
fix-oversight-in-FindTriggerIncompatibleWithInheritance.patch application/octet-stream 2.6 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message wenhui qiu 2025-07-15 08:41:56 Re: Requested WAL segment xxx has already been removed
Previous Message Jelte Fennema-Nio 2025-07-15 08:16:53 Re: Huge commitfest app update upcoming: Tags, Draft CF, Help page, and automated commitfest creat/open/close