Re: BUG #15587: Partitions with ALTER TABLE ADD CONSTRAINT

On 2019/01/16 7:55, Alvaro Herrera wrote:
> On 2019-Jan-15, Alvaro Herrera wrote:
>
>> What was on my head ("can we add ONLY to ADD FOREIGN KEY?") was the idea
>> that it'd be useful to construct the foreign keys in partitions, one by
>> one, and as a final step you construct a foreign key in the partitioned
>> table and then attach each FK in partition to the master one. Right
>> now, adding the foreign key in the parent table just creates duplicates
>> in the partitions, which is silly.
>
> I had put this aside and started reviewing Amit's patch 0002 here
> https://postgr.es/m/f2b8ead5-4131-d5a8-8016-2ea0a31250af@lab.ntt.co.jp
> when I realized that this is already implemented ... for the case where
> we attach a new partition, and the new partition already contains the
> constraint. The case of creating a constraint from scratch is just
> doing the recursion badly and not checking for pre-existing matching
> constraints, which is why it ends up with a dupe.

Yeah, that seems to be the problem.

> Fixing it is pretty
> simple -- we just need to call clone_fk_constraints() with only the
> constraint being created, and everything works correctly as far as I can
> tell.

Why not just move the code in clone_fk_constraints() that checks if the
constraint equivalent of the parent's constraint is present in the
partition and simply attach the two without creating a new copy for the
partition to a new function in tablecmds.c and call the function from both
clone_fk_constraints() and ATAddForeignKeyConstraint()? Attached is what
I'm thinking.

Thanks,
Amit