Self FK oddity when attaching a partition

From: Jehan-Guillaume de Rorthais <jgdr(at)dalibo(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Self FK oddity when attaching a partition
Date: 2022-06-03 13:42:32
Message-ID: 20220603154232.1715b14c@karst
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
self-fk-after-part-attach.sql application/sql 1.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Justin Pryzby 2022-06-03 13:53:54 Re: [v15 beta] pg_upgrade failed if earlier executed with -c switch
Previous Message Peter Eisentraut 2022-06-03 12:17:28 Re: Collation version tracking for macOS