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,
Attachment | Content-Type | Size |
---|---|---|
self-fk-after-part-attach.sql | application/sql | 1.6 KB |
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 |