UPDATE with invalid domain constraint

From efb33eac3599773402446d2812ec544313c65e29 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Wed, 20 Aug 2025 10:59:18 +0800
Subject: [PATCH v1 1/1] UPDATE with invalid domain constraint
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

should UPDATE statement need to verify that the domain value is satisfied with
invalid domain constraints?
Álvaro Herrera already mentioned this in [1], but I just want to confirm it.

drop table if exists dt1;
drop domain if exists d1;
create domain d1 as int;
create table dt1(i int, c d1);
insert into dt1 values(1,2);
alter domain d1 add constraint cc check(value <> 2) not valid;

update dt1 set i = i + 1;
update dt1 set c = c;
update dt1 set i = i + 1, c = c;
update dt1 set i = i + 1, c = c::d1;
This only happens with UPDATE, since INSERT will also check with domain invalid constraints.

[1]: https://postgr.es/m/202508140957.4daktvyr7xiw@alvherre.pgsql
---
 src/backend/rewrite/rewriteHandler.c | 49 ++++++++++++++++++++++++++++
 src/backend/utils/cache/typcache.c   | 19 +++++++++++
 src/include/utils/typcache.h         |  3 ++
 src/test/regress/expected/domain.out | 25 ++++++++++++++
 src/test/regress/sql/domain.sql      | 18 ++++++++++
 5 files changed, 114 insertions(+)

diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index adc9e7600e1..7eef5e57f89 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -730,6 +730,32 @@ adjustJoinTreeList(Query *parsetree, bool removert, int rt_index)
 }
 
 
+static Node *
+ConvertVarToCoerceToDomain(Var *var)
+{
+	Oid			baseTypeId;
+	int32		baseTypeMod;
+	Node	   *result;
+
+	baseTypeMod = var->vartypmod;
+	baseTypeId = getBaseTypeAndTypmod(var->vartype, &baseTypeMod);
+	if (baseTypeId != var->vartype &&
+		DomainHasInvalidConstraints(var->vartype))
+	{
+		result = coerce_to_domain((Node *) var,
+								  baseTypeId, baseTypeMod,
+								  var->vartype,
+								  COERCION_IMPLICIT,
+								  COERCE_IMPLICIT_CAST,
+								  -1,
+								  false);
+	}
+	else
+		result = (Node *) var;
+
+	return result;
+}
+
 /*
  * rewriteTargetListIU - rewrite INSERT/UPDATE targetlist into standard form
  *
@@ -806,6 +832,7 @@ rewriteTargetListIU(List *targetList,
 
 		if (!old_tle->resjunk)
 		{
+			Node	   *node = (Node *) old_tle->expr;
 			/* Normal attr: stash it into new_tles[] */
 			attrno = old_tle->resno;
 			if (attrno < 1 || attrno > numattrs)
@@ -816,6 +843,13 @@ rewriteTargetListIU(List *targetList,
 			if (att_tup->attisdropped)
 				continue;
 
+			if (IsA(node, Var))
+			{
+				Node 	*result;
+				result = ConvertVarToCoerceToDomain((Var *) node);
+
+				old_tle->expr = (Expr *) result;
+			}
 			/* Merge with any prior assignment to same attribute */
 			new_tles[attrno - 1] =
 				process_matched_tle(old_tle,
@@ -984,6 +1018,21 @@ rewriteTargetListIU(List *targetList,
 								NameStr(att_tup->attname)),
 						 errdetail("Column \"%s\" is a generated column.",
 								   NameStr(att_tup->attname))));
+			if (!new_tle && !att_tup->attgenerated && !apply_default)
+			{
+				Node 	*result;
+				Var		   *var;
+
+				var = makeVar(1, att_tup->attnum, att_tup->atttypid,
+							  att_tup->atttypmod, att_tup->attcollation, 0);
+
+				result = ConvertVarToCoerceToDomain(var);
+				if (IsA(result, CoerceToDomain))
+					new_tle = makeTargetEntry((Expr *) result,
+											  att_tup->attnum,
+											  NameStr(att_tup->attname),
+											  true);
+			}
 		}
 
 		if (att_tup->attgenerated)
diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c
index 6a347698edf..196a4355fd8 100644
--- a/src/backend/utils/cache/typcache.c
+++ b/src/backend/utils/cache/typcache.c
@@ -510,6 +510,7 @@ lookup_type_cache(Oid type_id, int flags)
 			firstDomainTypeEntry = typentry;
 		}
 
+		typentry->invalidDomainConstr = false;
 		ReleaseSysCache(tp);
 	}
 	else if (!(typentry->flags & TCFLAGS_HAVE_PG_TYPE_DATA))
@@ -1164,6 +1165,9 @@ load_domaintype_info(TypeCacheEntry *typentry)
 			if (c->contype != CONSTRAINT_CHECK)
 				continue;
 
