problems with foreign keys on partitioned tables - Mailing list pgsql-hackers

Hi,

I noticed a couple of problems with foreign keys on partitioned tables.

1. Foreign keys of partitions stop working correctly after being detached
from the parent table

create table pk (a int primary key);
create table p (a int) partition by list (a);
create table p1 partition of p for values in (1) partition by list (a);
create table p11 partition of p1 for values in (1);
alter table p add foreign key (a) references pk (a);

-- these things work correctly
insert into p values (1);
ERROR:  insert or update on table "p11" violates foreign key constraint
"p_a_fkey"
DETAIL:  Key (a)=(1) is not present in table "pk".
insert into pk values (1);
insert into p values (1);
delete from pk where a = 1;
ERROR:  update or delete on table "pk" violates foreign key constraint
"p_a_fkey" on table "p"
DETAIL:  Key (a)=(1) is still referenced from table "p".

-- detach p1, which preserves the foreign key key
alter table p detach partition p1;
create table p12 partition of p1 for values in (2);

-- this part of the foreign key on p1 still works
insert into p1 values (2);
ERROR:  insert or update on table "p12" violates foreign key constraint
"p_a_fkey"
DETAIL:  Key (a)=(2) is not present in table "pk".

-- but this seems wrong
delete from pk where a = 1;
DELETE 1

-- because
select * from p1;
 a
───
 1
(1 row)

This happens because the action triggers defined on the PK relation (pk)
refers to p as the referencing relation.  On detaching p1 from p, p1's
data is no longer accessible to that trigger.  To fix this problem, we
need create action triggers on PK relation that refer to p1 when it's
detached (unless such triggers already exist which might be true in some
cases).  Attached patch 0001 shows this approach.

2. Foreign keys of a partition cannot be dropped in some cases after
detaching it from the parent.

create table p (a int references pk) partition by list (a);
create table p1 partition of p for values in (1) partition by list (a);
create table p11 partition of p1 for values in (1);
alter table p detach partition p1;

-- p1's foreign key is no longer inherited, so should be able to drop it
alter table p1 drop constraint p_a_fkey ;
ERROR:  constraint "p_a_fkey" of relation "p11" does not exist

This happens because by the time ATExecDropConstraint tries to recursively
drop the p11's inherited foreign key constraint (which is what normally
happens for inherited constraints), the latter has already been dropped by
dependency management.  I think the foreign key inheritance related code
doesn't need to add dependencies for something that inheritance recursion
can take of and I can't think of any other reason to have such
dependencies around.  I thought maybe they're needed for pg_dump to work
correctly, but apparently not so.

Interestingly, the above problem doesn't occur if the constraint is added
to partitions by inheritance recursion.

create table p (a int) partition by list (a);
create table p1 partition of p for values in (1) partition by list (a);
create table p11 partition of p1 for values in (1);
alter table p add foreign key (a) references pk (a);
alter table p detach partition p1;
alter table p1 drop constraint p_a_fkey ;
ALTER TABLE

Looking into it, that happens to work *accidentally*.

ATExecDropInherit() doesn't try to recurse, which prevents the error in
this case, because it finds that the constraint on p1 is marked NO INHERIT
(non-inheritable), which is incorrect.  The value of p1's constraint's
connoinherit (in fact, other inheritance related properties too) is
incorrect, because ATAddForeignKeyConstraint doesn't bother to set them
correctly.  This is what the inheritance properties of various copies of
'p_a_fkey' looks like in the catalog in this case:

-- case 1: foreign key added to partitions recursively
create table p (a int) partition by list (a);
create table p1 partition of p for values in (1) partition by list (a);
create table p11 partition of p1 for values in (1);
alter table p add foreign key (a) references pk (a);
select conname, conrelid::regclass, conislocal, coninhcount, connoinherit
from pg_constraint where conname like 'p%fkey%';
 conname  │ conrelid │ conislocal │ coninhcount │ connoinherit
──────────┼──────────┼────────────┼─────────────┼──────────────
 p_a_fkey │ p        │ t          │           0 │ t
 p_a_fkey │ p1       │ t          │           0 │ t
 p_a_fkey │ p11      │ t          │           0 │ t
(3 rows)

In this case, after detaching p1 from p, p1's foreign key's coninhcount
turns to -1, which is not good.

alter table p detach partition p1;
select conname, conrelid::regclass, conislocal, coninhcount, connoinherit
from pg_constraint where conname like 'p%fkey%';
 conname  │ conrelid │ conislocal │ coninhcount │ connoinherit
──────────┼──────────┼────────────┼─────────────┼──────────────
 p_a_fkey │ p        │ t          │           0 │ t
 p_a_fkey │ p11      │ t          │           0 │ t
 p_a_fkey │ p1       │ t          │          -1 │ t
(3 rows)

-- case 2: foreign keys cloned to partitions after adding partitions
create table p (a int references pk) partition by list (a);
create table p1 partition of p for values in (1) partition by list (a);
create table p11 partition of p1 for values in (1);
select conname, conrelid::regclass, conislocal, coninhcount, connoinherit
from pg_constraint where conname like 'p%fkey%';
 conname  │ conrelid │ conislocal │ coninhcount │ connoinherit
──────────┼──────────┼────────────┼─────────────┼──────────────
 p_a_fkey │ p        │ t          │           0 │ t
 p_a_fkey │ p1       │ f          │           1 │ f
 p_a_fkey │ p11      │ f          │           1 │ f
(3 rows)

Anyway, I propose we fix this by first getting rid of dependencies for
foreign key constraints and instead rely on inheritance recursion for
dropping the inherited constraints.  Before we can do that, we'll need to
consistently set the inheritance properties of foreign key constraints
correctly, that is, teach ATAddForeignKeyConstraint what
clone_fk_constraints already does correctly.  See the attached patch 0002
for that.

I'm also attaching versions of 0001 and 0002 that can be applied to PG 11.

Thanks,
Amit