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

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

The only issue is that clone_fk_constraints is a static function in
pg_constraint.c, so I'd have to export it for use in tablecmds.c ... or
I could just apply patch 0002 that I posted here
https://www.postgresql.org/message-id/20181130191216.5xcxcsx3ascgqayv@alvherre.pgsql
which takes care precisely of moving that function to tablecmds.c (with
a better name, too).

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services