From 0b70579b695c878addf4d99bd93051b3667fce1a Mon Sep 17 00:00:00 2001 From: jian he 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