Thread: BUG #15425: DETACH/ATTACH PARTITION bug

The following bug has been logged on the website:

Bug reference:      15425
Logged by:          Michael Vitale
Email address:      mikemjv@gmail.com
PostgreSQL version: 11beta4
Operating system:   CentOS
Description:

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.

So then I try to delete that foreign key before trying to attach it again,
but now I get another error:
ERROR:  cannot drop inherited constraint "test_result_asset_id_fkey" of
relation "test_result_cbsystem_0001_0050_monthly_2018_09"

But why would I get that last error since my table is detached at that point
as shown by \d+ tablename

And obviously I cannot use inherit/disinherit logic against these tables
since they were created as declarative partitions.
Maybe this wasn't tested thoroughly in the FK addition to partitioned
tables? 
I find that hard to believe that I would have to cascade down and drop this
foreign key for all attached partitions before being able to add one
partition back in via ATTACH (edited)

Thinking more about it, it's as if the intention was to create the indexes
and foreign keys for new partitions being attached, not ones that were
detached for maintenance reasons, vacuum full, etc., that still have the
foreign keys and indexes defined no these detached partitions.

I deleted that one FK from the parent and then tried again to attach the
partition.  I got the same error but for the next foreign key on that
table.

Finally, I deleted all the foreign keys from the parent table, and then was
allowed to ATTACH the detached partition back in.  

This has to be a bug because nobody is gonna want to take the performance
hit of recreating all the foreign keys on partitioned tables whenever a
partition is detached and attached again.

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);

Looking into it.

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

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

Another version.  I realized that attaching a partitioned partition had
further trouble, because the recursion at each step would consider all
FKs instead of only the FKs that had been cloned.  So I had to split out
the recursive step of the cloning.  Now that works fine.

In order to make this work, I made two little change to struct
ForeignKeyCacheInfo: first, the constraint OID was added.  Second, I
made RelationGetFKeyList() return a nonempty list for partitioned
tables, which it didn't before (because of the optimization that
presupposes no triggers means no FKs, which is not true for partitioned
tables).  As far as I can see, this has no effect on how the planner
uses this function.  (This stuff could be done with repeated scans of
pg_constraint, but it seems much simpler this way.)

Michael sent me his test case offlist, and I verified that it works
correctly with this patch.

Unless there are objections, I intend to get this pushed tomorrow.

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

Another version.  I realized that attaching a partitioned partition had
further trouble, because the recursion at each step would consider all
FKs instead of only the FKs that had been cloned.  So I had to split out
the recursive step of the cloning.  Now that works fine.

In order to make this work, I made two little change to struct
ForeignKeyCacheInfo: first, the constraint OID was added.  Second, I
made RelationGetFKeyList() return a nonempty list for partitioned
tables, which it didn't before (because of the optimization that
presupposes no triggers means no FKs, which is not true for partitioned
tables).  As far as I can see, this has no effect on how the planner
uses this function.  (This stuff could be done with repeated scans of
pg_constraint, but it seems much simpler this way.)

Michael sent me his test case offlist, and I verified that it works
correctly with this patch.

Unless there are objections, I intend to get this pushed tomorrow.

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

Pushed, after some further refinement of the test case so that it'd
verify a few more corner case situations.

Thanks Michael.

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

Pushed, after some further refinement of the test case so that it'd
verify a few more corner case situations.

Thanks Michael.

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