On Mon, Sep 1, 2025 at 2:27 PM jian he <[email protected]> wrote: > > summary of the attached v7. > v7-0001, v7-00002: preparatory patch. > v7-0003 adds fast default support for ALTER TABLE ADD COLUMN when the domain > has > non-volatile constraints. > A table rewrite is still required for domains with volatile constraints. > > v7-0004 skip table rewrite (table scan only) for ALTER TABLE ADD > COLUMN with domains has volatile constraints. >
Hi. rebase, and further simplified. maybe we could perform a table scan for ALTER TABLE ADD COLUMN when the domain has volatile constraints like v7-0004, avoiding a table rewrite. However, this approach feels inelegant, so I do not plan to pursue it. So, the fast default now applies to domains with non-volatile constraint expressions only. Regarding the prior discussion about empty table behavior. This patch is consistent with the master: not throwing an error if the default would fail the domain constraints. -- jian https://www.enterprisedb.com/
From e797885a884cfda7e13eb237c277ad216710cc94 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Mon, 26 Jan 2026 15:05:56 +0800 Subject: [PATCH v8 2/3] add function DomainHaveVolatileConstraints Returns true if the Domain has any constraints. If you want check this domain have any volatile check constraints, make sure argument have_volatile is not NULL. We can avoid a table rewrite when adding or modifying a column with a constrained domain, but only if the domain's constraints are not volatile. Therefore function DomainHaveVolatileConstraints is necessary to check constraint volatility. discussion: https://postgr.es/m/cacjufxe_+izbr1i49k_ahigpppwltji6km8nosc7fwvkdem...@mail.gmail.com commitfest: https://commitfest.postgresql.org/patch/5641 --- src/backend/utils/cache/typcache.c | 37 ++++++++++++++++++++++++++++++ src/include/utils/typcache.h | 1 + 2 files changed, 38 insertions(+) diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c index dc4b1a56414..d00cfd471ae 100644 --- a/src/backend/utils/cache/typcache.c +++ b/src/backend/utils/cache/typcache.c @@ -1499,6 +1499,43 @@ DomainHasConstraints(Oid type_id) } +/* + * Check whether a domain has any constraints, and determine if any of those + * constraints contain volatile expressions. + * + * To detect volatile expressions within domain check constraints, ensure that + * have_volatile is not NULL. If have_volatile is NULL, this function behave the + * same as DomainHasConstraints. + */ +bool +DomainHaveVolatileConstraints(Oid type_id, bool *have_volatile) +{ + /* + * 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. + */ + TypeCacheEntry *typentry = lookup_type_cache(type_id, TYPECACHE_DOMAIN_CONSTR_INFO); + + if (typentry->domainData != NULL) + { + foreach_node(DomainConstraintState, constrstate, typentry->domainData->constraints) + { + if (constrstate->constrainttype == DOM_CONSTRAINT_CHECK && + contain_volatile_functions((Node *) constrstate->check_expr)) + { + if (have_volatile) + *have_volatile = true; + + break; + } + } + + return true; + } + + return false; +} + /* * 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 0e3945aa244..d9e894b1146 100644 --- a/src/include/utils/typcache.h +++ b/src/include/utils/typcache.h @@ -184,6 +184,7 @@ extern void InitDomainConstraintRef(Oid type_id, DomainConstraintRef *ref, extern void UpdateDomainConstraintRef(DomainConstraintRef *ref); extern bool DomainHasConstraints(Oid type_id); +extern bool DomainHaveVolatileConstraints(Oid type_id, bool *have_volatile); extern TupleDesc lookup_rowtype_tupdesc(Oid type_id, int32 typmod); -- 2.34.1
From 1a37a56dfe6c807a8d61be3968120cf7fc4811de Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Mon, 26 Jan 2026 14:52:23 +0800 Subject: [PATCH v8 1/3] Enable soft error handling in ExecPrepareExpr and ExecInitExpr discussion: https://postgr.es/m/cacjufxe_+izbr1i49k_ahigpppwltji6km8nosc7fwvkdem...@mail.gmail.com commitfest: https://commitfest.postgresql.org/patch/5641 --- src/backend/executor/execExpr.c | 21 ++++++++++++++++++++- src/include/executor/executor.h | 2 ++ 2 files changed, 22 insertions(+), 1 deletion(-) diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index 088eca24021..765e90494a5 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -141,6 +141,18 @@ static void ExecInitJsonCoercion(ExprState *state, JsonReturning *returning, */ ExprState * ExecInitExpr(Expr *node, PlanState *parent) +{ + return ExecInitExprExtended(node, parent, NULL); +} + +/* + * 'escontext' is expected to be non-NULL only for expression nodes that support + * soft errors. + * + * Not all expression nodes support this; if in doubt, pass NULL. + */ +ExprState * +ExecInitExprExtended(Expr *node, PlanState *parent, Node *escontext) { ExprState *state; ExprEvalStep scratch = {0}; @@ -154,6 +166,7 @@ ExecInitExpr(Expr *node, PlanState *parent) state->expr = node; state->parent = parent; state->ext_params = NULL; + state->escontext = (ErrorSaveContext *) escontext; /* Insert setup steps as needed */ ExecCreateExprSetupSteps(state, (Node *) node); @@ -763,6 +776,12 @@ ExecBuildUpdateProjection(List *targetList, */ ExprState * ExecPrepareExpr(Expr *node, EState *estate) +{ + return ExecPrepareExprExtended(node, estate, NULL); +} + +ExprState * +ExecPrepareExprExtended(Expr *node, EState *estate, Node *escontext) { ExprState *result; MemoryContext oldcontext; @@ -771,7 +790,7 @@ ExecPrepareExpr(Expr *node, EState *estate) node = expression_planner(node); - result = ExecInitExpr(node, NULL); + result = ExecInitExprExtended(node, NULL, escontext); MemoryContextSwitchTo(oldcontext); diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h index 5929aabc353..a53d60107cb 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -324,6 +324,7 @@ ExecProcNode(PlanState *node) * prototypes from functions in execExpr.c */ extern ExprState *ExecInitExpr(Expr *node, PlanState *parent); +extern ExprState *ExecInitExprExtended(Expr *node, PlanState *parent, Node *escontext); extern ExprState *ExecInitExprWithParams(Expr *node, ParamListInfo ext_params); extern ExprState *ExecInitQual(List *qual, PlanState *parent); extern ExprState *ExecInitCheck(List *qual, PlanState *parent); @@ -372,6 +373,7 @@ extern ProjectionInfo *ExecBuildUpdateProjection(List *targetList, TupleTableSlot *slot, PlanState *parent); extern ExprState *ExecPrepareExpr(Expr *node, EState *estate); +extern ExprState *ExecPrepareExprExtended(Expr *node, EState *estate, Node *escontext); extern ExprState *ExecPrepareQual(List *qual, EState *estate); extern ExprState *ExecPrepareCheck(List *qual, EState *estate); extern List *ExecPrepareExprList(List *nodes, EState *estate); -- 2.34.1
From 8f7b47f7c421ec32248c98d5ee68eb9fa272579e Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Mon, 26 Jan 2026 15:42:19 +0800 Subject: [PATCH v8 3/3] fast default for domain with constraints This is primarily done by evaluating CoerceToDomain with soft error support. If CoerceToDomain is evaluated as false in ATExecAddColumn, the defval node's value cannot be cast to the domain type. However, in some cases like when the table is empty, we cannot explicitly error out in ATExecAddColumn (Phase 2). For example, imagine add a new domain column to empty x, and the column domain specification is ``CHECK(value > 10) DEFAULT 8``. In such situations, the ALTER TABLE ADD COLUMN should be success. Thanks to commit aaaf9449ec6be62cb0d30ed3588dc384f56274bf[1], ExprState.escontext (ErrorSaveContext) was added, and ExecEvalConstraintNotNull, ExecEvalConstraintCheck were changed to use errsave instead of hard error. Now we can evaluate CoerceToDomain in a soft error way. However we do table rewrite for domain with volatile check constraints. discussion: https://postgr.es/m/cacjufxe_+izbr1i49k_ahigpppwltji6km8nosc7fwvkdem...@mail.gmail.com commitfest: https://commitfest.postgresql.org/patch/5641 --- src/backend/commands/tablecmds.c | 48 ++++++++++++++------- src/test/regress/expected/fast_default.out | 50 ++++++++++++++++++++++ src/test/regress/sql/fast_default.sql | 40 +++++++++++++++++ 3 files changed, 122 insertions(+), 16 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index f976c0e5c7e..7130bdc6460 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -7468,15 +7468,6 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, * NULL if so, so without any modification of the tuple data we will get * the effect of NULL values in the new column. * - * An exception occurs when the new column is of a domain type: the domain - * might have a not-null constraint, or a check constraint that indirectly - * rejects nulls. If there are any domain constraints then we construct - * an explicit NULL default value that will be passed through - * CoerceToDomain processing. (This is a tad inefficient, since it causes - * rewriting the table which we really wouldn't have to do; but we do it - * to preserve the historical behavior that such a failure will be raised - * only if the table currently contains some rows.) - * * Note: we use build_column_default, and not just the cooked default * returned by AddRelationNewConstraints, so that the right thing happens * when a datatype's default applies. @@ -7495,6 +7486,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, { bool has_domain_constraints; bool has_missing = false; + bool has_volatile = false; /* * For an identity column, we can't use build_column_default(), @@ -7512,8 +7504,20 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, else defval = (Expr *) build_column_default(rel, attribute->attnum); + has_domain_constraints = DomainHaveVolatileConstraints(attribute->atttypid, &has_volatile); + + /* + * Adding a column with volatile domain constraint requires table + * rewrite + */ + if (has_volatile) + { + Assert(has_domain_constraints); + + tab->rewrite |= AT_REWRITE_DEFAULT_VAL; + } + /* Build CoerceToDomain(NULL) expression if needed */ - has_domain_constraints = DomainHasConstraints(attribute->atttypid); if (!defval && has_domain_constraints) { Oid baseTypeId; @@ -7555,27 +7559,39 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, * Attempt to skip a complete table rewrite by storing the * specified DEFAULT value outside of the heap. This is only * allowed for plain relations and non-generated columns, and the - * default expression can't be volatile (stable is OK). Note that - * contain_volatile_functions deems CoerceToDomain immutable, but - * here we consider that coercion to a domain with constraints is - * volatile; else it might fail even when the table is empty. + * default expression can't be volatile (stable is OK), and the + * domain constraint expression can't be volatile (stable is OK). + * + * Note that contain_volatile_functions deems CoerceToDomain + * immutable. However we have determined domain's volatility via + * DomainHaveVolatileConstraints. We attempt a soft evaluation of + * CoerceToDomain; if that evaluation fails, we flag the table for + * a rewrite. */ if (rel->rd_rel->relkind == RELKIND_RELATION && !colDef->generated && - !has_domain_constraints && + !has_volatile && !contain_volatile_functions((Node *) defval)) { EState *estate; ExprState *exprState; Datum missingval; bool missingIsNull; + ErrorSaveContext escontext = {T_ErrorSaveContext}; /* Evaluate the default expression */ estate = CreateExecutorState(); - exprState = ExecPrepareExpr(defval, estate); + exprState = ExecPrepareExprExtended(defval, estate, (Node *) &escontext); missingval = ExecEvalExpr(exprState, GetPerTupleExprContext(estate), &missingIsNull); + + if (SOFT_ERROR_OCCURRED(exprState->escontext)) + { + missingIsNull = true; + tab->rewrite |= AT_REWRITE_DEFAULT_VAL; + } + /* If it turns out NULL, nothing to do; else store it */ if (!missingIsNull) { diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out index ccbcdf8403f..7dabe95dfee 100644 --- a/src/test/regress/expected/fast_default.out +++ b/src/test/regress/expected/fast_default.out @@ -317,11 +317,61 @@ SELECT a, b, length(c) = 3 as c_ok, d, e >= 10 as e_ok FROM t2; 2 | 3 | t | {This,is,abcd,the,real,world} | t (2 rows) +---test fast default over domains with constraints +CREATE DOMAIN domain5 AS int CHECK(value > 10) DEFAULT 8; +CREATE DOMAIN domain6 as int CHECK(value > 10) DEFAULT random(min=>11, max=>100); +CREATE DOMAIN domain7 as int CHECK((value + random(min=>11::int, max=>11)) > 12); +CREATE DOMAIN domain8 as int not null; +--tests with non-empty table. +CREATE TABLE test_add_domain_col(a int); +ALTER TABLE test_add_domain_col ADD COLUMN a1 domain5; --table rewrite, not fail. +NOTICE: rewriting table test_add_domain_col for reason 2 +ALTER TABLE test_add_domain_col DROP COLUMN a1; +INSERT INTO test_add_domain_col VALUES(1),(2); +ALTER TABLE test_add_domain_col ADD COLUMN b domain5; --table rewrite, then fail +NOTICE: rewriting table test_add_domain_col for reason 2 +ERROR: value for domain domain5 violates check constraint "domain5_check" +ALTER TABLE test_add_domain_col ADD COLUMN b domain8; --table rewrite, then fail +NOTICE: rewriting table test_add_domain_col for reason 2 +ERROR: domain domain8 does not allow null values +ALTER TABLE test_add_domain_col ADD COLUMN b domain5 DEFAULT 1; --table rewrite, then fail +NOTICE: rewriting table test_add_domain_col for reason 2 +ERROR: value for domain domain5 violates check constraint "domain5_check" +ALTER TABLE test_add_domain_col ADD COLUMN b domain5 DEFAULT 12; --ok, no table rewrite +--explicit column default expression override domain's default +--expression, so no table rewrite. +ALTER TABLE test_add_domain_col ADD COLUMN c domain6 DEFAULT 14; +ALTER TABLE test_add_domain_col ADD COLUMN c1 domain8 DEFAULT 13; --no table rewrite +SELECT attnum, attname, atthasmissing, atthasdef, attmissingval +FROM pg_attribute +WHERE attnum > 0 AND attrelid = 'test_add_domain_col'::regclass AND attisdropped is false +AND atthasmissing +ORDER BY attnum; + attnum | attname | atthasmissing | atthasdef | attmissingval +--------+---------+---------------+-----------+--------------- + 3 | b | t | t | {12} + 4 | c | t | t | {14} + 5 | c1 | t | t | {13} +(3 rows) + +-- We need to rewrite the table whenever domain default contains volatile expression +ALTER TABLE test_add_domain_col ADD COLUMN d domain6; +NOTICE: rewriting table test_add_domain_col for reason 2 +-- We need to rewrite the table whenever domain constraint expression contains volatile expression +ALTER TABLE test_add_domain_col ADD COLUMN e domain7 default 14; +NOTICE: rewriting table test_add_domain_col for reason 2 +ALTER TABLE test_add_domain_col ADD COLUMN f domain7; +NOTICE: rewriting table test_add_domain_col for reason 2 DROP TABLE t2; +DROP TABLE test_add_domain_col; DROP DOMAIN domain1; DROP DOMAIN domain2; DROP DOMAIN domain3; DROP DOMAIN domain4; +DROP DOMAIN domain5; +DROP DOMAIN domain6; +DROP DOMAIN domain7; +DROP DOMAIN domain8; DROP FUNCTION foo(INT); -- Fall back to full rewrite for volatile expressions CREATE TABLE T(pk INT NOT NULL PRIMARY KEY); diff --git a/src/test/regress/sql/fast_default.sql b/src/test/regress/sql/fast_default.sql index 068dd0bc8aa..14d5f178c9c 100644 --- a/src/test/regress/sql/fast_default.sql +++ b/src/test/regress/sql/fast_default.sql @@ -287,11 +287,51 @@ ORDER BY attnum; SELECT a, b, length(c) = 3 as c_ok, d, e >= 10 as e_ok FROM t2; +---test fast default over domains with constraints +CREATE DOMAIN domain5 AS int CHECK(value > 10) DEFAULT 8; +CREATE DOMAIN domain6 as int CHECK(value > 10) DEFAULT random(min=>11, max=>100); +CREATE DOMAIN domain7 as int CHECK((value + random(min=>11::int, max=>11)) > 12); +CREATE DOMAIN domain8 as int not null; + +--tests with non-empty table. +CREATE TABLE test_add_domain_col(a int); +ALTER TABLE test_add_domain_col ADD COLUMN a1 domain5; --table rewrite, not fail. +ALTER TABLE test_add_domain_col DROP COLUMN a1; +INSERT INTO test_add_domain_col VALUES(1),(2); + +ALTER TABLE test_add_domain_col ADD COLUMN b domain5; --table rewrite, then fail +ALTER TABLE test_add_domain_col ADD COLUMN b domain8; --table rewrite, then fail +ALTER TABLE test_add_domain_col ADD COLUMN b domain5 DEFAULT 1; --table rewrite, then fail +ALTER TABLE test_add_domain_col ADD COLUMN b domain5 DEFAULT 12; --ok, no table rewrite + +--explicit column default expression override domain's default +--expression, so no table rewrite. +ALTER TABLE test_add_domain_col ADD COLUMN c domain6 DEFAULT 14; + +ALTER TABLE test_add_domain_col ADD COLUMN c1 domain8 DEFAULT 13; --no table rewrite +SELECT attnum, attname, atthasmissing, atthasdef, attmissingval +FROM pg_attribute +WHERE attnum > 0 AND attrelid = 'test_add_domain_col'::regclass AND attisdropped is false +AND atthasmissing +ORDER BY attnum; + +-- We need to rewrite the table whenever domain default contains volatile expression +ALTER TABLE test_add_domain_col ADD COLUMN d domain6; + +-- We need to rewrite the table whenever domain constraint expression contains volatile expression +ALTER TABLE test_add_domain_col ADD COLUMN e domain7 default 14; +ALTER TABLE test_add_domain_col ADD COLUMN f domain7; + DROP TABLE t2; +DROP TABLE test_add_domain_col; DROP DOMAIN domain1; DROP DOMAIN domain2; DROP DOMAIN domain3; DROP DOMAIN domain4; +DROP DOMAIN domain5; +DROP DOMAIN domain6; +DROP DOMAIN domain7; +DROP DOMAIN domain8; DROP FUNCTION foo(INT); -- Fall back to full rewrite for volatile expressions -- 2.34.1
