Subject: Re: altering a column's collation leaves an invalid foreign key

From: jian he <[email-masked]>
Date: Sat, 13 Apr 2024 21:13:00 +0800

On Fri, Apr 12, 2024 at 5:06 PM jian he <[email-masked]> wrote:
>
> On Tue, Mar 26, 2024 at 1:00 PM jian he <[email-masked]> wrote:
> >
> > On Mon, Mar 25, 2024 at 2:47 PM Paul Jungwirth
> > <[email-masked]> wrote:
> > >
> > > On 3/23/24 10:04, Paul Jungwirth wrote:
> > > > Perhaps if the previous collation was nondeterministic we should force a re-check.
> > >
> > > Here is a patch implementing this. It was a bit more fuss than I expected, so maybe someone has a
> > > better way.
I think I found a simple way.

the logic is:
* ATExecAlterColumnType changes one column once at a time.
* one column can only have one collation. so we don't need  to store a
list of collation oid.
* ATExecAlterColumnType we can get the new collation (targetcollid)
and original collation info.
* RememberAllDependentForRebuilding  will check the column dependent,
whether this column is referenced by a foreign key or not information
is recorded.
so AlteredTableInfo->changedConstraintOids have the primary key and
foreign key oids.
* ATRewriteCatalogs will call ATPostAlterTypeCleanup (see the comments
in ATRewriteCatalogs)
* for tab->changedConstraintOids (foreign key, primary key) will call
ATPostAlterTypeParse, so
for foreign key (con->contype == CONSTR_FOREIGN)  will call TryReuseForeignKey.
* in TryReuseForeignKey, we can pass the information that our primary
key old collation is nondeterministic
and old collation != new collation to the foreign key constraint.
so foreign key can act accordingly at ATAddForeignKeyConstraint (old_check_ok).

based on the above logic, I add one bool in struct AlteredTableInfo,
one bool in struct Constraint.
bool in AlteredTableInfo is for storing it, later passing it to struct
Constraint.
we need bool in Constraint because ATAddForeignKeyConstraint needs it.
From 0eb0846450cb33996d6fb35c19290d297c26fd3c Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Sat, 13 Apr 2024 20:28:25 +0800
Subject: [PATCH v3 1/1] ALTER COLUMN SET DATA TYPE Re-check foreign key ties
 under certain condition

with deterministic collations, we check ties by looking at binary
equality. But nondeterministic collation can allow non-identical values
to be considered equal, e.g. 'a' and 'A' when case-insensitive.
ALTER COLUMN .. SET DATA TYPE make
some references that used to be valid may not be anymore.

for `ALTER COLUMN .. SET DATA TYPE`:
If the changed key columns is part of the primary key columns,
then under the following condition
we need to revalidate the foreign key constraint:
* the primary key is referenced by a foreign key constraint
* the new collation is not the same as the old
* the old collation is nondeterministic.

discussion: https://postgr.es/m/78d824e0-b21e-480d-a252-e4b84bc2c24b%40illuminatedcomputing.com
---
 src/backend/commands/tablecmds.c              | 62 +++++++++++++++----
 src/include/nodes/parsenodes.h                |  3 +-
 .../regress/expected/collate.icu.utf8.out     |  9 +++
 src/test/regress/sql/collate.icu.utf8.sql     |  8 +++
 4 files changed, 69 insertions(+), 13 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 000212f2..d5bd61f1 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -198,6 +198,7 @@ typedef struct AlteredTableInfo
 	/* Objects to rebuild after completing ALTER TYPE operations */
 	List	   *changedConstraintOids;	/* OIDs of constraints to rebuild */
 	List	   *changedConstraintDefs;	/* string definitions of same */
+	bool		verify_new_collation; /* T if we should recheck new collation for foreign key */
 	List	   *changedIndexOids;	/* OIDs of indexes to rebuild */
 	List	   *changedIndexDefs;	/* string definitions of same */
 	char	   *replicaIdentityIndex;	/* index to reset as REPLICA IDENTITY */
@@ -583,12 +584,13 @@ static void ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab,
 								   LOCKMODE lockmode);
 static void ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId,
 								 char *cmd, List **wqueue, LOCKMODE lockmode,
-								 bool rewrite);
+								 bool rewrite,
+								 bool verify_new_collation);
 static void RebuildConstraintComment(AlteredTableInfo *tab, AlterTablePass pass,
 									 Oid objid, Relation rel, List *domname,
 									 const char *conname);
 static void TryReuseIndex(Oid oldId, IndexStmt *stmt);
-static void TryReuseForeignKey(Oid oldId, Constraint *con);
+static void TryReuseForeignKey(Oid oldId, Constraint *con, bool verify_new_collation);
 static ObjectAddress ATExecAlterColumnGenericOptions(Relation rel, const char *colName,
 													 List *options, LOCKMODE lockmode);
 static void change_owner_fix_column_acls(Oid relationOid,
@@ -10256,6 +10258,15 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 									old_pfeqop_item);
 		}
 		if (old_check_ok)
