Date: Fri, 3 Jun 2022 15:42:32 +0200
From: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
To: pgsql-hackers  <pgsql-hackers@postgresql.org>
Subject: Self FK oddity when attaching a partition
Message-ID: <20220603154232.1715b14c@karst>
Organization: Dalibo
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="MP_/EU_bqBvW_.4MD7uYUTHHR/p"
Archived-At: 
 <https://www.postgresql.org/message-id/20220603154232.1715b14c%40karst>
Precedence: bulk

--MP_/EU_bqBvW_.4MD7uYUTHHR/p
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

--MP_/EU_bqBvW_.4MD7uYUTHHR/p
Content-Type: application/sql
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename=self-fk-after-part-attach.sql

RFJPUCBUQUJMRSBJRiBFWElTVFMgcGFyZW50LCBjaGlsZDE7CgpDUkVBVEUgVEFCTEUgcGFyZW50
ICgKICAgIGlkIGJpZ2ludCBOT1QgTlVMTCBkZWZhdWx0IDEsCiAgICBub19wYXJ0IHNtYWxsaW50
IE5PVCBOVUxMLAogICAgaWRfYWJjIGJpZ2ludCwKICAgIGlkX2RlZiBiaWdpbnQsCiAgICBpZF9n
aGkgYmlnaW50LAogICAgaWRfamtsIGJpZ2ludCwKICAgIHRzX2NyZWF0aW9uIHRpbWVzdGFtcCB3
aXRob3V0IHRpbWUgem9uZSBkZWZhdWx0IG5vdygpICBOT1QgTlVMTCwKICAgIEZPUkVJR04gS0VZ
IChpZF9hYmMsIG5vX3BhcnQpIFJFRkVSRU5DRVMgcGFyZW50KGlkLCBub19wYXJ0KSBPTiBVUERB
VEUgUkVTVFJJQ1QgT04gREVMRVRFIFJFU1RSSUNULAogICAgRk9SRUlHTiBLRVkgKGlkX2RlZiwg
bm9fcGFydCkgUkVGRVJFTkNFUyBwYXJlbnQoaWQsIG5vX3BhcnQpIE9OIFVQREFURSBSRVNUUklD
VCBPTiBERUxFVEUgUkVTVFJJQ1QsCiAgICBGT1JFSUdOIEtFWSAoaWRfZ2hpLCBub19wYXJ0KSBS
RUZFUkVOQ0VTIHBhcmVudChpZCwgbm9fcGFydCkgT04gVVBEQVRFIFJFU1RSSUNUIE9OIERFTEVU
RSBSRVNUUklDVCwKICAgIEZPUkVJR04gS0VZIChpZF9qa2wsIG5vX3BhcnQpIFJFRkVSRU5DRVMg
cGFyZW50KGlkLCBub19wYXJ0KSBPTiBVUERBVEUgUkVTVFJJQ1QgT04gREVMRVRFIFJFU1RSSUNU
LAogICAgUFJJTUFSWSBLRVkgKGlkLCBub19wYXJ0KQopClBBUlRJVElPTiBCWSBMSVNUIChub19w
YXJ0KTsKCi0tIHAxCkNSRUFURSBUQUJMRSBjaGlsZDEgKAogICAgaWQgYmlnaW50IE5PVCBOVUxM
IGRlZmF1bHQgMSwKICAgIG5vX3BhcnQgc21hbGxpbnQgTk9UIE5VTEwsCiAgICBpZF9hYmMgYmln
aW50LAogICAgaWRfZGVmIGJpZ2ludCwKICAgIGlkX2doaSBiaWdpbnQsCiAgICBpZF9qa2wgYmln
aW50LAogICAgdHNfY3JlYXRpb24gdGltZXN0YW1wIHdpdGhvdXQgdGltZSB6b25lIGRlZmF1bHQg
bm93KCkgIE5PVCBOVUxMLAogICAgRk9SRUlHTiBLRVkgKGlkX2FiYywgbm9fcGFydCkgUkVGRVJF
TkNFUyBjaGlsZDEoaWQsIG5vX3BhcnQpIE9OIFVQREFURSBSRVNUUklDVCBPTiBERUxFVEUgUkVT
VFJJQ1QsCiAgICBGT1JFSUdOIEtFWSAoaWRfZGVmLCBub19wYXJ0KSBSRUZFUkVOQ0VTIGNoaWxk
MShpZCwgbm9fcGFydCkgT04gVVBEQVRFIFJFU1RSSUNUIE9OIERFTEVURSBSRVNUUklDVCwKICAg
IEZPUkVJR04gS0VZIChpZF9naGksIG5vX3BhcnQpIFJFRkVSRU5DRVMgY2hpbGQxKGlkLCBub19w
YXJ0KSBPTiBVUERBVEUgUkVTVFJJQ1QgT04gREVMRVRFIFJFU1RSSUNULAogICAgRk9SRUlHTiBL
RVkgKGlkX2prbCwgbm9fcGFydCkgUkVGRVJFTkNFUyBjaGlsZDEoaWQsIG5vX3BhcnQpIE9OIFVQ
REFURSBSRVNUUklDVCBPTiBERUxFVEUgUkVTVFJJQ1QsCiAgICBDT05TVFJBSU5UIGNoaWxkMSBD
SEVDSyAoKG5vX3BhcnQgPSAxKSksCiAgICBQUklNQVJZIEtFWSAoaWQsIG5vX3BhcnQpCik7CgpB
TFRFUiBUQUJMRSBPTkxZIHBhcmVudCBBVFRBQ0ggUEFSVElUSU9OIGNoaWxkMSBGT1IgVkFMVUVT
IElOICgnMScpOwoKU0VMRUNUIGNvbm5hbWUsIGNvbnBhcmVudGlkLCBjb25yZWxpZCwgY29uZnJl
bGlkCkZST00gcGdfY29uc3RyYWludApXSEVSRSBjb25uYW1lIH4gJ19ub19wYXJ0X2ZrZXknCk9S
REVSIEJZIDE7CgpcZCsgY2hpbGQxCg==

--MP_/EU_bqBvW_.4MD7uYUTHHR/p--