A row-level trigger on a partitioned table is not created on a sub-partition created later

From: Petr Fedorov <petr(dot)fedorov(at)phystech(dot)edu>
To: pgsql-bugs(at)postgresql(dot)org
Subject: A row-level trigger on a partitioned table is not created on a sub-partition created later
Date: 2019-12-05 07:24:16
Message-ID: 6b3f0646-ba8c-b3a9-c62d-1c6651a1920f@phystech.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello,

According to the documentation
<https://www.postgresql.org/docs/11/sql-createtrigger.html>,  creating a
row-level trigger on a partitioned table will cause identical triggers
to be created in all its existing partitions; and any partitions created
or attached later will contain an identical trigger, too.

It does not happen for a sub-partition - partition of partition - when
it is created/attached later.  Steps to reproduce (11.6, Centos 7):

create table level1 (id1 integer not null, id2 integer not null, id3
integer not null) partition by list (id2);
create table level2 partition of level1 for values in (1) partition by
list (id3);
create table level3 partition of level2 for values in (1);
create or replace function trigger_func() returns trigger language
'plpgsql' as $body$ begin raise exception 'fired'; return null; end $body$;
create trigger test_trigger after insert on level1 for each row execute
procedure trigger_func();

insert into level1 values (1,1,1);  -- fails as expected due to
test_trigger();

drop table level1;
drop function trigger_func();

create table level1 (id1 integer not null, id2 integer not null, id3
integer not null) partition by list (id2);
create or replace function trigger_func() returns trigger language
'plpgsql' as $body$ begin raise exception 'fired'; return null; end $body$;
create trigger test_trigger after insert on level1 for each row execute
procedure trigger_func();

create table level2 partition of level1 for values in (1) partition by
list (id3);
create table level3 partition of level2 for values in (1);

insert into level1 values (1,1,1); -- row inserted

psql \d+ shows that level3 does not have row level trigger while level2
and level1 have.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2019-12-05 09:17:34 BUG #16150: UPDATE set NULL value in non-null columns
Previous Message Jeremy Schneider 2019-12-05 01:36:16 logical decoding bug: segfault in ReorderBufferToastReplace()