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,
