A row-level trigger on a partitioned table is not created on a sub-partition created later
Petr Fedorov <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.bugs |
|---|---|
| Message-ID | <[email protected]> |
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.