+		{
+			/* also see TryReuseForeignKey.
+			 * collation_recheck is true means that, we need to
+			 * recheck the foreign key side value is ok with the
+			 * new primey key collation.
+			*/
+			old_check_ok = !fkconstraint->collation_recheck;
+		}
+		if (old_check_ok)
 		{
 			Oid			old_fktype;
 			Oid			new_fktype;
@@ -10301,10 +10312,6 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 			 * turn conform to the domain.  Consequently, we need not treat
 			 * domains specially here.
 			 *
-			 * Since we require that all collations share the same notion of
-			 * equality (which they do, because texteq reduces to bitwise
-			 * equality), we don't compare collation here.
-			 *
 			 * We need not directly consider the PK type.  It's necessarily
 			 * binary coercible to the opcintype of the unique index column,
 			 * and ri_triggers.c will only deal with PK datums in terms of
@@ -13738,6 +13745,30 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
 	/* And the collation */
 	targetcollid = GetColumnDefCollation(NULL, def, targettype);
 
+	if (OidIsValid(targetcollid))
+	{
+		Oid			typid;
+		int32		typmod;
+		Oid			collid;
+
+		get_atttypetypmodcoll(RelationGetRelid(tab->rel), attnum,
+							&typid, &typmod, &collid);
+		/*
+		* All deterministic collations use bitwise equality to resolve
+		* ties, but if the previous(source) collation was indeterminstic,
+		* and previous collation is not the same as the target collation then
+		* we must re-check the foreign key, because some references
+		* that use to be "equal" may not be anymore. we first store this
+		* information in AlteredTableInfo, later we pass it to foreign key
+		* Constraint.
+		* also see ATAddForeignKeyConstraint.
+		*/
+		if (OidIsValid(collid))
+		{
+			if (!get_collation_isdeterministic(collid) && targetcollid != collid)
+				tab->verify_new_collation = true;
+		}
+	}
 	/*
 	 * If there is a default expression for the column, get it and ensure we
 	 * can coerce it to the new datatype.  (We must do this before changing
@@ -14406,7 +14437,8 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
 
 		ATPostAlterTypeParse(oldId, relid, confrelid,
 							 (char *) lfirst(def_item),
-							 wqueue, lockmode, tab->rewrite);
+							 wqueue, lockmode, tab->rewrite,
+							 tab->verify_new_collation);
 	}
 	forboth(oid_item, tab->changedIndexOids,
 			def_item, tab->changedIndexDefs)
@@ -14417,7 +14449,8 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
 		relid = IndexGetRelation(oldId, false);
 		ATPostAlterTypeParse(oldId, relid, InvalidOid,
 							 (char *) lfirst(def_item),
-							 wqueue, lockmode, tab->rewrite);
+							 wqueue, lockmode, tab->rewrite,
+							 tab->verify_new_collation);
 
 		ObjectAddressSet(obj, RelationRelationId, oldId);
 		add_exact_object_address(&obj, objects);
@@ -14433,7 +14466,8 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
 		relid = StatisticsGetRelation(oldId, false);
 		ATPostAlterTypeParse(oldId, relid, InvalidOid,
 							 (char *) lfirst(def_item),
-							 wqueue, lockmode, tab->rewrite);
+							 wqueue, lockmode, tab->rewrite,
+							 tab->verify_new_collation);
 
 		ObjectAddressSet(obj, StatisticExtRelationId, oldId);
 		add_exact_object_address(&obj, objects);
@@ -14496,7 +14530,8 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
  */
 static void
 ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
-					 List **wqueue, LOCKMODE lockmode, bool rewrite)
+					 List **wqueue, LOCKMODE lockmode, bool rewrite,
+					 bool verify_new_collation)
 {
 	List	   *raw_parsetree_list;
 	List	   *querytree_list;
@@ -14623,7 +14658,7 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
 					/* rewriting neither side of a FK */
 					if (con->contype == CONSTR_FOREIGN &&
 						!rewrite && tab->rewrite == 0)
-						TryReuseForeignKey(oldId, con);
+						TryReuseForeignKey(oldId, con, verify_new_collation);
 					con->reset_default_tblspc = true;
 					cmd->subtype = AT_ReAddConstraint;
 					tab->subcmds[AT_PASS_OLD_CONSTR] =
@@ -14784,7 +14819,7 @@ TryReuseIndex(Oid oldId, IndexStmt *stmt)
  * this constraint can be skipped.
  */
 static void
-TryReuseForeignKey(Oid oldId, Constraint *con)
+TryReuseForeignKey(Oid oldId, Constraint *con, bool verify_new_collation)
 {
 	HeapTuple	tup;
 	Datum		adatum;
@@ -14816,6 +14851,9 @@ TryReuseForeignKey(Oid oldId, Constraint *con)
 		con->old_conpfeqop = lappend_oid(con->old_conpfeqop, rawarr[i]);
 
 	ReleaseSysCache(tup);
+
+	/* verify_new_collation is computed at ATExecAlterColumnType */
+	con->collation_recheck = verify_new_collation;
 }
 
 /*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index f763f790..0349c61c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2777,7 +2777,8 @@ typedef struct Constraint
 	List	   *old_conpfeqop;	/* pg_constraint.conpfeqop of my former self */
 	Oid			old_pktable_oid;	/* pg_constraint.confrelid of my former
 									 * self */
-
+	bool		collation_recheck; /* does primary key columns collation change need
+									* foreign key constraint recheck */
 	ParseLoc	location;		/* token location, or -1 if unknown */
 } Constraint;
 
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index 4b8c8f14..4119776f 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1933,6 +1933,15 @@ SELECT * FROM test11fk;
 ---
 (0 rows)
 
+-- Test altering the collation of the referenced column.
+CREATE TABLE pktable (x text COLLATE case_insensitive PRIMARY KEY);
+CREATE TABLE fktable (x text REFERENCES pktable);
+INSERT INTO pktable VALUES ('a');
+INSERT INTO fktable VALUES ('A');
+-- should fail:
+ALTER TABLE pktable ALTER COLUMN x TYPE text COLLATE "C";
+ERROR:  insert or update on table "fktable" violates foreign key constraint "fktable_x_fkey"
+DETAIL:  Key (x)=(A) is not present in table "pktable".
 -- 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 80f28a97..08a69649 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -721,6 +721,14 @@ DELETE FROM test11pk WHERE x = 'abc';
 SELECT * FROM test11pk;
 SELECT * FROM test11fk;
 
