BUG #15425: DETACH/ATTACH PARTITION bug
От | PG Bug reporting form |
---|---|
Тема | BUG #15425: DETACH/ATTACH PARTITION bug |
Дата | |
Msg-id | 15425-2dbc9d2aa999f816@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #15425: DETACH/ATTACH PARTITION bug
|
Список | pgsql-bugs |
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.
В списке pgsql-bugs по дате отправления: