[BUG] Fix DETACH with FK pointing to a partitioned table fails
От | Jehan-Guillaume de Rorthais |
---|---|
Тема | [BUG] Fix DETACH with FK pointing to a partitioned table fails |
Дата | |
Msg-id | 20230705233028.2f554f73@karst обсуждение исходный текст |
Ответы |
Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails
|
Список | pgsql-hackers |
Hi, (patch proposal below). Consider a table with a FK pointing to a partitioned table. CREATE TABLE p ( id bigint PRIMARY KEY ) PARTITION BY list (id); CREATE TABLE p_1 PARTITION OF p FOR VALUES IN (1); CREATE TABLE r_1 ( id bigint PRIMARY KEY, p_id bigint NOT NULL, FOREIGN KEY (p_id) REFERENCES p (id) ); Now, attach this table "refg_1" as partition of another one having the same FK: CREATE TABLE r ( id bigint PRIMARY KEY, p_id bigint NOT NULL, FOREIGN KEY (p_id) REFERENCES p (id) ) PARTITION BY list (id); ALTER TABLE r ATTACH PARTITION r_1 FOR VALUES IN (1); The old sub-FKs (below 18289) created in this table to enforce the action triggers on referenced partitions are not deleted when the table becomes a partition. Because of this, we have additional and useless triggers on the referenced partitions and we can not DETACH this partition on the referencing side anymore: => ALTER TABLE r DETACH PARTITION r_1; ERROR: could not find ON INSERT check triggers of foreign key constraint 18289 => SELECT c.oid, conparentid, conrelid::regclass, confrelid::regclass, t.tgfoid::regproc FROM pg_constraint c JOIN pg_trigger t ON t.tgconstraint = c.oid WHERE confrelid::regclass = 'p_1'::regclass; oid │ conparentid │ conrelid │ confrelid │ tgfoid ───────┼─────────────┼──────────┼───────────┼──────────────────────── 18289 │ 18286 │ r_1 │ p_1 │ "RI_FKey_noaction_del" 18289 │ 18286 │ r_1 │ p_1 │ "RI_FKey_noaction_upd" 18302 │ 18299 │ r │ p_1 │ "RI_FKey_noaction_del" 18302 │ 18299 │ r │ p_1 │ "RI_FKey_noaction_upd" (4 rows) The legitimate constraint and triggers here are 18302. The old sub-FK 18289 having 18286 as parent should have gone during the ATTACH PARTITION. Please, find in attachment a patch dropping old "sub-FK" during the ATTACH PARTITION command and adding a regression test about it. At the very least, it help understanding the problem and sketch a possible solution. Regards,
Вложения
В списке pgsql-hackers по дате отправления: