v8-0001-add-more-tests-for-pk-fk-tie-with-nondetermin.no-cfbot1

From 0b70579b695c878addf4d99bd93051b3667fce1a Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 14 Nov 2024 10:02:30 +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.
add tests: alter table add foreign key
add tests: alter table alter column set data type
---
 .../regress/expected/collate.icu.utf8.out     | 50 +++++++++++++++++++
 src/test/regress/sql/collate.icu.utf8.sql     | 46 +++++++++++++++++
 2 files changed, 96 insertions(+)

diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index de17f7db6c..2d79bbdf42 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1849,6 +1849,56 @@ 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 TABLE test11fail (x text);
+ALTER TABLE test11fail add foreign key (x) REFERENCES test11pk ON UPDATE CASCADE ON DELETE CASCADE; --error
+ERROR:  foreign key constraint "test11fail_x_fkey" cannot be implemented
+DETAIL:  Key columns "x" of the referencing table and "x" of the referenced table have incompatible collations: "default" and "case_insensitive".  If either collation is nondeterministic, then both collations have to be the same.
+CREATE TABLE test11fk (x text COLLATE case_insensitive );
+ALTER TABLE test11fk add foreign key (x) REFERENCES test11pk ON UPDATE CASCADE ON DELETE CASCADE; --ok
+ALTER TABLE test11pk alter column x set data type text; --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_insensitive" and "default".  If either collation is nondeterministic, then both collations have to be the same.
+ALTER TABLE test11fk alter column x set data type text; --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: "default" 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..b8823c81ac 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -694,6 +694,52 @@ 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 TABLE test11fail (x text);
+ALTER TABLE test11fail add foreign key (x) REFERENCES test11pk ON UPDATE CASCADE ON DELETE CASCADE; --error
+
+CREATE TABLE test11fk (x text COLLATE case_insensitive );
+ALTER TABLE test11fk add foreign key (x) REFERENCES test11pk ON UPDATE CASCADE ON DELETE CASCADE; --ok
+ALTER TABLE test11pk alter column x set data type text; --error
+ALTER TABLE test11fk alter column x set data type text; --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