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

From: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Jesper Pedersen <jesper(dot)pedersen(at)redhat(dot)com>, pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #15587: Partitions with ALTER TABLE ADD CONSTRAINT
Date: 2019-01-16 09:30:02
Message-ID: 0757116d-f94e-ba78-b6d9-17ffcf8ff5bc@lab.ntt.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Attachment Content-Type Size
check-exist-FK-constr-in-part-before-recursing.patch text/plain 11.0 KB

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Maksim Milyutin 2019-01-16 14:23:01 Re: BUG #15585: infinite DynamicSharedMemoryControlLock waiting in parallel query
Previous Message Michael Paquier 2019-01-16 01:36:33 Re: Is temporary functions feature official/supported? Found some issues with it.