Hi all, While studying the issue discussed in thread "Detaching a partition with a FK on itself is not possible"[1], I stumbled across an oddity while attaching a partition having the same multiple self-FK than the parent table.
Only one of the self-FK is found as a duplicate. Find in attachment some SQL to
reproduce the scenario. Below the result of this scenario (constant from v12 to
commit 7e367924e3). Why "child1_id_abc_no_part_fkey" is found duplicated but not
the three others? From pg_constraint, only "child1_id_abc_no_part_fkey" has a
"conparentid" set.
conname | conparentid | conrelid | confrelid
-----------------------------+-------------+----------+-----------
child1_id_abc_no_part_fkey | 16901 | 16921 | 16921
child1_id_def_no_part_fkey | 0 | 16921 | 16921
child1_id_ghi_no_part_fkey | 0 | 16921 | 16921
child1_id_jkl_no_part_fkey | 0 | 16921 | 16921
parent_id_abc_no_part_fkey | 16901 | 16921 | 16894
parent_id_abc_no_part_fkey | 0 | 16894 | 16894
parent_id_abc_no_part_fkey1 | 16901 | 16894 | 16921
parent_id_def_no_part_fkey | 16906 | 16921 | 16894
parent_id_def_no_part_fkey | 0 | 16894 | 16894
parent_id_def_no_part_fkey1 | 16906 | 16894 | 16921
parent_id_ghi_no_part_fkey | 0 | 16894 | 16894
parent_id_ghi_no_part_fkey | 16911 | 16921 | 16894
parent_id_ghi_no_part_fkey1 | 16911 | 16894 | 16921
parent_id_jkl_no_part_fkey | 0 | 16894 | 16894
parent_id_jkl_no_part_fkey | 16916 | 16921 | 16894
parent_id_jkl_no_part_fkey1 | 16916 | 16894 | 16921
(16 rows)
Table "public.child1"
[...]
Partition of: parent FOR VALUES IN ('1')
Partition constraint: ((no_part IS NOT NULL) AND (no_part = '1'::smallint))
Indexes:
"child1_pkey" PRIMARY KEY, btree (id, no_part)
Check constraints:
"child1" CHECK (no_part = 1)
Foreign-key constraints:
"child1_id_def_no_part_fkey"
FOREIGN KEY (id_def, no_part)
REFERENCES child1(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT
"child1_id_ghi_no_part_fkey"
FOREIGN KEY (id_ghi, no_part)
REFERENCES child1(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT
"child1_id_jkl_no_part_fkey"
FOREIGN KEY (id_jkl, no_part)
REFERENCES child1(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT
TABLE "parent" CONSTRAINT "parent_id_abc_no_part_fkey"
FOREIGN KEY (id_abc, no_part)
REFERENCES parent(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT
TABLE "parent" CONSTRAINT "parent_id_def_no_part_fkey"
FOREIGN KEY (id_def, no_part)
REFERENCES parent(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT
TABLE "parent" CONSTRAINT "parent_id_ghi_no_part_fkey"
FOREIGN KEY (id_ghi, no_part)
REFERENCES parent(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT
TABLE "parent" CONSTRAINT "parent_id_jkl_no_part_fkey"
FOREIGN KEY (id_jkl, no_part)
REFERENCES parent(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT
Referenced by:
TABLE "child1" CONSTRAINT "child1_id_def_no_part_fkey"
FOREIGN KEY (id_def, no_part)
REFERENCES child1(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT
TABLE "child1" CONSTRAINT "child1_id_ghi_no_part_fkey"
FOREIGN KEY (id_ghi, no_part)
REFERENCES child1(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT
TABLE "child1" CONSTRAINT "child1_id_jkl_no_part_fkey"
FOREIGN KEY (id_jkl, no_part)
REFERENCES child1(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT
TABLE "parent" CONSTRAINT "parent_id_abc_no_part_fkey"
FOREIGN KEY (id_abc, no_part)
REFERENCES parent(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT
TABLE "parent" CONSTRAINT "parent_id_def_no_part_fkey"
FOREIGN KEY (id_def, no_part)
REFERENCES parent(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT
TABLE "parent" CONSTRAINT "parent_id_ghi_no_part_fkey"
FOREIGN KEY (id_ghi, no_part)
REFERENCES parent(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT
TABLE "parent" CONSTRAINT "parent_id_jkl_no_part_fkey"
FOREIGN KEY (id_jkl, no_part)
REFERENCES parent(id, no_part) ON UPDATE RESTRICT ON DELETE RESTRICT
Regards,
[1]
https://www.postgresql.org/message-id/flat/20220321113634.68c09d4b%40karst#83c0880a1b4921fcd00d836d4e6bceb3
self-fk-after-part-attach.sql
Description: application/sql