+-- Test altering the collation of the referenced column.
+CREATE TABLE pktable (x text COLLATE case_insensitive PRIMARY KEY);
+CREATE TABLE fktable (x text REFERENCES pktable);
+INSERT INTO pktable VALUES ('a');
+INSERT INTO fktable VALUES ('A');
+-- should fail:
+ALTER TABLE pktable ALTER COLUMN x TYPE text COLLATE "C";
+
 -- 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');

base-commit: 4cc1c76fe9f13aa96bae14f4fcfdf6d508af72a4
-- 
2.34.1

---

From: jian he <[email-masked]>
Date: Fri, 7 Jun 2024 14:39:15 +0800

> we need bool in Constraint because ATAddForeignKeyConstraint needs it.

I refactored the comments.
also added some extra tests hoping to make it more bullet proof, maybe
it's redundant.
From 9a2e73231e037c7555449d8790885c1feb4f3ed0 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Fri, 7 Jun 2024 14:32:55 +0800
Subject: [PATCH v4 1/1] Revalidate PK-FK ties when PK collation is changed

With deterministic collations, we check the PK-FK ties by looking at binary

for `ALTER COLUMN .. SET DATA TYPE`:
If the changed key column is part of the primary key columns,
then under the following conditions we need to revalidate the PK-FK ties
* the PK new collation is different from the old
* the old collation is nondeterministic.

we need to revalidate the PK-FK ties.
discussion: https://postgr.es/m/78d824e0-b21e-480d-a252-e4b84bc2c24b%40illuminatedcomputing.com
---
 src/backend/commands/tablecmds.c              | 61 +++++++++++++++----
 src/include/nodes/parsenodes.h                |  3 +-
 .../regress/expected/collate.icu.utf8.out     | 18 ++++++
 src/test/regress/sql/collate.icu.utf8.sql     | 16 +++++
 4 files changed, 85 insertions(+), 13 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 7b6c69b7..77337b6f 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -199,6 +199,7 @@ typedef struct AlteredTableInfo
 	/* Objects to rebuild after completing ALTER TYPE operations */
 	List	   *changedConstraintOids;	/* OIDs of constraints to rebuild */
 	List	   *changedConstraintDefs;	/* string definitions of same */
+	bool		verify_new_collation; /* T if we need recheck the new collation */
 	List	   *changedIndexOids;	/* OIDs of indexes to rebuild */
 	List	   *changedIndexDefs;	/* string definitions of same */
 	char	   *replicaIdentityIndex;	/* index to reset as REPLICA IDENTITY */
@@ -572,12 +573,13 @@ static void ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab,
 								   LOCKMODE lockmode);
 static void ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId,
 								 char *cmd, List **wqueue, LOCKMODE lockmode,
-								 bool rewrite);
+								 bool rewrite,
+								 bool verify_new_collation);
 static void RebuildConstraintComment(AlteredTableInfo *tab, AlterTablePass pass,
 									 Oid objid, Relation rel, List *domname,
 									 const char *conname);
 static void TryReuseIndex(Oid oldId, IndexStmt *stmt);
-static void TryReuseForeignKey(Oid oldId, Constraint *con);
+static void TryReuseForeignKey(Oid oldId, Constraint *con, bool verify_new_collation);
 static ObjectAddress ATExecAlterColumnGenericOptions(Relation rel, const char *colName,
 													 List *options, LOCKMODE lockmode);
 static void change_owner_fix_column_acls(Oid relationOid,
@@ -9857,6 +9859,15 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 									old_pfeqop_item);
 		}
 		if (old_check_ok)
+		{
+			/*
+			 * collation_recheck is true then we need to
+			 * revalidate the foreign key and primary key value ties.
+			 * see also TryReuseForeignKey.
+			*/
+			old_check_ok = !fkconstraint->collation_recheck;
+		}
+		if (old_check_ok)
 		{
 			Oid			old_fktype;
 			Oid			new_fktype;
@@ -9902,10 +9913,6 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 			 * turn conform to the domain.  Consequently, we need not treat
 			 * domains specially here.
 			 *
-			 * Since we require that all collations share the same notion of
-			 * equality (which they do, because texteq reduces to bitwise
-			 * equality), we don't compare collation here.
-			 *
 			 * We need not directly consider the PK type.  It's necessarily
 			 * binary coercible to the opcintype of the unique index column,
 			 * and ri_triggers.c will only deal with PK datums in terms of
@@ -13035,6 +13042,29 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
 	/* And the collation */
 	targetcollid = GetColumnDefCollation(NULL, def, targettype);
 
+	if (OidIsValid(targetcollid))
+	{
+		Oid			typid;
+		int32		typmod;
+		Oid			source_collid;
+
+		get_atttypetypmodcoll(RelationGetRelid(tab->rel), attnum,
+							&typid, &typmod, &source_collid);
+		/*
+		* All deterministic collations use bitwise equality to resolve
+		* PK-FK ties. But if the primary key (source) collation was indeterministic,
+		* and ALTER COLUMN .. SET DATA TYPE changes the primary key collation, then
+		* we must re-check the PK-FK ties, because some references
+		* that used to be "equal" may not be anymore.
+		* We add a flag in AlteredTableInfo to signify
+		* that we need to verify the new collation for PK-FK ties.
+		* see ATAddForeignKeyConstraint also.
+		*/
+		if (OidIsValid(source_collid) &&
+				!get_collation_isdeterministic(source_collid) &&
+				(targetcollid != source_collid))
+			tab->verify_new_collation = true;
+	}
 	/*
 	 * If there is a default expression for the column, get it and ensure we
 	 * can coerce it to the new datatype.  (We must do this before changing
@@ -13742,7 +13772,8 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
 
@@ -13753,7 +13784,8 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
 
@@ -13769,7 +13801,8 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
 
@@ -13832,7 +13865,8 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
@@ -13959,7 +13993,7 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
@@ -14119,7 +14153,7 @@ TryReuseIndex(Oid oldId, IndexStmt *stmt)
@@ -14151,6 +14185,9 @@ TryReuseForeignKey(Oid oldId, Constraint *con)
 
 	ReleaseSysCache(tup);
+
 
 /*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index ddfed02d..29b484f5 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2773,7 +2773,8 @@ typedef struct Constraint
 	List	   *old_conpfeqop;	/* pg_constraint.conpfeqop of my former self */
 	Oid			old_pktable_oid;	/* pg_constraint.confrelid of my former
 									 * self */
-
+	bool		collation_recheck; /* does primary key columns collation changes need
+									* foreign key constraint recheck */
 	ParseLoc	location;		/* token location, or -1 if unknown */
 } Constraint;
 
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index 7d59fb44..ea28031b 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1933,6 +1933,24 @@ SELECT * FROM test11fk;
 ---
 (0 rows)
 
