Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Sat, Jun 9, 2018 at 3:48 AM, Alvaro Herrera <alvherre(at)2ndquadrant(dot)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