domain check constraint should also consider domain's collation
Started by jian he6 months ago2 messages
hi.
CREATE COLLATION case_insensitive (provider = icu, locale =
'@colStrength=secondary', deterministic = false);
SELECT 'a' = 'A' COLLATE case_insensitive;
CREATE DOMAIN d1 as text collate case_insensitive check (value <> 'a');
SELECT 'A'::d1;
``SELECT 'A'::d1`` should error out as domain check constraint not satisfied?
If so, attached is the POC trying to implement it.
Attachments:
v1-0001-CoerceToDomainValue-should-use-domain-s-collation.patchtext/x-patch; charset=UTF-8; name=v1-0001-CoerceToDomainValue-should-use-domain-s-collation.patchDownload
From 8d0b94976c5c1c0cf16f70f7d740ea4f37698340 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 14 Jul 2025 16:14:32 +0800
Subject: [PATCH v1 1/1] CoerceToDomainValue should use domain's collation
---
src/backend/commands/typecmds.c | 15 ++++++++-----
.../regress/expected/collate.icu.utf8.out | 21 +++++++++++++++++++
src/test/regress/sql/collate.icu.utf8.sql | 10 +++++++++
3 files changed, 41 insertions(+), 5 deletions(-)
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 26d985193ae..529001d7bfc 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -133,7 +133,8 @@ static void checkEnumOwner(HeapTuple tup);
static char *domainAddCheckConstraint(Oid domainOid, Oid domainNamespace,
Oid baseTypeOid,
int typMod, Constraint *constr,
- const char *domainName, ObjectAddress *constrAddr);
+ const char *domainName, Oid domaincoll,
+ ObjectAddress *constrAddr);
static Node *replace_domain_constraint_value(ParseState *pstate,
ColumnRef *cref);
static void domainAddNotNullConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
@@ -1145,7 +1146,7 @@ DefineDomain(ParseState *pstate, CreateDomainStmt *stmt)
case CONSTR_CHECK:
domainAddCheckConstraint(address.objectId, domainNamespace,
basetypeoid, basetypeMod,
- constr, domainName, NULL);
+ constr, domainName, domaincoll, NULL);
break;
case CONSTR_NOTNULL:
@@ -2937,6 +2938,7 @@ AlterDomainAddConstraint(List *names, Node *newConstraint,
{
TypeName *typename;
Oid domainoid;
+ Oid domaincoll;
Relation typrel;
HeapTuple tup;
Form_pg_type typTup;
@@ -2948,6 +2950,8 @@ AlterDomainAddConstraint(List *names, Node *newConstraint,
typename = makeTypeNameFromNameList(names);
domainoid = typenameTypeId(NULL, typename);
+ domaincoll = get_typcollation(domainoid);
+
/* Look up the domain in the type table */
typrel = table_open(TypeRelationId, RowExclusiveLock);
@@ -2977,7 +2981,7 @@ AlterDomainAddConstraint(List *names, Node *newConstraint,
ccbin = domainAddCheckConstraint(domainoid, typTup->typnamespace,
typTup->typbasetype, typTup->typtypmod,
- constr, NameStr(typTup->typname), constrAddr);
+ constr, NameStr(typTup->typname), domaincoll, constrAddr);
/*
@@ -3504,7 +3508,8 @@ checkDomainOwner(HeapTuple tup)
static char *
domainAddCheckConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
int typMod, Constraint *constr,
- const char *domainName, ObjectAddress *constrAddr)
+ const char *domainName, Oid domaincoll,
+ ObjectAddress *constrAddr)
{
Node *expr;
char *ccbin;
@@ -3549,7 +3554,7 @@ domainAddCheckConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid,
domVal = makeNode(CoerceToDomainValue);
domVal->typeId = baseTypeOid;
domVal->typeMod = typMod;
- domVal->collation = get_typcollation(baseTypeOid);
+ domVal->collation = domaincoll;
domVal->location = -1; /* will be set when/if used */
pstate->p_pre_columnref_hook = replace_domain_constraint_value;
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index 69805d4b9ec..002e61c1e0e 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -1459,6 +1459,27 @@ CREATE COLLATION case_sensitive (provider = icu, locale = '');
NOTICE: using standard form "und" for ICU locale ""
CREATE COLLATION case_insensitive (provider = icu, locale = '@colStrength=secondary', deterministic = false);
NOTICE: using standard form "und-u-ks-level2" for ICU locale "@colStrength=secondary"
+--domain with check constraints
+CREATE DOMAIN d0 as text COLLATE "C" check (value <> 'a');
+CREATE DOMAIN d1 as d0 COLLATE case_insensitive check (value <> 'b');
+CREATE DOMAIN d2 as d0 COLLATE case_insensitive;
+SELECT 'A'::d1; --ok
+ d1
+----
+ A
+(1 row)
+
+SELECT 'a'::d1; --error
+ERROR: value for domain d1 violates check constraint "d0_check"
+SELECT 'B'::d1; --error
+ERROR: value for domain d1 violates check constraint "d1_check"
+SELECT 'A'::d2; --ok
+ d2
+----
+ A
+(1 row)
+
+DROP DOMAIN d0, d1, d2;
SELECT 'abc' <= 'ABC' COLLATE case_sensitive, 'abc' >= 'ABC' COLLATE case_sensitive;
?column? | ?column?
----------+----------
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index dbc190227d0..d50f75fb819 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -561,6 +561,16 @@ SELECT * FROM test6a WHERE b = ARRAY['äbc'] COLLATE ctest_nondet;
CREATE COLLATION case_sensitive (provider = icu, locale = '');
CREATE COLLATION case_insensitive (provider = icu, locale = '@colStrength=secondary', deterministic = false);
+--domain with check constraints
+CREATE DOMAIN d0 as text COLLATE "C" check (value <> 'a');
+CREATE DOMAIN d1 as d0 COLLATE case_insensitive check (value <> 'b');
+CREATE DOMAIN d2 as d0 COLLATE case_insensitive;
+SELECT 'A'::d1; --ok
+SELECT 'a'::d1; --error
+SELECT 'B'::d1; --error
+SELECT 'A'::d2; --ok
+DROP DOMAIN d0, d1, d2;
+
SELECT 'abc' <= 'ABC' COLLATE case_sensitive, 'abc' >= 'ABC' COLLATE case_sensitive;
SELECT 'abc' <= 'ABC' COLLATE case_insensitive, 'abc' >= 'ABC' COLLATE case_insensitive;
--
2.34.1
Re: domain check constraint should also consider domain's collation
jian he <jian.universality@gmail.com> writes:
CREATE COLLATION case_insensitive (provider = icu, locale =
'@colStrength=secondary', deterministic = false);
SELECT 'a' = 'A' COLLATE case_insensitive;
CREATE DOMAIN d1 as text collate case_insensitive check (value <> 'a');
SELECT 'A'::d1;
``SELECT 'A'::d1`` should error out as domain check constraint not satisfied?
No. In the above, 'value' is of type text, not type d1, and therefore
that comparison will use the default collation. If you try to make it
do something else, you will break far more than you fix. (The
fundamental reason why this is important is that we cannot assume that
the domain constraints hold for the value until after we complete the
CHECK expressions.) So the correct way to create a domain that works
as you have in mind is
CREATE DOMAIN d1 as text collate case_insensitive
check (value <> 'a' COLLATE case_insensitive);
regards, tom lane