+-- Test altering the collation of the referenced column.
+CREATE COLLATION ignore_accent_case (provider = icu, deterministic = false, locale = 'und-u-ks-level1');
+CREATE TABLE pktable (x text COLLATE case_insensitive PRIMARY KEY);
+CREATE TABLE fktable (x text REFERENCES pktable);
+INSERT INTO pktable VALUES ('a');
+INSERT INTO fktable VALUES ('A');
+-- should fail:
+ALTER TABLE pktable ALTER COLUMN x TYPE text COLLATE "C";
+ERROR:  insert or update on table "fktable" violates foreign key constraint "fktable_x_fkey"
+DETAIL:  Key (x)=(A) is not present in table "pktable".
+-- should fail:
+ALTER TABLE pktable ALTER COLUMN x TYPE text COLLATE ignore_accents;
+ERROR:  insert or update on table "fktable" violates foreign key constraint "fktable_x_fkey"
+DETAIL:  Key (x)=(A) is not present in table "pktable".
+-- should ok:
+ALTER TABLE pktable ALTER COLUMN x TYPE text COLLATE case_insensitive;
+ALTER TABLE pktable ALTER COLUMN x TYPE text COLLATE ignore_accent_case;
+DROP TABLE fktable, pktable;
 -- 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 80f28a97..dcdbbc48 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -721,6 +721,22 @@ DELETE FROM test11pk WHERE x = 'abc';
 SELECT * FROM test11pk;
 SELECT * FROM test11fk;
 
+-- Test altering the collation of the referenced column.
+CREATE COLLATION ignore_accent_case (provider = icu, deterministic = false, locale = 'und-u-ks-level1');
+CREATE TABLE pktable (x text COLLATE case_insensitive PRIMARY KEY);
+CREATE TABLE fktable (x text REFERENCES pktable);
+INSERT INTO pktable VALUES ('a');
+INSERT INTO fktable VALUES ('A');
+-- should fail:
+ALTER TABLE pktable ALTER COLUMN x TYPE text COLLATE "C";
+
+-- should fail:
+ALTER TABLE pktable ALTER COLUMN x TYPE text COLLATE ignore_accents;
+
+-- should ok:
+ALTER TABLE pktable ALTER COLUMN x TYPE text COLLATE case_insensitive;
+ALTER TABLE pktable ALTER COLUMN x TYPE text COLLATE ignore_accent_case;
+DROP TABLE fktable, pktable;
 -- 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

---

From: Tom Lane <[email-masked]>
Date: Fri, 07 Jun 2024 16:12:08 -0400

>> and old collation != new collation to the foreign key constraint.

I have a basic question about this: why are we allowing FKs to be
based on nondeterministic collations at all?  ISTM that that breaks
the assumption that there is exactly one referenced row for any
referencing row.

			regards, tom lane

---

From: jian he <[email-masked]>
Date: Sat, 8 Jun 2024 12:14:53 +0800

> the assumption that there is exactly one referenced row for any

for FKs nondeterministic,
I think that would require the PRIMARY KEY collation to not be
indeterministic also.

for example:
CREATE COLLATION ignore_accent_case (provider = icu, deterministic =
false, locale = 'und-u-ks-level1');
DROP TABLE IF EXISTS fktable, pktable;
CREATE TABLE pktable (x text COLLATE ignore_accent_case PRIMARY KEY);
CREATE TABLE fktable (x text REFERENCES pktable on update cascade on
delete cascade);
INSERT INTO pktable VALUES ('A');
INSERT INTO fktable VALUES ('a');
INSERT INTO fktable VALUES ('A');
update pktable set x  = 'Å';
table fktable;

if FK is nondeterministic, then it looks PK more like FK.
the following example, one FK row is referenced by two PK rows.

DROP TABLE IF EXISTS fktable, pktable;
CREATE TABLE pktable (x text COLLATE "C" PRIMARY KEY);
CREATE TABLE fktable (x text COLLATE ignore_accent_case REFERENCES
pktable on update cascade on delete cascade);
INSERT INTO pktable VALUES ('A'), ('Å');
INSERT INTO fktable VALUES ('A');

begin; delete from pktable where x = 'Å'; TABLE fktable; rollback;
begin; delete from pktable where x = 'A'; TABLE fktable; rollback;

---

From: Peter Eisentraut <[email-masked]>
Date: Tue, 18 Jun 2024 10:50:24 +0200

> INSERT INTO fktable VALUES ('A');

Yes, this is a problem.  The RI checks are done with the collation of 
the primary key.

The comment at ri_GenerateQualCollation() says "the SQL standard 
specifies that RI comparisons should use the referenced column's 
collation".  But this is not what it says in my current copy.

... [ digs around ISO archives ] ...

