BUG #15425: DETACH/ATTACH PARTITION bug

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: mikemjv(at)gmail(dot)com
Subject: BUG #15425: DETACH/ATTACH PARTITION bug
Date: 2018-10-10 16:17:23
Message-ID: 15425-2dbc9d2aa999f816@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

The following bug has been logged on the website:

Bug reference: 15425
Logged by: Michael Vitale
Email address: mikemjv(at)gmail(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2018-10-10 16:47:28 Re: BUG #15425: DETACH/ATTACH PARTITION bug
Previous Message Pavan Teja 2018-10-10 13:59:57 Re: General: Unable to start the postgresql after copying postgresql.log

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2018-10-10 16:46:15 Re: Requesting advanced Group By support
Previous Message Catalin Iacob 2018-10-10 15:46:35 Re: NOTIFY and pg_notify performance when deduplicating notifications