From 4a2dbfc6be45c4ce2d9ac53502f931e6b1d70318 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Fri, 29 Nov 2024 08:52:27 +0100 Subject: [PATCH] Add tests for foreign keys with case-insensitive collations Some of the behaviors of the different referential actions, such as the difference between NO ACTION and RESTRICT are best illustrated using a case-insensitive collation. So add some tests for that. (What is actually being tested here is the behavior with values that are "distinct" (binary different) but compare as equal. Another way to do that would be with positive and negative zeroes with float types. But this way seems nicer and more flexible.) Discussion: https://www.postgresql.org/message-id/ea5b2777-266a-46fa-852f-6fca6ec480ad@eisentraut.org --- .../regress/expected/collate.icu.utf8.out | 62 +++++++++++++++++++ src/test/regress/sql/collate.icu.utf8.sql | 30 +++++++++ 2 files changed, 92 insertions(+) diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out index 79e76d4b85..9f5e57428e 100644 --- a/src/test/regress/expected/collate.icu.utf8.out +++ b/src/test/regress/expected/collate.icu.utf8.out @@ -2024,6 +2024,68 @@ 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. +-- foreign key actions +-- Some of the behaviors are most easily visible with a +-- case-insensitive collation. +CREATE TABLE test12pk (x text COLLATE case_insensitive PRIMARY KEY); +CREATE TABLE test12fk (a int, b text COLLATE case_insensitive REFERENCES test12pk (x) ON UPDATE NO ACTION); +INSERT INTO test12pk VALUES ('abc'); +INSERT INTO test12fk VALUES (1, 'abc'), (2, 'ABC'); +UPDATE test12pk SET x = 'ABC' WHERE x = 'abc'; -- ok +SELECT * FROM test12pk; + x +----- + ABC +(1 row) + +SELECT * FROM test12fk; -- no updates here + a | b +---+----- + 1 | abc + 2 | ABC +(2 rows) + +DROP TABLE test12pk, test12fk; +CREATE TABLE test12pk (x text COLLATE case_insensitive PRIMARY KEY); +CREATE TABLE test12fk (a int, b text COLLATE case_insensitive REFERENCES test12pk (x) ON UPDATE RESTRICT); +INSERT INTO test12pk VALUES ('abc'); +INSERT INTO test12fk VALUES (1, 'abc'), (2, 'ABC'); +UPDATE test12pk SET x = 'ABC' WHERE x = 'abc'; -- restrict violation +ERROR: update or delete on table "test12pk" violates foreign key constraint "test12fk_b_fkey" on table "test12fk" +DETAIL: Key (x)=(abc) is still referenced from table "test12fk". +SELECT * FROM test12pk; + x +----- + abc +(1 row) + +SELECT * FROM test12fk; + a | b +---+----- + 1 | abc + 2 | ABC +(2 rows) + +DROP TABLE test12pk, test12fk; +CREATE TABLE test12pk (x text COLLATE case_insensitive PRIMARY KEY); +CREATE TABLE test12fk (a int, b text COLLATE case_insensitive REFERENCES test12pk (x) ON UPDATE CASCADE); +INSERT INTO test12pk VALUES ('abc'); +INSERT INTO test12fk VALUES (1, 'abc'), (2, 'ABC'); +UPDATE test12pk SET x = 'ABC' WHERE x = 'abc'; -- ok +SELECT * FROM test12pk; + x +----- + ABC +(1 row) + +SELECT * FROM test12fk; -- was updated + a | b +---+----- + 1 | ABC + 2 | ABC +(2 rows) + +DROP TABLE test12pk, test12fk; -- 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 797e93ac71..5ee2da4e0e 100644 --- a/src/test/regress/sql/collate.icu.utf8.sql +++ b/src/test/regress/sql/collate.icu.utf8.sql @@ -743,6 +743,36 @@ 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 +-- foreign key actions +-- Some of the behaviors are most easily visible with a +-- case-insensitive collation. +CREATE TABLE test12pk (x text COLLATE case_insensitive PRIMARY KEY); +CREATE TABLE test12fk (a int, b text COLLATE case_insensitive REFERENCES test12pk (x) ON UPDATE NO ACTION); +INSERT INTO test12pk VALUES ('abc'); +INSERT INTO test12fk VALUES (1, 'abc'), (2, 'ABC'); +UPDATE test12pk SET x = 'ABC' WHERE x = 'abc'; -- ok +SELECT * FROM test12pk; +SELECT * FROM test12fk; -- no updates here +DROP TABLE test12pk, test12fk; + +CREATE TABLE test12pk (x text COLLATE case_insensitive PRIMARY KEY); +CREATE TABLE test12fk (a int, b text COLLATE case_insensitive REFERENCES test12pk (x) ON UPDATE RESTRICT); +INSERT INTO test12pk VALUES ('abc'); +INSERT INTO test12fk VALUES (1, 'abc'), (2, 'ABC'); +UPDATE test12pk SET x = 'ABC' WHERE x = 'abc'; -- restrict violation +SELECT * FROM test12pk; +SELECT * FROM test12fk; +DROP TABLE test12pk, test12fk; + +CREATE TABLE test12pk (x text COLLATE case_insensitive PRIMARY KEY); +CREATE TABLE test12fk (a int, b text COLLATE case_insensitive REFERENCES test12pk (x) ON UPDATE CASCADE); +INSERT INTO test12pk VALUES ('abc'); +INSERT INTO test12fk VALUES (1, 'abc'), (2, 'ABC'); +UPDATE test12pk SET x = 'ABC' WHERE x = 'abc'; -- ok +SELECT * FROM test12pk; +SELECT * FROM test12fk; -- was updated +DROP TABLE test12pk, test12fk; + -- 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.30.2