+			if (!c->convalidated)
+				typentry->invalidDomainConstr = true;
+
 			/* Not expecting conbin to be NULL, but we'll test for it anyway */
 			val = fastgetattr(conTup, Anum_pg_constraint_conbin,
 							  conRel->rd_att, &isNull);
@@ -1499,6 +1503,21 @@ DomainHasConstraints(Oid type_id)
 }
 
 
+bool
+DomainHasInvalidConstraints(Oid type_id)
+{
+	TypeCacheEntry *typentry;
+
+	/*
+	 * Note: a side effect is to cause the typcache's domain data to become
+	 * valid.  This is fine since we'll likely need it soon if there is any.
+	 */
+	typentry = lookup_type_cache(type_id, TYPECACHE_DOMAIN_CONSTR_INFO);
+
+	return typentry->invalidDomainConstr;
+}
+
+
 /*
  * array_element_has_equality and friends are helper routines to check
  * whether we should believe that array_eq and related functions will work
diff --git a/src/include/utils/typcache.h b/src/include/utils/typcache.h
index 1cb30f1818c..dcaff52b1b2 100644
--- a/src/include/utils/typcache.h
+++ b/src/include/utils/typcache.h
@@ -121,6 +121,8 @@ typedef struct TypeCacheEntry
 	 */
 	DomainConstraintCache *domainData;
 
+	bool		invalidDomainConstr;
+
 	/* Private data, for internal use of typcache.c only */
 	int			flags;			/* flags about what we've computed */
 
@@ -184,6 +186,7 @@ extern void InitDomainConstraintRef(Oid type_id, DomainConstraintRef *ref,
 extern void UpdateDomainConstraintRef(DomainConstraintRef *ref);
 
 extern bool DomainHasConstraints(Oid type_id);
+extern bool DomainHasInvalidConstraints(Oid type_id);
 
 extern TupleDesc lookup_rowtype_tupdesc(Oid type_id, int32 typmod);
 
diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out
index b5ea707df31..ed1ae82a5d1 100644
--- a/src/test/regress/expected/domain.out
+++ b/src/test/regress/expected/domain.out
@@ -926,6 +926,31 @@ ALTER DOMAIN things VALIDATE CONSTRAINT meow;
 ERROR:  column "stuff" of table "thethings" contains values that violate the new constraint
 UPDATE thethings SET stuff = 10;
 ALTER DOMAIN things VALIDATE CONSTRAINT meow;
+create domain d1 as int;
+create table dt1(i int, c d1, d int);
+insert into dt1 values(1,2);
+alter domain d1 add constraint cc check(value <> 2) not valid;
+explain (verbose, costs off) update dt1 set i = i + 1;
+            QUERY PLAN            
+----------------------------------
+ Update on public.dt1
+   ->  Seq Scan on public.dt1
+         Output: (i + 1), c, ctid
+(3 rows)
+
+update dt1 set i = i + 1; --error
+ERROR:  value for domain d1 violates check constraint "cc"
+update dt1 set i = i + 1, c = c; --error
+ERROR:  value for domain d1 violates check constraint "cc"
+update dt1 set i = i + 1, c = c::d1; --error
+ERROR:  value for domain d1 violates check constraint "cc"
+update dt1 set i = i + 1, c = 2; --error
+ERROR:  value for domain d1 violates check constraint "cc"
+truncate dt1;
+insert into dt1 values(1,3);
+update dt1 set i = i + 1; --ok
+drop table dt1;
+drop domain d1;
 -- Confirm ALTER DOMAIN with RULES.
 create table domtab (col1 integer);
 create domain dom as integer;
diff --git a/src/test/regress/sql/domain.sql b/src/test/regress/sql/domain.sql
index b8f5a639712..11edc293346 100644
--- a/src/test/regress/sql/domain.sql
+++ b/src/test/regress/sql/domain.sql
@@ -537,6 +537,24 @@ ALTER DOMAIN things VALIDATE CONSTRAINT meow;
 UPDATE thethings SET stuff = 10;
 ALTER DOMAIN things VALIDATE CONSTRAINT meow;
 
+create domain d1 as int;
+create table dt1(i int, c d1, d int);
+insert into dt1 values(1,2);
+alter domain d1 add constraint cc check(value <> 2) not valid;
+explain (verbose, costs off) update dt1 set i = i + 1;
+
+update dt1 set i = i + 1; --error
+update dt1 set i = i + 1, c = c; --error
+update dt1 set i = i + 1, c = c::d1; --error
+update dt1 set i = i + 1, c = 2; --error
+
+truncate dt1;
+insert into dt1 values(1,3);
+update dt1 set i = i + 1; --ok
+
+drop table dt1;
+drop domain d1;
+
 -- Confirm ALTER DOMAIN with RULES.
 create table domtab (col1 integer);
 create domain dom as integer;
-- 
2.34.1