Yes, this was changed in SQL:2016 to require the collation on the PK 
side and the FK side to match at constraint creation time.  The argument 
made is exactly the same we have here.  This was actually already the 
rule in SQL:1999 but was then relaxed in SQL:2003 and then changed back 
because it was a mistake.

We probably don't need to enforce this for deterministic collations, 
which would preserve some backward compatibility.

I'll think some more about what steps to take to solve this and what to 
do about back branches etc.

---

From: jian he <[email-masked]>
Date: Tue, 16 Jul 2024 16:29:00 +0800

> do about back branches etc.

I have come up with 3 corner cases.

---case1. not ok. PK indeterministic, FK default

RI_FKey_check (Check foreign key existence ) querybuf.data is
SELECT 1 FROM ONLY "public"."pktable" x WHERE "x"
OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
in here, fktable doesn't have collation.
we cannot rewrite to
SELECT 1 FROM ONLY "public"."pktable" x WHERE "x"
OPERATOR(pg_catalog.=) $1 collate "C" FOR KEY SHARE OF x
so assumption (only one referenced row for any referencing row) will
break when inserting values to fktable.
RI_FKey_check already allows invalidate values to happen, not sure how
ri_GenerateQualCollation can help.
overall i don't know how to stop invalid value  while inserting value
to fktable in this case.

---case2. not ok case PK deterministic, FK indeterministic
DROP TABLE IF EXISTS fktable, pktable;
CREATE TABLE pktable (x text COLLATE "C" PRIMARY KEY);
CREATE TABLE fktable (x text COLLATE ignore_accent_case REFERENCES
pktable on update cascade on delete cascade);
INSERT INTO pktable VALUES ('A'), ('Å');
INSERT INTO fktable VALUES ('A');
begin; update pktable set x  = 'B' where x = 'Å'; table fktable; rollback;
begin; update pktable set x  = 'B' where x = 'A'; table fktable; rollback;

when cascade update fktable, in RI_FKey_cascade_upd
we can use pktable's collation. but again, a query updating fktable
only, using pktable collation seems strange.

---case3. ---not ok case PK indeterministic, FK deterministic
DROP TABLE IF EXISTS fktable, pktable;
CREATE TABLE pktable (x text COLLATE ignore_accent_case PRIMARY KEY);
CREATE TABLE fktable (x text collate "C" REFERENCES pktable on update
cascade on delete cascade);
INSERT INTO pktable VALUES ('A');
INSERT INTO fktable VALUES ('A');
INSERT INTO fktable VALUES ('a');
begin; update pktable set x  = 'Å'; table fktable; rollback;

when we "INSERT INTO fktable VALUES ('a');"
we can disallow this invalid query in RI_FKey_check by constructing
the following stop query
SELECT 1 FROM ONLY public.pktable x WHERE x OPERATOR(pg_catalog.=) 'a'
collate "C" FOR KEY SHARE OF x;
but this query associated PK table with fktable collation seems weird?

summary:
case1 seems hard to resolve
case2 can be resolved in ri_GenerateQualCollation, not 100% sure.
case3 can be resolved in RI_FKey_check
because case1 is hard to resolve;
so overall I feel like erroring out PK indeterministic and FK
indeterministic while creating foreign keys is easier.

We can mandate foreign keys and primary key columns be deterministic
in ATAddForeignKeyConstraint.
The attached patch does that.

That means src/test/regress/sql/collate.icu.utf8.sql table test10pk,
table test11pk will have a big change.
so only attach src/backend/commands/tablecmds.c changes.
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 721d2478..491ba87c 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -9757,6 +9757,39 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 				(errcode(ERRCODE_INVALID_FOREIGN_KEY),
 				 errmsg("number of referencing and referenced columns for foreign key disagree")));
 
