Horton

Thread

Commits

  1. Fix oversight in FindTriggerIncompatibleWithInheritance.

  1. Odd behavior of statement triggers with transition tables on partitions

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2025-07-15T08:26:03Z

    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

  2. Re: Odd behavior of statement triggers with transition tables on partitions

    Etsuro Fujita <etsuro.fujita@gmail.com> — 2025-08-08T09:19:55Z

    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