On Tue, Aug 19, 2025 at 10:08 PM jian he <[email protected]> wrote:
>
> 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;
>
> Should the four statements above result in an error?
> This only happens with UPDATE, since INSERT will check with domain
> invalid constraints.
the main idea is that
if we find out that a Var Node type is domain with invalid constraint
then we convert the
Var to CoerceToDomain node.
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)
as you can see from the "Output:", column "c" is also here,
In rewriteTargetListIU, In rewriteTargetListIU, I use makeTargetEntry to produce
a new TargetEntry for column c, set its expr to a CoerceToDomain node, and set
resjunk to true.
From efb33eac3599773402446d2812ec544313c65e29 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
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/[email protected]
---
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