+	for (i = 0; i < numpks; i++)
+	{
+		int32		keycoltypmod;
+		Oid			keycoltype;
+		Oid			keycolcollation;
+
+		get_atttypetypmodcoll(RelationGetRelid(pkrel), pkattnum[i],
+								&keycoltype, &keycoltypmod,
+								&keycolcollation);
+		if (OidIsValid(keycolcollation) &&
+				!get_collation_isdeterministic(keycolcollation))
+			ereport(ERROR,
+					(errcode(ERRCODE_DATATYPE_MISMATCH),
+						 errmsg("foreign key constraint \"%s\" cannot be implemented",
+								fkconstraint->conname),
+						 errdetail("Cannot have indeterministic collations in primary key for referenced table \"%s\"",
+								RelationGetRelationName(pkrel)),
+						 errhint("You may make the primary key collations deterministic.")));
+
+		get_atttypetypmodcoll(RelationGetRelid(rel), fkattnum[i],
+								&keycoltype, &keycoltypmod,
+								&keycolcollation);
+		if (OidIsValid(keycolcollation) &&
+				!get_collation_isdeterministic(keycolcollation))
+			ereport(ERROR,
+					(errcode(ERRCODE_DATATYPE_MISMATCH),
+					 errmsg("foreign key constraint \"%s\" cannot be implemented",
+							fkconstraint->conname),
+					 errdetail("Foreign key cannot use indeterministic collations for referencing table \"%s\"",
+						RelationGetRelationName(rel)),
+					 errhint("You may make the foreign key collations deterministic.")));
+	}
+
 	/*
 	 * On the strength of a previous constraint, we might avoid scanning
 	 * tables to validate this one.  See below.

---

From: Peter Eisentraut <[email-masked]>
Date: Tue, 3 Sep 2024 11:41:20 +0200

> also added some extra tests hoping to make it more bullet proof, maybe

I like this patch version (v4).  It's the simplest, probably also 
easiest to backpatch.

It has a flaw: It will also trigger a FK recheck if you alter the 
collation of the referencing column (foreign key column), even though 
that is not necessary.  (Note that your tests and the examples in this 
thread only discuss altering the PK column collation, because that is 
what is actually used during the foreign key checks.)  Maybe there is an 
easy way to avoid that, but I couldn't see one in that patch structure.

Maybe that is ok as a compromise.  If, in the future, we make it a 
requirement that the collations on the PK and FK side have to be the 
same if either collation is nondeterministic, then this case can no 
longer happen anyway.  And so building more infrastructure for this 
check might be wasted.

---

From: jian he <[email-masked]>
Date: Wed, 4 Sep 2024 14:54:00 +0800

> I like this patch version (v4).  It's the simplest, probably also

I am actually confused.
In this email thread [1], I listed 3 corn cases.
I thought all these 3 corner cases should not be allowed.
but V4 didn't solve these corner case issues.
what do you think of their corner case, should it be allowed?

Anyway, I thought these corner cases should not be allowed to happen,
so I made sure PK, FK ties related collation were deterministic.
PK can have indeterministic collation as long as it does not interact with FK.

[1] https://www.postgresql.org/message-id/CACJufxEW6OMBqt8cbr%3D3Jt%2B%2BZd_SL-4YDjfk7Q7DhGKiSLcu4g%40mail.gmail.com
From 024d818e0e286af10b74649bd3156836d6f28447 Mon Sep 17 00:00:00 2001
From: jian he <[email-masked]>
Date: Wed, 4 Sep 2024 14:31:43 +0800
Subject: [PATCH v5 1/1] ensure PK-FK ties related collation indeterministic

PK can have indeterministic collation as long as it not interact with FK.
To make the PK-FK tie consistent, we need to ensure both side's collation is
deterministic, otherwise, the following two some anomalitys can happen.

1. PK side collation is indeterministic, multiple form FK values equal to one PK
value.
2. FK side collation is indeterministic, multiple form PK values equal to one FK
value.

discussion: https://postgr.es/m/78d824e0-b21e-480d-a252-e4b84bc2c24b%40illuminatedcomputing.com
---
 src/backend/commands/tablecmds.c              |  65 ++++++++
 .../regress/expected/collate.icu.utf8.out     | 142 +++++++-----------
 src/test/regress/sql/collate.icu.utf8.sql     |  60 ++++----
 3 files changed, 151 insertions(+), 116 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index b3cc6f8f69..6c951ae36e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -9705,6 +9705,71 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo *tab, Relation rel,
 				(errcode(ERRCODE_INVALID_FOREIGN_KEY),
 				 errmsg("number of referencing and referenced columns for foreign key disagree")));
 
+	for (i = 0; i < numpks; i++)
+	{
+		int32		keycoltypmod;
+		Oid			keycoltype;
+		Oid			keycolcollation;
+		Oid			typcollation;
+		bool		col_coll_deterministic = true;
+
+		/*
+		 * for both foreign key and primary key, we need make sure the specified
+		 * keys collation (via COLLATE clause) is deterministic. we also need to
+		 * ensure the key's typcollation is indeterministic (for domain type).
+		 *
+		 * PK and FK both are not indeterministic, then anomality can happen:
+		 * 1. PK side collation is indeterministic, multiple form FK values
+		 *    equal to one PK value.
+		 * 2. FK side collation is indeterministic, multiple form PK values
+		 *    equal to one FK value.
+		 *
+		 */
+
+		/* checking PK collation deterministic */
+		get_atttypetypmodcoll(RelationGetRelid(pkrel), pkattnum[i],
+								&keycoltype, &keycoltypmod,
+								&keycolcollation);
+
+		typcollation = get_typcollation(keycoltype);
+		if (OidIsValid(typcollation))
+			col_coll_deterministic = get_collation_isdeterministic(typcollation);
+
+		if (col_coll_deterministic && OidIsValid(keycolcollation))
+			col_coll_deterministic = get_collation_isdeterministic(keycolcollation);
+
+		if (!col_coll_deterministic)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATATYPE_MISMATCH),
+					errmsg("foreign key constraint \"%s\" cannot be implemented",
+							fkconstraint->conname),
+					errdetail("Cannot have indeterministic collations in primary key for referenced table \"%s\".",
+							   RelationGetRelationName(pkrel)),
+					errhint("You may need to make primary key constraint's collation deterministic.")));
+
+
+		/* checking PK collation deterministic */
+		get_atttypetypmodcoll(RelationGetRelid(rel), fkattnum[i],
+								&keycoltype, &keycoltypmod,
+								&keycolcollation);
+
+		typcollation = get_typcollation(keycoltype);
+		if (OidIsValid(typcollation))
+			col_coll_deterministic = get_collation_isdeterministic(typcollation);
+
+		if (col_coll_deterministic && OidIsValid(keycolcollation))
+			col_coll_deterministic = get_collation_isdeterministic(keycolcollation);
+
+		if (!col_coll_deterministic)
+			ereport(ERROR,
+					(errcode(ERRCODE_DATATYPE_MISMATCH),
+					 errmsg("foreign key constraint \"%s\" cannot be implemented",
+							fkconstraint->conname),
+					 errdetail("Foreign key constraint cannot use indeterministic collation for referencing table \"%s\".",
+								RelationGetRelationName(rel)),
+					 errhint("You may need to make the foreign key constraint's collation deterministic.")));
+	}
+
 	/*
 	 * On the strength of a previous constraint, we might avoid scanning
 	 * tables to validate this one.  See below.
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index 31345295c1..5074812099 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1841,100 +1841,62 @@ SELECT * FROM test4 WHERE b = 'Cote' COLLATE case_insensitive;
 (1 row)
 
 -- foreign keys (should use collation of primary key)
--- PK is case-sensitive, FK is case-insensitive
+CREATE COLLATION ignore_accent_case (provider = icu, deterministic = false, locale = 'und-u-ks-level1');
+CREATE DOMAIN text_d_ignore_accent_case AS text COLLATE ignore_accent_case;
+-- PK is case-sensitive (deterministic), FK is case-insensitive (indeterministic).
+-- not OK, multiple PK-side value equal to one FK-side value not allowed
+-- e.g. not allowed case: PK values {'a', 'A"} both equal to FK value {'A'}
 CREATE TABLE test10pk (x text COLLATE case_sensitive PRIMARY KEY);
-INSERT INTO test10pk VALUES ('abc'), ('def'), ('ghi');
 CREATE TABLE test10fk (x text COLLATE case_insensitive REFERENCES test10pk (x) ON UPDATE CASCADE ON DELETE CASCADE);
-INSERT INTO test10fk VALUES ('abc');  -- ok
-INSERT INTO test10fk VALUES ('ABC');  -- error
-ERROR:  insert or update on table "test10fk" violates foreign key constraint "test10fk_x_fkey"
-DETAIL:  Key (x)=(ABC) is not present in table "test10pk".
-INSERT INTO test10fk VALUES ('xyz');  -- error
-ERROR:  insert or update on table "test10fk" violates foreign key constraint "test10fk_x_fkey"
-DETAIL:  Key (x)=(xyz) is not present in table "test10pk".
-SELECT * FROM test10pk;
-  x  
------
- abc
- def
- ghi
-(3 rows)
-
-SELECT * FROM test10fk;
-  x  
------
- abc
-(1 row)
-
--- restrict update even though the values are "equal" in the FK table
-UPDATE test10fk SET x = 'ABC' WHERE x = 'abc';  -- error
-ERROR:  insert or update on table "test10fk" violates foreign key constraint "test10fk_x_fkey"
-DETAIL:  Key (x)=(ABC) is not present in table "test10pk".
-SELECT * FROM test10fk;
-  x  
------
- abc
-(1 row)
-
-DELETE FROM test10pk WHERE x = 'abc';
-SELECT * FROM test10pk;
-  x  
------
- def
- ghi
-(2 rows)
-
-SELECT * FROM test10fk;
- x 
----
-(0 rows)
-
--- PK is case-insensitive, FK is case-sensitive
+ERROR:  foreign key constraint "test10fk_x_fkey" cannot be implemented
+DETAIL:  Foreign key constraint cannot use indeterministic collation for referencing table "test10fk".
+HINT:  You may need to make the foreign key constraint's collation deterministic.
+-- PK is case-insensitive (indeterministic), FK is case-sensitive (deterministic)
+-- more than one referencing (FK) row value equal to referenced (PK) row should not allowed
+-- e.g. not allowed case: FK values {'a', 'A"} both equal to PK value {'A'}
 CREATE TABLE test11pk (x text COLLATE case_insensitive PRIMARY KEY);
-INSERT INTO test11pk VALUES ('abc'), ('def'), ('ghi');
 CREATE TABLE test11fk (x text COLLATE case_sensitive REFERENCES test11pk (x) ON UPDATE CASCADE ON DELETE CASCADE);
-INSERT INTO test11fk VALUES ('abc');  -- ok
-INSERT INTO test11fk VALUES ('ABC');  -- ok
-INSERT INTO test11fk VALUES ('xyz');  -- error
-ERROR:  insert or update on table "test11fk" violates foreign key constraint "test11fk_x_fkey"
-DETAIL:  Key (x)=(xyz) is not present in table "test11pk".
-SELECT * FROM test11pk;
-  x  
------
- abc
- def
- ghi
-(3 rows)
-
-SELECT * FROM test11fk;
-  x  
------
- abc
- ABC
-(2 rows)
-
--- cascade update even though the values are "equal" in the PK table
-UPDATE test11pk SET x = 'ABC' WHERE x = 'abc';
-SELECT * FROM test11fk;
-  x  
------
- ABC
- ABC
-(2 rows)
-
-DELETE FROM test11pk WHERE x = 'abc';
-SELECT * FROM test11pk;
-  x  
------
- def
- ghi
-(2 rows)
-
-SELECT * FROM test11fk;
- x 
----
-(0 rows)
-
+ERROR:  foreign key constraint "test11fk_x_fkey" cannot be implemented
+DETAIL:  Cannot have indeterministic collations in primary key for referenced table "test11pk".
+HINT:  You may need to make primary key constraint's collation deterministic.
+drop table test11pk;
+CREATE TABLE test12pk (x text PRIMARY KEY);
+CREATE TABLE test12fk (x text REFERENCES test12pk on update cascade on delete cascade);
+--alter table change primary key's collation to indeterministic, should fail.
+alter table test12pk alter column x set data type text COLLATE ignore_accent_case;
+ERROR:  foreign key constraint "test12fk_x_fkey" cannot be implemented
+DETAIL:  Cannot have indeterministic collations in primary key for referenced table "test12pk".
+HINT:  You may need to make primary key constraint's collation deterministic.
+alter table test12pk alter column x set data type text_d_ignore_accent_case;
+ERROR:  foreign key constraint "test12fk_x_fkey" cannot be implemented
+DETAIL:  Cannot have indeterministic collations in primary key for referenced table "test12pk".
+HINT:  You may need to make primary key constraint's collation deterministic.
+---should also fail.
+alter table test12pk alter column x set data type text_d_ignore_accent_case COLLATE "C";
+ERROR:  foreign key constraint "test12fk_x_fkey" cannot be implemented
+DETAIL:  Cannot have indeterministic collations in primary key for referenced table "test12pk".
+HINT:  You may need to make primary key constraint's collation deterministic.
+drop table test12pk,test12fk;
+--alter table change foreign key's collation, all these case should fail.
+CREATE TABLE test13pk (x text PRIMARY KEY);
+CREATE TABLE test13fk (x text REFERENCES test13pk on update cascade on delete cascade);
+alter table test13fk alter column x set data type text COLLATE ignore_accent_case;
+ERROR:  foreign key constraint "test13fk_x_fkey" cannot be implemented
+DETAIL:  Foreign key constraint cannot use indeterministic collation for referencing table "test13fk".
+HINT:  You may need to make the foreign key constraint's collation deterministic.
+alter table test13fk alter column x set data type text_d_ignore_accent_case;
+ERROR:  foreign key constraint "test13fk_x_fkey" cannot be implemented
+DETAIL:  Foreign key constraint cannot use indeterministic collation for referencing table "test13fk".
+HINT:  You may need to make the foreign key constraint's collation deterministic.
+alter table test13fk alter column x set data type text_d_ignore_accent_case COLLATE ignore_accent_case;
+ERROR:  foreign key constraint "test13fk_x_fkey" cannot be implemented
+DETAIL:  Foreign key constraint cannot use indeterministic collation for referencing table "test13fk".
+HINT:  You may need to make the foreign key constraint's collation deterministic.
+alter table test13fk alter column x set data type text_d_ignore_accent_case COLLATE "C";
+ERROR:  foreign key constraint "test13fk_x_fkey" cannot be implemented
+DETAIL:  Foreign key constraint cannot use indeterministic collation for referencing table "test13fk".
+HINT:  You may need to make the foreign key constraint's collation deterministic.
+drop table test13pk,test13fk;
 -- 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 80f28a97d7..ad418e87eb 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -688,38 +688,46 @@ SELECT * FROM test4 WHERE b = 'Cote' COLLATE ignore_accents;  -- still case-sens
 SELECT * FROM test4 WHERE b = 'Cote' COLLATE case_insensitive;
 
 -- foreign keys (should use collation of primary key)
+CREATE COLLATION ignore_accent_case (provider = icu, deterministic = false, locale = 'und-u-ks-level1');
+CREATE DOMAIN text_d_ignore_accent_case AS text COLLATE ignore_accent_case;
 
--- PK is case-sensitive, FK is case-insensitive
+-- PK is case-sensitive (deterministic), FK is case-insensitive (indeterministic).
+-- not OK, multiple PK-side value equal to one FK-side value not allowed
+-- e.g. not allowed case: PK values {'a', 'A"} both equal to FK value {'A'}
 CREATE TABLE test10pk (x text COLLATE case_sensitive PRIMARY KEY);
-INSERT INTO test10pk VALUES ('abc'), ('def'), ('ghi');
 CREATE TABLE test10fk (x text COLLATE case_insensitive REFERENCES test10pk (x) ON UPDATE CASCADE ON DELETE CASCADE);
-INSERT INTO test10fk VALUES ('abc');  -- ok
-INSERT INTO test10fk VALUES ('ABC');  -- error
-INSERT INTO test10fk VALUES ('xyz');  -- error
-SELECT * FROM test10pk;
-SELECT * FROM test10fk;
--- restrict update even though the values are "equal" in the FK table
-UPDATE test10fk SET x = 'ABC' WHERE x = 'abc';  -- error
-SELECT * FROM test10fk;
-DELETE FROM test10pk WHERE x = 'abc';
-SELECT * FROM test10pk;
-SELECT * FROM test10fk;
 
--- PK is case-insensitive, FK is case-sensitive
+
+-- PK is case-insensitive (indeterministic), FK is case-sensitive (deterministic)
+-- more than one referencing (FK) row value equal to referenced (PK) row should not allowed
+-- e.g. not allowed case: FK values {'a', 'A"} both equal to PK value {'A'}
 CREATE TABLE test11pk (x text COLLATE case_insensitive PRIMARY KEY);
-INSERT INTO test11pk VALUES ('abc'), ('def'), ('ghi');
 CREATE TABLE test11fk (x text COLLATE case_sensitive REFERENCES test11pk (x) ON UPDATE CASCADE ON DELETE CASCADE);
-INSERT INTO test11fk VALUES ('abc');  -- ok
-INSERT INTO test11fk VALUES ('ABC');  -- ok
-INSERT INTO test11fk VALUES ('xyz');  -- error
-SELECT * FROM test11pk;
-SELECT * FROM test11fk;
--- cascade update even though the values are "equal" in the PK table
-UPDATE test11pk SET x = 'ABC' WHERE x = 'abc';
-SELECT * FROM test11fk;
-DELETE FROM test11pk WHERE x = 'abc';
-SELECT * FROM test11pk;
-SELECT * FROM test11fk;
+drop table test11pk;
+
+
+
+CREATE TABLE test12pk (x text PRIMARY KEY);
+CREATE TABLE test12fk (x text REFERENCES test12pk on update cascade on delete cascade);
+
+--alter table change primary key's collation to indeterministic, should fail.
+alter table test12pk alter column x set data type text COLLATE ignore_accent_case;
+alter table test12pk alter column x set data type text_d_ignore_accent_case;
+
+---should also fail.
+alter table test12pk alter column x set data type text_d_ignore_accent_case COLLATE "C";
+drop table test12pk,test12fk;
+
+
+
+--alter table change foreign key's collation, all these case should fail.
+CREATE TABLE test13pk (x text PRIMARY KEY);
+CREATE TABLE test13fk (x text REFERENCES test13pk on update cascade on delete cascade);
+alter table test13fk alter column x set data type text COLLATE ignore_accent_case;
+alter table test13fk alter column x set data type text_d_ignore_accent_case;
+alter table test13fk alter column x set data type text_d_ignore_accent_case COLLATE ignore_accent_case;
+alter table test13fk alter column x set data type text_d_ignore_accent_case COLLATE "C";
+drop table test13pk,test13fk;
 
 -- partitioning

base-commit: 31a98934d1699007b50aefaedd68ab4d6b42e3b4
-- 
2.34.1