From 8eef33154a7faea8aab7a1b423abb44e35fbfdac Mon Sep 17 00:00:00 2001
From: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
Date: Sat, 1 Oct 2022 00:00:28 +0200
Subject: [PATCH 3/3] Add regression tests about self-FK with partitions

---
 src/test/regress/expected/constraints.out | 37 +++++++++++++++++++++++
 src/test/regress/sql/constraints.sql      | 31 +++++++++++++++++++
 2 files changed, 68 insertions(+)

diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index e6f6602d95..49aa659330 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -626,6 +626,43 @@ SELECT conname FROM pg_constraint WHERE conrelid = 'parted_fk_naming_1'::regclas
 (1 row)
 
 DROP TABLE parted_fk_naming;
+--
+-- Test self-referencing foreign key with partition.
+-- This should create only one fk constraint per partition
+--
+CREATE TABLE parted_self_fk (
+    id bigint NOT NULL PRIMARY KEY,
+    id_abc bigint,
+    FOREIGN KEY (id_abc) REFERENCES parted_self_fk(id)
+)
+PARTITION BY RANGE (id);
+-- test with an existing table attached
+CREATE TABLE part1_self_fk (
+    id bigint NOT NULL PRIMARY KEY,
+    id_abc bigint
+);
+ALTER TABLE parted_self_fk ATTACH PARTITION part1_self_fk FOR VALUES FROM (0) TO (10);
+-- test with a newly created partition
+CREATE TABLE part2_self_fk PARTITION OF parted_self_fk FOR VALUES FROM (10) TO (20);
+SELECT cr.relname, co.conname, co.contype, p.conname AS conparentrelname,
+       cf.relname AS conforeignrelname
+FROM pg_catalog.pg_constraint co
+JOIN pg_catalog.pg_class cr ON cr.oid = co.conrelid
+LEFT JOIN pg_catalog.pg_class cf ON cf.oid = co.confrelid
+LEFT JOIN pg_catalog.pg_constraint p ON p.oid = co.conparentid
+WHERE cr.relname IN ('parted_self_fk', 'part1_self_fk', 'part2_self_fk')
+ORDER BY cr.relname, co.conname, p.conname;
+    relname     |          conname           | contype |      conparentrelname      | conforeignrelname 
+----------------+----------------------------+---------+----------------------------+-------------------
+ part1_self_fk  | part1_self_fk_pkey         | p       | parted_self_fk_pkey        | 
+ part1_self_fk  | parted_self_fk_id_abc_fkey | f       | parted_self_fk_id_abc_fkey | parted_self_fk
+ part2_self_fk  | part2_self_fk_pkey         | p       | parted_self_fk_pkey        | 
+ part2_self_fk  | parted_self_fk_id_abc_fkey | f       | parted_self_fk_id_abc_fkey | parted_self_fk
+ parted_self_fk | parted_self_fk_id_abc_fkey | f       |                            | parted_self_fk
+ parted_self_fk | parted_self_fk_pkey        | p       |                            | 
+(6 rows)
+
+DROP TABLE parted_self_fk;
 -- test a HOT update that invalidates the conflicting tuple.
 -- the trigger should still fire and catch the violation
 BEGIN;
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 5ffcd4ffc7..bdfc9f1455 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -449,6 +449,37 @@ ALTER TABLE parted_fk_naming ATTACH PARTITION parted_fk_naming_1 FOR VALUES IN (
 SELECT conname FROM pg_constraint WHERE conrelid = 'parted_fk_naming_1'::regclass AND contype = 'f';
 DROP TABLE parted_fk_naming;
 
+--
+-- Test self-referencing foreign key with partition.
+-- This should create only one fk constraint per partition
+--
+CREATE TABLE parted_self_fk (
+    id bigint NOT NULL PRIMARY KEY,
+    id_abc bigint,
+    FOREIGN KEY (id_abc) REFERENCES parted_self_fk(id)
+)
+PARTITION BY RANGE (id);
+
+-- test with an existing table attached
+CREATE TABLE part1_self_fk (
+    id bigint NOT NULL PRIMARY KEY,
+    id_abc bigint
+);
+ALTER TABLE parted_self_fk ATTACH PARTITION part1_self_fk FOR VALUES FROM (0) TO (10);
+
+-- test with a newly created partition
+CREATE TABLE part2_self_fk PARTITION OF parted_self_fk FOR VALUES FROM (10) TO (20);
+
+SELECT cr.relname, co.conname, co.contype, p.conname AS conparentrelname,
+       cf.relname AS conforeignrelname
+FROM pg_catalog.pg_constraint co
+JOIN pg_catalog.pg_class cr ON cr.oid = co.conrelid
+LEFT JOIN pg_catalog.pg_class cf ON cf.oid = co.confrelid
+LEFT JOIN pg_catalog.pg_constraint p ON p.oid = co.conparentid
+WHERE cr.relname IN ('parted_self_fk', 'part1_self_fk', 'part2_self_fk')
+ORDER BY cr.relname, co.conname, p.conname;
+
+DROP TABLE parted_self_fk;
 -- test a HOT update that invalidates the conflicting tuple.
 -- the trigger should still fire and catch the violation
 
-- 
2.37.3