On 04.09.24 08:54, jian he wrote:
On Tue, Sep 3, 2024 at 5:41 PM Peter Eisentraut <pe...@eisentraut.org> wrote:
I like this patch version (v4). It's the simplest, probably also
easiest to backpatch.
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.
I had thought we could at first do a limited patch that just prevents
the problematic collation changes that Paul pointed out initially, and
then backpatch that, and then develop a more complete solution for
master. But after thinking about this a bit more, such a limited patch
might just be some partial whack-a-mole that would still leave open
problems (as you have pointed out), and it also looks like such a patch
wouldn't be any simpler than the complete solution.
So I took the v5 patch you had posted and started working from there.
The rule that you had picked isn't quite what we want, I think. It's
okay to have nondeterministic collations on foreign keys, as long as the
collation is the same on both sides. That's what I have implemented.
See attached.
This approach also allows cleaning up a bunch of hackiness in
ri_triggers.c, which feels satisfying.
I don't know what to do about backpatching though. The patch itself
appears to backpatch ok. (There are some cosmetic issues that need
manual intervention, but the code structure is pretty consistent going
back.) But that kind of behavior change, I don't know. Also, for the
most part, the existing setup works, it's only if you do some particular
table alterations that you can construct problems.
We could make the error a warning instead, so people know that what they
are building is problematic and deprecated.
But in either case, or even with some of the other approaches discussed
in previous patch versions, such as v4, you'd only get that warning or
error if you do table DDL. If you'd just upgrade the minor release, you
don't get any info. So we'd also have to construct some query to check
for this and create some release note guidance.
So for the moment this is a master-only patch. I think once we have
tied down the behavior we want for the future, we can then see how we
can nudge older versions in that direction.
From 0cdd04de9775adcae2dd6980d3161cf69076643a Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pe...@eisentraut.org>
Date: Thu, 17 Oct 2024 12:25:08 +0200
Subject: [PATCH v6] Fix collation handling for foreign keys
Allowing foreign keys where the referenced and the referencing columns
have collations with different notions of equality is problematic.
This can only happen when using nondeterministic collations, for
example, if the referenced column is case-insensitive and the
referencing column is not, or vice versa. It does not happen if both
collations are deterministic.
XXX maybe some examples here?
When nondeterministic collations were implemented, the SQL standard
available to yours truly said that referential integrity checks should
be performed with the collation of the referenced column, and so
that's how we implemented it. But this turned out to be a mistake in
the SQL standard, for the same reasons as above, that was later
(SQL:2016) fixed to require both collations to be the same. So that's
what we are aiming for here.
We don't have to be quite so strict. We can allow different
collations if they are both deterministic. This is also good for
backward compatibility.
So the new rule is that the collations either have to be the same or
both deterministic. Or in other words, if one of them is
nondeterministic, then both have to be the same.
Some of the nice test cases for the previous situation in
collate.icu.utf8.sql are now obsolete. They are changed to just check
the error checking of the new rule. Note that collate.sql already
contained a test for foreign keys with different deterministic
colllations.
A bunch of code in ri_triggers.c that added a COLLATE clause to
enforce the referenced column's collation can be removed, because both
columns now have to have the same notion of equality, so it doesn't
matter which one to use.
Reported-by: Paul Jungwirth <p...@illuminatedcomputing.com>
Discussion:
https://www.postgresql.org/message-id/flat/78d824e0-b21e-480d-a252-e4b84bc2c...@illuminatedcomputing.com
---
doc/src/sgml/ref/create_table.sgml | 7 ++
src/backend/commands/tablecmds.c | 73 ++++++++++---
src/backend/utils/adt/ri_triggers.c | 55 +++-------
.../regress/expected/collate.icu.utf8.out | 100 ++----------------
src/test/regress/sql/collate.icu.utf8.sql | 35 +-----
5 files changed, 96 insertions(+), 174 deletions(-)
diff --git a/doc/src/sgml/ref/create_table.sgml
b/doc/src/sgml/ref/create_table.sgml
index 83859bac76f..dd7446117bc 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1199,6 +1199,13 @@ <title>Parameters</title>
must have its final column marked <literal>WITHOUT OVERLAPS</literal>.
</para>
+ <para>
+ For each pair of referencing and referenced column, if they are of a
+ collatable data type, then the collations must either be both
+ deterministic or else both the same. This ensures that both columns
+ have a consistent notion of equality.
+ </para>
+
<para>
The user
must have <literal>REFERENCES</literal> permission on the referenced
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1ccc80087c3..85cd2f0f170 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -389,10 +389,10 @@ static ObjectAddress ATExecValidateConstraint(List
**wqueue,
Relation rel, char *constrName,
bool recurse, bool recursing, LOCKMODE lockmode);
static int transformColumnNameList(Oid relId, List *colList,
- int16
*attnums, Oid *atttypids);
+ int16
*attnums, Oid *atttypids, Oid *attcollids);
static int transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
List
**attnamelist,
-
int16 *attnums, Oid *atttypids,
+
int16 *attnums, Oid *atttypids, Oid *attcollids,
Oid
*opclasses, bool *pk_has_without_overlaps);
static Oid transformFkeyCheckAttrs(Relation pkrel,
int
numattrs, int16 *attnums,
@@ -9570,6 +9570,8 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo
*tab, Relation rel,
int16 fkattnum[INDEX_MAX_KEYS] = {0};
Oid pktypoid[INDEX_MAX_KEYS] = {0};
Oid fktypoid[INDEX_MAX_KEYS] = {0};
+ Oid pkcolloid[INDEX_MAX_KEYS] = {0};
+ Oid fkcolloid[INDEX_MAX_KEYS] = {0};
Oid opclasses[INDEX_MAX_KEYS] = {0};
Oid pfeqoperators[INDEX_MAX_KEYS] = {0};
Oid ppeqoperators[INDEX_MAX_KEYS] = {0};
@@ -9670,7 +9672,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo
*tab, Relation rel,
*/
numfks = transformColumnNameList(RelationGetRelid(rel),
fkconstraint->fk_attrs,
-
fkattnum, fktypoid);
+
fkattnum, fktypoid, fkcolloid);
with_period = fkconstraint->fk_with_period ||
fkconstraint->pk_with_period;
if (with_period && !fkconstraint->fk_with_period)
ereport(ERROR,
@@ -9679,7 +9681,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo
*tab, Relation rel,
numfkdelsetcols = transformColumnNameList(RelationGetRelid(rel),
fkconstraint->fk_del_set_cols,
-
fkdelsetcols, NULL);
+
fkdelsetcols, NULL, NULL);
validateFkOnDeleteSetColumns(numfks, fkattnum,
numfkdelsetcols, fkdelsetcols,
fkconstraint->fk_del_set_cols);
@@ -9694,7 +9696,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo
*tab, Relation rel,
{
numpks = transformFkeyGetPrimaryKey(pkrel, &indexOid,
&fkconstraint->pk_attrs,
-
pkattnum, pktypoid,
+
pkattnum, pktypoid, pkcolloid,
opclasses, &pk_has_without_overlaps);
/* If the primary key uses WITHOUT OVERLAPS, the fk must use
PERIOD */
@@ -9707,7 +9709,7 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo
*tab, Relation rel,
{
numpks = transformColumnNameList(RelationGetRelid(pkrel),
fkconstraint->pk_attrs,
-
pkattnum, pktypoid);
+
pkattnum, pktypoid, pkcolloid);
/* Since we got pk_attrs, one should be a period. */
if (with_period && !fkconstraint->pk_with_period)
@@ -9809,6 +9811,8 @@ ATAddForeignKeyConstraint(List **wqueue, AlteredTableInfo
*tab, Relation rel,
Oid pktype = pktypoid[i];
Oid fktype = fktypoid[i];
Oid fktyped;
+ Oid pkcoll = pkcolloid[i];
+ Oid fkcoll = fkcolloid[i];
HeapTuple cla_ht;
Form_pg_opclass cla_tup;
Oid amid;
@@ -9951,6 +9955,40 @@ ATAddForeignKeyConstraint(List **wqueue,
AlteredTableInfo *tab, Relation rel,
format_type_be(fktype),
format_type_be(pktype))));
+ /*
+ * This shouldn't be possible, but better check to make sure we
have a
+ * consistent state for the check below.
+ */
+ if ((pkcoll && !fkcoll) || (!pkcoll && fkcoll))
+ elog(ERROR, "key columns are not both collatable");
+
+ if (pkcoll && fkcoll)
+ {
+ bool pkcolldet;
+ bool fkcolldet;
+
+ pkcolldet = get_collation_isdeterministic(pkcoll);
+ fkcolldet = get_collation_isdeterministic(fkcoll);
+
+ /*
+ * SQL requires that both collations are the same.
This is
+ * because we need a consistent notion of equality on
both
+ * columns. We relax this by allowing different
collations if
+ * they are both deterministic. (This is also for
backward
+ * compatibility, because PostgreSQL has always allowed
this.)
+ */
+ if ((!pkcolldet || !fkcolldet) && pkcoll != fkcoll)
+ ereport(ERROR,
+
(errcode(ERRCODE_COLLATION_MISMATCH),
+ errmsg("foreign key constraint
\"%s\" cannot be implemented", fkconstraint->conname),
+ errdetail("Key columns \"%s\"
and \"%s\" have incompatible collations: \"%s\" and \"%s\". "
+ "If either
collation is nondeterministic, then both collations have to be the same.",
+
strVal(list_nth(fkconstraint->fk_attrs, i)),
+
strVal(list_nth(fkconstraint->pk_attrs, i)),
+
get_collation_name(fkcoll),
+
get_collation_name(pkcoll))));
+ }
+
if (old_check_ok)
{
/*
@@ -9971,6 +10009,8 @@ ATAddForeignKeyConstraint(List **wqueue,
AlteredTableInfo *tab, Relation rel,
CoercionPathType new_pathtype;
Oid old_castfunc;
Oid new_castfunc;
+ Oid old_fkcoll;
+ Oid new_fkcoll;
Form_pg_attribute attr = TupleDescAttr(tab->oldDesc,
fkattnum[i] - 1);
@@ -9986,6 +10026,9 @@ ATAddForeignKeyConstraint(List **wqueue,
AlteredTableInfo *tab, Relation rel,
new_pathtype = findFkeyCast(pfeqop_right, new_fktype,
&new_castfunc);
+ old_fkcoll = attr->attcollation;
+ new_fkcoll = fkcoll;
+
/*
* Upon a change to the cast from the FK column to its
pfeqop
* operand, revalidate the constraint. For this
evaluation, a
@@ -10009,9 +10052,10 @@ 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.
+ * If the collation changes, revalidation is required,
unless both
+ * collations are deterministic, because those share
the same
+ * notion of equality (because texteq reduces to bitwise
+ * equality).
*
* We need not directly consider the PK type. It's
necessarily
* binary coercible to the opcintype of the unique
index column,
@@ -10021,7 +10065,9 @@ ATAddForeignKeyConstraint(List **wqueue,
AlteredTableInfo *tab, Relation rel,
old_check_ok = (new_pathtype == old_pathtype &&
new_castfunc ==
old_castfunc &&
(!IsPolymorphicType(pfeqop_right) ||
- new_fktype ==
old_fktype));
+ new_fktype ==
old_fktype) &&
+ (new_fkcoll ==
old_fkcoll ||
+
(get_collation_isdeterministic(old_fkcoll) &&
get_collation_isdeterministic(new_fkcoll))));
}
pfeqoperators[i] = pfeqop;
@@ -11883,7 +11929,7 @@ ATExecValidateConstraint(List **wqueue, Relation rel,
char *constrName,
*/
static int
transformColumnNameList(Oid relId, List *colList,
- int16 *attnums, Oid *atttypids)
+ int16 *attnums, Oid *atttypids,
Oid *attcollids)
{
ListCell *l;
int attnum;
@@ -11914,6 +11960,8 @@ transformColumnNameList(Oid relId, List *colList,
attnums[attnum] = attform->attnum;
if (atttypids != NULL)
atttypids[attnum] = attform->atttypid;
+ if (attcollids != NULL)
+ attcollids[attnum] = attform->attcollation;
ReleaseSysCache(atttuple);
attnum++;
}
@@ -11937,7 +11985,7 @@ transformColumnNameList(Oid relId, List *colList,
static int
transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
List **attnamelist,
- int16 *attnums, Oid
*atttypids,
+ int16 *attnums, Oid
*atttypids, Oid *attcollids,
Oid *opclasses, bool
*pk_has_without_overlaps)
{
List *indexoidlist;
@@ -12011,6 +12059,7 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid
*indexOid,
attnums[i] = pkattno;
atttypids[i] = attnumTypeId(pkrel, pkattno);
+ attcollids[i] = attnumCollationId(pkrel, pkattno);
opclasses[i] = indclass->values[i];
*attnamelist = lappend(*attnamelist,
makeString(pstrdup(NameStr(*attnumAttName(pkrel, pkattno)))));
diff --git a/src/backend/utils/adt/ri_triggers.c
b/src/backend/utils/adt/ri_triggers.c
index 6896e1ae638..1738cb3371b 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -207,7 +207,7 @@ static void ri_BuildQueryKey(RI_QueryKey *key,
int32 constr_queryno);
static bool ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot
*newslot,
const RI_ConstraintInfo
*riinfo, bool rel_is_pk);
-static bool ri_CompareWithCast(Oid eq_opr, Oid typeid,
+static bool ri_CompareWithCast(Oid eq_opr, Oid typeid, Oid collid,
Datum lhs, Datum
rhs);
static void ri_InitHashTables(void);
@@ -776,8 +776,6 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
{
Oid pk_type = RIAttType(pk_rel,
riinfo->pk_attnums[i]);
Oid fk_type = RIAttType(fk_rel,
riinfo->fk_attnums[i]);
- Oid pk_coll =
RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll =
RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
quoteOneName(attname,
RIAttName(fk_rel,
riinfo->fk_attnums[i]));
@@ -786,8 +784,6 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
paramname, pk_type,
riinfo->pf_eq_oprs[i],
attname, fk_type);
- if (pk_coll != fk_coll &&
!get_collation_isdeterministic(pk_coll))
- ri_GenerateQualCollation(&querybuf, pk_coll);
querysep = "AND";
queryoids[i] = pk_type;
}
@@ -881,8 +877,6 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
{
Oid pk_type = RIAttType(pk_rel,
riinfo->pk_attnums[i]);
Oid fk_type = RIAttType(fk_rel,
riinfo->fk_attnums[i]);
- Oid pk_coll =
RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll =
RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
quoteOneName(attname,
RIAttName(fk_rel,
riinfo->fk_attnums[i]));
@@ -891,8 +885,6 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
paramname, pk_type,
riinfo->pf_eq_oprs[i],
attname, fk_type);
- if (pk_coll != fk_coll &&
!get_collation_isdeterministic(pk_coll))
- ri_GenerateQualCollation(&querybuf, pk_coll);
querysep = "AND";
queryoids[i] = pk_type;
}
@@ -996,8 +988,6 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
{
Oid pk_type = RIAttType(pk_rel,
riinfo->pk_attnums[i]);
Oid fk_type = RIAttType(fk_rel,
riinfo->fk_attnums[i]);
- Oid pk_coll =
RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll =
RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
quoteOneName(attname,
RIAttName(fk_rel,
riinfo->fk_attnums[i]));
@@ -1009,8 +999,6 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
paramname, pk_type,
riinfo->pf_eq_oprs[i],
attname, fk_type);
- if (pk_coll != fk_coll &&
!get_collation_isdeterministic(pk_coll))
- ri_GenerateQualCollation(&querybuf, pk_coll);
querysep = ",";
qualsep = "AND";
queryoids[i] = pk_type;
@@ -1232,8 +1220,6 @@ ri_set(TriggerData *trigdata, bool is_set_null, int
tgkind)
{
Oid pk_type = RIAttType(pk_rel,
riinfo->pk_attnums[i]);
Oid fk_type = RIAttType(fk_rel,
riinfo->fk_attnums[i]);
- Oid pk_coll =
RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll =
RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
quoteOneName(attname,
RIAttName(fk_rel,
riinfo->fk_attnums[i]));
@@ -1243,8 +1229,6 @@ ri_set(TriggerData *trigdata, bool is_set_null, int
tgkind)
paramname, pk_type,
riinfo->pf_eq_oprs[i],
attname, fk_type);
- if (pk_coll != fk_coll &&
!get_collation_isdeterministic(pk_coll))
- ri_GenerateQualCollation(&querybuf, pk_coll);
qualsep = "AND";
queryoids[i] = pk_type;
}
@@ -1998,19 +1982,16 @@ ri_GenerateQual(StringInfo buf,
/*
* ri_GenerateQualCollation --- add a COLLATE spec to a WHERE clause
*
- * At present, we intentionally do not use this function for RI queries that
- * compare a variable to a $n parameter. Since parameter symbols always have
- * default collation, the effect will be to use the variable's collation.
- * Now that is only strictly correct when testing the referenced column, since
- * the SQL standard specifies that RI comparisons should use the referenced
- * column's collation. However, so long as all collations have the same
- * notion of equality (which they do, because texteq reduces to bitwise
- * equality), there's no visible semantic impact from using the referencing
- * column's collation when testing it, and this is a good thing to do because
- * it lets us use a normal index on the referencing column. However, we do
- * have to use this function when directly comparing the referencing and
- * referenced columns, if they are of different collations; else the parser
- * will fail to resolve the collation to use.
+ * We only have to use this function when directly comparing the referencing
+ * and referenced columns, if they are of different collations; else the
+ * parser will fail to resolve the collation to use. We don't need to use
+ * this function for RI queries that compare a variable to a $n parameter.
+ * Since parameter symbols always have default collation, the effect will be
+ * to use the variable's collation.
+ *
+ * Note that we require that the collations of the referencing and the
+ * referenced column have the some notion of equality: Either they have to
+ * both be deterministic or else they both have to be the same.
*/
static void
ri_GenerateQualCollation(StringInfo buf, Oid collation)
@@ -2952,7 +2933,7 @@ ri_KeysEqual(Relation rel, TupleTableSlot *oldslot,
TupleTableSlot *newslot,
* operator. Changes that compare equal will still
satisfy the
* constraint after the update.
*/
- if (!ri_CompareWithCast(eq_opr, RIAttType(rel,
attnums[i]),
+ if (!ri_CompareWithCast(eq_opr, RIAttType(rel,
attnums[i]), RIAttCollation(rel, attnums[i]),
newvalue, oldvalue))
return false;
}
@@ -2972,7 +2953,7 @@ ri_KeysEqual(Relation rel, TupleTableSlot *oldslot,
TupleTableSlot *newslot,
* NB: we have already checked that neither value is null.
*/
static bool
-ri_CompareWithCast(Oid eq_opr, Oid typeid,
+ri_CompareWithCast(Oid eq_opr, Oid typeid, Oid collid,
Datum lhs, Datum rhs)
{
RI_CompareHashEntry *entry = ri_HashCompareOp(eq_opr, typeid);
@@ -2998,9 +2979,9 @@ ri_CompareWithCast(Oid eq_opr, Oid typeid,
* on the other side of a foreign-key constraint. Therefore, the
* comparison here would need to be done with the collation of the
*other*
* table. For simplicity (e.g., we might not even have the other table
- * open), we'll just use the default collation here, which could lead to
- * some false negatives. All this would break if we ever allow
- * database-wide collations to be nondeterministic.
+ * open), we'll use our own collation. This is fine because we require
+ * that both collations have the same notion of equality (either they
are
+ * both deterministic or else they are both the same).
*
* With range/multirangetypes, the collation of the base type is stored
as
* part of the rangetype (pg_range.rngcollation), and always used, so
@@ -3008,9 +2989,7 @@ ri_CompareWithCast(Oid eq_opr, Oid typeid,
* But if we support arbitrary types with PERIOD, we should perhaps just
* always force a re-check.
*/
- return DatumGetBool(FunctionCall2Coll(&entry->eq_opr_finfo,
-
DEFAULT_COLLATION_OID,
-
lhs, rhs));
+ return DatumGetBool(FunctionCall2Coll(&entry->eq_opr_finfo, collid,
lhs, rhs));
}
/*
diff --git a/src/test/regress/expected/collate.icu.utf8.out
b/src/test/regress/expected/collate.icu.utf8.out
index faa376e060c..7bfdad5b099 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1840,101 +1840,15 @@ SELECT * FROM test4 WHERE b = 'Cote' COLLATE
case_insensitive;
1 | cote
(1 row)
--- foreign keys (should use collation of primary key)
--- PK is case-sensitive, FK is case-insensitive
+-- foreign keys (mixing different nondeterministic collations not allowed)
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
+CREATE TABLE test10fk (x text COLLATE case_insensitive REFERENCES test10pk (x)
ON UPDATE CASCADE ON DELETE CASCADE); -- error
+ERROR: foreign key constraint "test10fk_x_fkey" cannot be implemented
+DETAIL: Key columns "x" and "x" have incompatible collations:
"case_insensitive" and "case_sensitive". If either collation is
nondeterministic, then both collations have to be the same.
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)
-
+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" and "x" have incompatible collations:
"case_sensitive" and "case_insensitive". If either collation is
nondeterministic, then both collations have to be the same.
-- 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 80f28a97d78..31f6aff0acd 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -687,39 +687,12 @@ CREATE TABLE test4 (a int, b text);
SELECT * FROM test4 WHERE b = 'Cote' COLLATE ignore_accents; -- still
case-sensitive
SELECT * FROM test4 WHERE b = 'Cote' COLLATE case_insensitive;
--- foreign keys (should use collation of primary key)
-
--- PK is case-sensitive, FK is case-insensitive
+-- foreign keys (mixing different nondeterministic collations not allowed)
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
+CREATE TABLE test10fk (x text COLLATE case_insensitive REFERENCES test10pk (x)
ON UPDATE CASCADE ON DELETE CASCADE); -- error
+
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;
+CREATE TABLE test11fk (x text COLLATE case_sensitive REFERENCES test11pk (x)
ON UPDATE CASCADE ON DELETE CASCADE); -- error
-- partitioning
CREATE TABLE test20 (a int, b text COLLATE case_insensitive) PARTITION BY LIST
(b);
base-commit: 665785d85f8b9a7250802d7fadd84841bb0aafa1
--
2.47.0
