pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Self FK oddity when attaching a partition
Date: Fri, 3 Jun 2022 15:42:32 +0200
Message-ID: <20220603154232.1715b14c@karst> (raw)

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

Message-ID: <20220603154232.1715b14c@karst>
Permalink:  ../20220603154232.1715b14c@karst/
Also on:    postgresql.org/message-id/20220603154232.1715b14c@karst

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: jgdr@dalibo.com
  Subject: Re: Self FK oddity when attaching a partition
  In-Reply-To: <20220603154232.1715b14c@karst>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox