Re: BUG #15425: DETACH/ATTACH PARTITION bug

On 2018-Oct-10, Alvaro Herrera wrote:

> On 2018-Oct-10, PG Bug reporting form wrote:
>
> > After I DETACH a partition, and then try to ATTACH it again, I get errors:
> > ERROR: duplicate key value violates unique constraint
> > "pg_constraint_conrelid_contypid_conname_index"
> > DETAIL: Key (conrelid, contypid, conname)=(26702, 0,
> > test_result_asset_id_fkey) already exists.
> >
> > It looks like it is trying to add the foreign key again.
>
> Thanks. Reproduced with
>
> create table main (a int primary key);
> create table part (a int references main) partition by range (a);
> create table part1 partition of part for values from (1) to (100);
> alter table part detach partition part1;
> alter table part attach partition part1 for values from (1) to (100);

There are two bugs here, actually. One is that detaching the partition
does not make the FK independent, so if you later drop the partitioned
table, the FK in the partition goes away. The second is that attaching
a partition does not first see whether a convenient FK is defined in the
partition, so we would create a duplicate one.

AFAICS the attached fixes both things. Could you please verify that it
fixes your scenario too?

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