v8-0001-add-more-tests-for-pk-fk-tie-with-nondetermini.no-cfbot

From 61e185ba7e2444471ae733e941e3ea728d7a6bab Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Wed, 13 Nov 2024 19:38:42 +0800
Subject: [PATCH v8 1/1] add more tests for pk-fk tie with nondeterministic
 collation

add tests for referential_action as on delete casade, on update cascade.
---
 .../regress/expected/collate.icu.utf8.out     | 38 +++++++++++++++++++
 src/test/regress/sql/collate.icu.utf8.sql     | 38 +++++++++++++++++++
 2 files changed, 76 insertions(+)

diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index de17f7db6c..648a693247 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1849,6 +1849,44 @@ CREATE TABLE test11pk (x text COLLATE case_insensitive PRIMARY KEY);
 CREATE TABLE test11fk (x text COLLATE case_sensitive REFERENCES test11pk (x) ON UPDATE CASCADE ON DELETE CASCADE);  -- error
 ERROR:  foreign key constraint "test11fk_x_fkey" cannot be implemented
 DETAIL:  Key columns "x" of the referencing table and "x" of the referenced table have incompatible collations: "case_sensitive" and "case_insensitive".  If either collation is nondeterministic, then both collations have to be the same.
+CREATE OR REPLACE FUNCTION trigger_func() RETURNS trigger language plpgsql AS $$
+BEGIN
+    IF tg_op IN ('DELETE', 'UPDATE') THEN
+        RAISE INFO '%: %: old = %', TG_NAME, TG_WHEN, OLD;
+    END IF;
+
+    IF tg_op IN ('UPDATE', 'INSERT') THEN
+        RAISE INFO '%: %: new = %', TG_NAME, TG_WHEN, NEW;
+    END IF;
+
+    IF tg_op = 'DELETE' THEN
+        RETURN OLD;
+    ELSE
+        RETURN NEW;
+    END IF;
+END
+$$;
+-- foreign keys (same nondeterministic collations, test on update casade, on delete cascade)
+CREATE TABLE test12pk (x text collate case_insensitive PRIMARY KEY);
+CREATE TABLE test12fk (x text collate case_insensitive REFERENCES test12pk on update cascade on delete cascade);
+CREATE TRIGGER tg_fkside BEFORE UPDATE OR DELETE ON test12fk
+FOR EACH ROW
+EXECUTE PROCEDURE trigger_func();
+INSERT INTO test12pk VALUES ('A'), ('Å');
+INSERT INTO test12fk VALUES ('a'), ('A');
+update test12pk set x = 'A' where x = 'a'; --won't cascade to test12fk
+update test12pk set x = 'a' where x = 'a'; --do cascade to test12fk
+INFO:  tg_fkside: BEFORE: old = (a)
+INFO:  tg_fkside: BEFORE: new = (a)
+INFO:  tg_fkside: BEFORE: old = (A)
+INFO:  tg_fkside: BEFORE: new = (a)
+update test12pk set x = 'a' where x = 'a'; --won't cascade to test12fk
+delete from test12pk where x = 'A';  --do cascade to test12fk
+INFO:  tg_fkside: BEFORE: old = (a)
+INFO:  tg_fkside: BEFORE: old = (a)
+delete from test12pk where x = 'Å';  --won't cascade to test12fk
+DROP TABLE test12pk, test12fk;
+DROP FUNCTION trigger_func;
 -- partitioning
 CREATE TABLE test20 (a int, b text COLLATE case_insensitive) PARTITION BY LIST (b);
 CREATE TABLE test20_1 PARTITION OF test20 FOR VALUES IN ('abc');
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index 0c9491c260..ae7ede2ea7 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -694,6 +694,44 @@ CREATE TABLE test10fk (x text COLLATE case_insensitive REFERENCES test10pk (x) O
 CREATE TABLE test11pk (x text COLLATE case_insensitive PRIMARY KEY);
 CREATE TABLE test11fk (x text COLLATE case_sensitive REFERENCES test11pk (x) ON UPDATE CASCADE ON DELETE CASCADE);  -- error
 
+CREATE OR REPLACE FUNCTION trigger_func() RETURNS trigger language plpgsql AS $$
+BEGIN
+    IF tg_op IN ('DELETE', 'UPDATE') THEN
+        RAISE INFO '%: %: old = %', TG_NAME, TG_WHEN, OLD;
+    END IF;
+
+    IF tg_op IN ('UPDATE', 'INSERT') THEN
+        RAISE INFO '%: %: new = %', TG_NAME, TG_WHEN, NEW;
+    END IF;
+
+    IF tg_op = 'DELETE' THEN
+        RETURN OLD;
+    ELSE
+        RETURN NEW;
+    END IF;
+END
+$$;
+
+-- foreign keys (same nondeterministic collations, test on update casade, on delete cascade)
+CREATE TABLE test12pk (x text collate case_insensitive PRIMARY KEY);
+CREATE TABLE test12fk (x text collate case_insensitive REFERENCES test12pk on update cascade on delete cascade);
+
+CREATE TRIGGER tg_fkside BEFORE UPDATE OR DELETE ON test12fk
+FOR EACH ROW
+EXECUTE PROCEDURE trigger_func();
+
+INSERT INTO test12pk VALUES ('A'), ('Å');
+INSERT INTO test12fk VALUES ('a'), ('A');
+
+update test12pk set x = 'A' where x = 'a'; --won't cascade to test12fk
+update test12pk set x = 'a' where x = 'a'; --do cascade to test12fk
+update test12pk set x = 'a' where x = 'a'; --won't cascade to test12fk
+
+delete from test12pk where x = 'A';  --do cascade to test12fk
+delete from test12pk where x = 'Å';  --won't cascade to test12fk
+DROP TABLE test12pk, test12fk;
+DROP FUNCTION trigger_func;
+
 -- partitioning
 CREATE TABLE test20 (a int, b text COLLATE case_insensitive) PARTITION BY LIST (b);
 CREATE TABLE test20_1 PARTITION OF test20 FOR VALUES IN ('abc');
-- 
2.34.1