On Sat, Jun 9, 2018 at 3:48 AM, Alvaro Herrera <alvhe...@2ndquadrant.com> wrote: > On 2018-Jun-08, Alvaro Herrera wrote: > >> Actually, the column order doesn't matter for a trigger function, >> because these don't refer to columns by number but by name. So unless >> users write trigger functions in C and use hardcoded column numbers >> (extremely unlikely), I think this is not an issue. In other words, in >> the interesting cases the trigger functions are the same for all >> partitions -- therefore upgrading from separate per-partition triggers >> to one master trigger in the partitioned table is not going to be that >> difficult, ISTM. > > One last thing. This wording has been there since pg10; the only thing > that changed is that it now says "BEFORE ROW triggers" instead of "Row > triggers". I don't think leaving it there one more year is going to get > us too much grief, TBH.
I am worried about following scenario -- create partitioned table create table t1 (a int, b int) partition by range(a); -- create some tables which will be attached to the partitioned table in future create table t1p1 (like t1); create table t1p2 (like t1); -- those tables have indexes create index t1p1_a on t1p1(a); create index t1p2_a on t1p2(a); -- attach partitions alter table t1 attach partition t1p1 for values from (0) to (100); alter table t1 attach partition t1p2 for values from (100) to (200); -- create index on partitioned table, it doesn't create new indexes on t1p1 and t1p2 since there are already indexes on a create index t1_a on t1(a); -- create triggers, user may create different trigger functions one for each partition, unless s/he understands that the tables can share trigger functions create function trig_t1p1() returns trigger as $$ begin return new; end;$$ language plpgsql; create trigger trig_t1p1_1 before update on t1p1 execute procedure trig_t1p1(); create trigger trig_t1p1_2 before update on t1p1 execute procedure trig_t1p1(); create function trig_t1p2() returns trigger as $$ begin return new; end;$$ language plpgsql; create trigger trig_t1p2 before update on t1p2 execute procedure trig_t1p2(); When this schema gets upgraded to v12 or whatever version supports BEFORE ROW triggers on a partitioned table and the user tries to create a trigger on a partitioned table 1. somehow the code has to detect that there are already triggers on the partitions so no need to create new triggers on the partitions. I don't see how this can be done, unless the user is smart enough to use the same trigger function for all partitions. 2. user has to drop those triggers and trigger functions and create trigger on the partitioned table. May be I am underestimating users but I am sure there will be some users who will end up with scenario. -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
