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.
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() |