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.