[Jehan-Guillaume de Rorthais <jgdr@dalibo.com>]

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#83c0880a1b4921fcd00d836d4...

Attachments:

  [application/sql] self-fk-after-part-attach.sql (1.6K, ../20220603154232.1715b14c@karst/2-self-fk-after-part-attach.sql)
  download