public inbox for [email protected]
help / color / mirror / Atom feedRe: support fast default for domain with constraints
13+ messages / 3 participants
[nested] [flat]
* Re: support fast default for domain with constraints
@ 2025-03-06 13:53 jian he <[email protected]>
2025-03-24 11:14 ` Re: support fast default for domain with constraints jian he <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: jian he @ 2025-03-06 13:53 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
On Thu, Mar 6, 2025 at 11:04 AM jian he <[email protected]> wrote:
>
> hi.
>
> rearrange the patch.
> v3-0001 and v3-0002 is preparare patches.
> v3-0001 add function: ExecPrepareExprSafe and ExecInitExprSafe.
> v3-0002 add function: DomainHaveVolatileConstraints
>
i actually do need DomainHaveVolatileConstraints
for virtual generated columns over domain with constraints in [1],
which I am working on.
for example:
create domain d1 as int check(value > random(min=>11::int, max=>12));
create domain d2 as int check(value > 12);
create table t(a int);
insert into t select g from generate_series(1, 10) g;
----we do need table rewrite in phase 3.
alter table t add column b d1 generated always as (a+11) virtual;
--we can only do table scan in phase 3.
alter table t add column c d2 generated always as (a + 12) virtual;
Generally, table rewrite is more expensive than table scan.
In the above case, if domain constraints are not volatile, table scan
should be fine.
[1]: https://postgr.es/m/CACJufxHArQysbDkWFmvK+D1TPHQWWTxWN15cMuUaTYX3xhQXgg@mail.gmail.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: support fast default for domain with constraints
2025-03-06 13:53 Re: support fast default for domain with constraints jian he <[email protected]>
@ 2025-03-24 11:14 ` jian he <[email protected]>
2025-05-19 07:05 ` Re: support fast default for domain with constraints jian he <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: jian he @ 2025-03-24 11:14 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
hi.
rebase because of commit: 8dd7c7cd0a2605d5301266a6b67a569d6a305106
also did minor enhancement.
v4-0001 add function: ExecPrepareExprSafe and ExecInitExprSafe.
v4-0002 add function: DomainHaveVolatileConstraints
v4-0003 tests and apply fast default for domain with constraints.
v4-0003 table with empty rows aligned with master behavior.
also will do table rewrite if the new column is domain with volatile
check constraints,
so less surprising behavior.
Attachments:
[text/x-patch] v4-0001-soft-error-variant-of-ExecPrepareExpr-ExecInitExp.patch (3.9K, 2-v4-0001-soft-error-variant-of-ExecPrepareExpr-ExecInitExp.patch)
download | inline diff:
From 09547bbc65f20846ac28efce035c016b21a75825 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 24 Mar 2025 16:07:46 +0800
Subject: [PATCH v4 1/3] soft error variant of ExecPrepareExpr, ExecInitExpr
ExecInitExprSafe: soft error of ExecInitExpr.
ExecPrepareExprSafe: soft error of ExecPrepareExpr.
ExecPrepareExprSafe initialize for expression execution with soft error support.
not all expression node support it. Like node CoerceToDomain support it.
XXX more comments.
discussion: https://postgr.es/m/CACJufxE_+iZBR1i49k_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
---
src/backend/executor/execExpr.c | 63 +++++++++++++++++++++++++++++++++
src/include/executor/executor.h | 2 ++
2 files changed, 65 insertions(+)
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index f1569879b52..9182ba446a0 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -170,6 +170,47 @@ ExecInitExpr(Expr *node, PlanState *parent)
return state;
}
+/*
+ * ExecInitExprSafe: soft error variant of ExecInitExpr.
+ *
+ * use it only for expression nodes support soft errors, not all expression
+ * nodes support it.
+*/
+ExprState *
+ExecInitExprSafe(Expr *node, PlanState *parent)
+{
+ ExprState *state;
+ ExprEvalStep scratch = {0};
+
+ /* Special case: NULL expression produces a NULL ExprState pointer */
+ if (node == NULL)
+ return NULL;
+
+ /* Initialize ExprState with empty step list */
+ state = makeNode(ExprState);
+ state->expr = node;
+ state->parent = parent;
+ state->ext_params = NULL;
+ state->escontext = makeNode(ErrorSaveContext);
+ state->escontext->type = T_ErrorSaveContext;
+ state->escontext->error_occurred = false;
+ state->escontext->details_wanted = true;
+
+ /* Insert setup steps as needed */
+ ExecCreateExprSetupSteps(state, (Node *) node);
+
+ /* Compile the expression proper */
+ ExecInitExprRec(node, state, &state->resvalue, &state->resnull);
+
+ /* Finally, append a DONE step */
+ scratch.opcode = EEOP_DONE_RETURN;
+ ExprEvalPushStep(state, &scratch);
+
+ ExecReadyExpr(state);
+
+ return state;
+}
+
/*
* ExecInitExprWithParams: prepare a standalone expression tree for execution
*
@@ -778,6 +819,28 @@ ExecPrepareExpr(Expr *node, EState *estate)
return result;
}
+/*
+ * ExecPrepareExprSafe: soft error variant of ExecPrepareExpr.
+ *
+ * use it when expression node *support* soft error expression execution.
+ */
+ExprState *
+ExecPrepareExprSafe(Expr *node, EState *estate)
+{
+ ExprState *result;
+ MemoryContext oldcontext;
+
+ oldcontext = MemoryContextSwitchTo(estate->es_query_cxt);
+
+ node = expression_planner(node);
+
+ result = ExecInitExprSafe(node, NULL);
+
+ MemoryContextSwitchTo(oldcontext);
+
+ return result;
+}
+
/*
* ExecPrepareQual --- initialize for qual execution outside a normal
* Plan tree context.
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index 0db5d18ba22..464c7318de1 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -305,6 +305,7 @@ ExecProcNode(PlanState *node)
* prototypes from functions in execExpr.c
*/
extern ExprState *ExecInitExpr(Expr *node, PlanState *parent);
+extern ExprState *ExecInitExprSafe(Expr *node, PlanState *parent);
extern ExprState *ExecInitExprWithParams(Expr *node, ParamListInfo ext_params);
extern ExprState *ExecInitQual(List *qual, PlanState *parent);
extern ExprState *ExecInitCheck(List *qual, PlanState *parent);
@@ -353,6 +354,7 @@ extern ProjectionInfo *ExecBuildUpdateProjection(List *targetList,
TupleTableSlot *slot,
PlanState *parent);
extern ExprState *ExecPrepareExpr(Expr *node, EState *estate);
+extern ExprState *ExecPrepareExprSafe(Expr *node, EState *estate);
extern ExprState *ExecPrepareQual(List *qual, EState *estate);
extern ExprState *ExecPrepareCheck(List *qual, EState *estate);
extern List *ExecPrepareExprList(List *nodes, EState *estate);
--
2.34.1
[text/x-patch] v4-0003-fast-default-for-domain-with-constraints.patch (14.8K, 3-v4-0003-fast-default-for-domain-with-constraints.patch)
download | inline diff:
From b9739edd3de443b203a14db3a6175988125de135 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 24 Mar 2025 19:08:25 +0800
Subject: [PATCH v4 3/3] fast default for domain with constraints
This is primarily done by evaluating CoerceToDomain with soft error support.
If we evaluate CoerceToDomain to false, in ATExecAddColumn, the defval node
evaluation value cannot be cast to the domain. However, in some scarenio we
cannot fail at the Phase 2 stage in cases where the table is empty. For example,
the to be added column is type of domain x, domain x is "check(value > 10) default 8".
Therefore, if an error occurred while evaluation, do not raise the error,
we signal Phase 3 to do table rewrite, error will be raised on Phase 3.
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 need table rewrite for domain with volatile check constraints.
so there will be less surprising behavior.
discussion: https://postgr.es/m/CACJufxE_+iZBR1i49k_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
[1]: https://git.postgresql.org/cgit/postgresql.git/commit/?id=aaaf9449ec6be62cb0d30ed3588dc384f56274bf
---
src/backend/commands/tablecmds.c | 66 ++++++++++---
src/test/regress/expected/fast_default.out | 103 +++++++++++++++++++++
src/test/regress/sql/fast_default.sql | 68 ++++++++++++++
3 files changed, 222 insertions(+), 15 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1202544ebd0..abddb9cdba7 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7342,15 +7342,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.
@@ -7369,6 +7360,8 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
{
bool has_domain_constraints;
bool has_missing = false;
+ bool explicit_defval = true;
+ bool has_volatile = false;
/*
* For an identity column, we can't use build_column_default(),
@@ -7386,8 +7379,31 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
else
defval = (Expr *) build_column_default(rel, attribute->attnum);
+ /*
+ * if defval is there, atthasdef is false, that means the defval comes
+ * from domain default expression and no explicit DEFAULT expression has
+ * been specified.
+ * In that case we need evaluate defval error safe way, so new column
+ * with domain specification such as ``CHECK(VALUE > 10) DEFAULT 8 ``
+ * can be addded to empty table.
+ */
+ if (defval)
+ {
+ TupleDesc rd_att = rel->rd_att;
+ Form_pg_attribute att_tup = TupleDescAttr(rd_att, attribute->attnum - 1);
+ if (!att_tup->atthasdef)
+ explicit_defval = false;
+ }
+
+ has_domain_constraints = DomainHaveVolatileConstraints(attribute->atttypid, &has_volatile);
+ /* new column with volatile domain constraint, then 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;
@@ -7408,6 +7424,13 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
-1);
if (defval == NULL) /* should not happen */
elog(ERROR, "failed to coerce base type to domain");
+
+ /*
+ * if domain have not-null constraint or check constraint that is
+ * equivalent to not-null, we only want it failure when table have
+ * some rows. let's evaulate defval in a soft error way.
+ */
+ explicit_defval = false;
}
if (defval)
@@ -7430,13 +7453,16 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
* 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.
+ * contain_volatile_functions deems CoerceToDomain immutable.
+ * But domain with volatile constraint will need table rewrite,
+ * regardless of domain's default expression.
+ * We do support soft error evaluation of CoerceToDomain if
+ * ExprState->escontext is not NULL. In that case if evaluation
+ * failed, set table rewrite to true, let's fail on Phase 3.
*/
if (rel->rd_rel->relkind == RELKIND_RELATION &&
!colDef->generated &&
- !has_domain_constraints &&
+ !has_volatile &&
!contain_volatile_functions((Node *) defval))
{
EState *estate;
@@ -7446,10 +7472,20 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Evaluate the default expression */
estate = CreateExecutorState();
- exprState = ExecPrepareExpr(defval, estate);
+ if (explicit_defval)
+ exprState = ExecPrepareExpr(defval, estate);
+ else
+ exprState = ExecPrepareExprSafe(defval, estate);
+
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..73fb23fd79b 100644
--- a/src/test/regress/expected/fast_default.out
+++ b/src/test/regress/expected/fast_default.out
@@ -317,11 +317,114 @@ 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 check constraint
+CREATE DOMAIN domain5 AS int check(value > 10) default 8;
+CREATE DOMAIN domain6 as int not null;
+CREATE DOMAIN domain7 as int check(value is not null);
+CREATE DOMAIN domain8 as int check(value > 10) DEFAULT random(min=>10, max=>100);
+CREATE TABLE t3(a int);
+ALTER TABLE t3 ADD COLUMN b domain5 default 1; --error
+ERROR: value for domain domain5 violates check constraint "domain5_check"
+ALTER TABLE t3 ADD COLUMN b domain6 default NULL; --error
+ERROR: domain domain6 does not allow null values
+ALTER TABLE t3 ADD COLUMN b domain6 default 11 + NULL; --error
+ERROR: domain domain6 does not allow null values
+ALTER TABLE t3 ADD COLUMN b domain7 default 11 + NULL; --error
+ERROR: value for domain domain7 violates check constraint "domain7_check"
+ALTER TABLE t3 ADD COLUMN d domain5; --ok. because t3 is empty
+NOTICE: rewriting table t3 for reason 2
+ALTER TABLE t3 ADD COLUMN b domain6; --ok. because t3 is empty
+NOTICE: rewriting table t3 for reason 2
+ALTER TABLE t3 ADD COLUMN c domain7; --ok. because t3 is empty
+NOTICE: rewriting table t3 for reason 2
+INSERT INTO t3 default values; --error
+ERROR: value for domain domain5 violates check constraint "domain5_check"
+INSERT INTO t3(a,b,c,d) values(1,2, 3, 12); --ok
+DROP TABLE t3;
+CREATE TABLE t3(a int);
+INSERT INTO t3 VALUES(1),(2);
+ALTER TABLE t3 ADD COLUMN b domain5; --table rewrite, then fail
+NOTICE: rewriting table t3 for reason 2
+ERROR: value for domain domain5 violates check constraint "domain5_check"
+ALTER TABLE t3 ADD COLUMN b domain6; --table rewrite, then fail
+NOTICE: rewriting table t3 for reason 2
+ERROR: domain domain6 does not allow null values
+ALTER TABLE t3 ADD COLUMN b domain7; --table rewrite, then fail
+NOTICE: rewriting table t3 for reason 2
+ERROR: value for domain domain7 violates check constraint "domain7_check"
+ALTER TABLE t3 ADD COLUMN b domain5 default 12; --no table rewrite
+ALTER TABLE t3 ADD COLUMN c domain6 default 13; --no table rewrite
+ALTER TABLE t3 ADD COLUMN d domain7 default 14; --no table rewrite
+--no table rewrite. explicit column default expression override domain default expression
+ALTER TABLE t3 ADD COLUMN e domain8 default 15;
+SELECT attnum, attname, atthasmissing, atthasdef, attmissingval
+FROM pg_attribute
+WHERE attnum > 0 AND attrelid = 't3'::regclass AND attisdropped is false
+ORDER BY attnum;
+ attnum | attname | atthasmissing | atthasdef | attmissingval
+--------+---------+---------------+-----------+---------------
+ 1 | a | f | f |
+ 2 | b | t | t | {12}
+ 3 | c | t | t | {13}
+ 4 | d | t | t | {14}
+ 5 | e | t | t | {15}
+(5 rows)
+
+--table rewrite. we are applying domain volatile default expresion
+ALTER TABLE t3 ADD COLUMN f domain8;
+NOTICE: rewriting table t3 for reason 2
+SELECT attnum, attname, atthasmissing, atthasdef, attmissingval
+FROM pg_attribute
+WHERE attnum > 0 AND attrelid = 't3'::regclass and not attisdropped
+ORDER BY attnum;
+ attnum | attname | atthasmissing | atthasdef | attmissingval
+--------+---------+---------------+-----------+---------------
+ 1 | a | f | f |
+ 2 | b | f | t |
+ 3 | c | f | t |
+ 4 | d | f | t |
+ 5 | e | f | t |
+ 6 | f | f | f |
+(6 rows)
+
+SELECT a,b,c,d,e,f > 10 as f_ok FROM t3 ORDER BY a;
+ a | b | c | d | e | f_ok
+---+----+----+----+----+------
+ 1 | 12 | 13 | 14 | 15 | t
+ 2 | 12 | 13 | 14 | 15 | t
+(2 rows)
+
+------test table rewrite for volatile domain constraints.
+CREATE DOMAIN domain9 as int check((value + random(min=>11::int, max=>11)) > 12); --volatile
+CREATE DOMAIN domain10 as int check((value + random(min=>11::int, max=>11)) > 12) default 1; --volatile
+CREATE TABLE t4(a int);
+INSERT INTO t4 VALUES(1),(2);
+--all these will table rewrite and be ok.
+ALTER TABLE t4 ADD COLUMN b domain9; --default to NULL
+NOTICE: rewriting table t4 for reason 2
+ALTER TABLE t4 ADD COLUMN c domain10 default 14;
+NOTICE: rewriting table t4 for reason 2
+SELECT COUNT(*) AS expect_zero
+FROM pg_attribute
+WHERE attnum > 0 AND attrelid = 't4'::regclass AND attmissingval IS NOT NULL;
+ expect_zero
+-------------
+ 0
+(1 row)
+
DROP TABLE t2;
+DROP TABLE t3;
+DROP TABLE t4;
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 DOMAIN domain9;
+DROP DOMAIN domain10;
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..e9187cd2198 100644
--- a/src/test/regress/sql/fast_default.sql
+++ b/src/test/regress/sql/fast_default.sql
@@ -287,11 +287,79 @@ 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 check constraint
+CREATE DOMAIN domain5 AS int check(value > 10) default 8;
+CREATE DOMAIN domain6 as int not null;
+CREATE DOMAIN domain7 as int check(value is not null);
+CREATE DOMAIN domain8 as int check(value > 10) DEFAULT random(min=>10, max=>100);
+
+CREATE TABLE t3(a int);
+ALTER TABLE t3 ADD COLUMN b domain5 default 1; --error
+ALTER TABLE t3 ADD COLUMN b domain6 default NULL; --error
+ALTER TABLE t3 ADD COLUMN b domain6 default 11 + NULL; --error
+ALTER TABLE t3 ADD COLUMN b domain7 default 11 + NULL; --error
+
+ALTER TABLE t3 ADD COLUMN d domain5; --ok. because t3 is empty
+ALTER TABLE t3 ADD COLUMN b domain6; --ok. because t3 is empty
+ALTER TABLE t3 ADD COLUMN c domain7; --ok. because t3 is empty
+INSERT INTO t3 default values; --error
+INSERT INTO t3(a,b,c,d) values(1,2, 3, 12); --ok
+
+DROP TABLE t3;
+CREATE TABLE t3(a int);
+INSERT INTO t3 VALUES(1),(2);
+
+ALTER TABLE t3 ADD COLUMN b domain5; --table rewrite, then fail
+ALTER TABLE t3 ADD COLUMN b domain6; --table rewrite, then fail
+ALTER TABLE t3 ADD COLUMN b domain7; --table rewrite, then fail
+
+ALTER TABLE t3 ADD COLUMN b domain5 default 12; --no table rewrite
+ALTER TABLE t3 ADD COLUMN c domain6 default 13; --no table rewrite
+ALTER TABLE t3 ADD COLUMN d domain7 default 14; --no table rewrite
+--no table rewrite. explicit column default expression override domain default expression
+ALTER TABLE t3 ADD COLUMN e domain8 default 15;
+
+SELECT attnum, attname, atthasmissing, atthasdef, attmissingval
+FROM pg_attribute
+WHERE attnum > 0 AND attrelid = 't3'::regclass AND attisdropped is false
+ORDER BY attnum;
+
+--table rewrite. we are applying domain volatile default expresion
+ALTER TABLE t3 ADD COLUMN f domain8;
+
+SELECT attnum, attname, atthasmissing, atthasdef, attmissingval
+FROM pg_attribute
+WHERE attnum > 0 AND attrelid = 't3'::regclass and not attisdropped
+ORDER BY attnum;
+
+SELECT a,b,c,d,e,f > 10 as f_ok FROM t3 ORDER BY a;
+------test table rewrite for volatile domain constraints.
+CREATE DOMAIN domain9 as int check((value + random(min=>11::int, max=>11)) > 12); --volatile
+CREATE DOMAIN domain10 as int check((value + random(min=>11::int, max=>11)) > 12) default 1; --volatile
+
+CREATE TABLE t4(a int);
+INSERT INTO t4 VALUES(1),(2);
+--all these will table rewrite and be ok.
+ALTER TABLE t4 ADD COLUMN b domain9; --default to NULL
+ALTER TABLE t4 ADD COLUMN c domain10 default 14;
+
+SELECT COUNT(*) AS expect_zero
+FROM pg_attribute
+WHERE attnum > 0 AND attrelid = 't4'::regclass AND attmissingval IS NOT NULL;
+
DROP TABLE t2;
+DROP TABLE t3;
+DROP TABLE t4;
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 DOMAIN domain9;
+DROP DOMAIN domain10;
DROP FUNCTION foo(INT);
-- Fall back to full rewrite for volatile expressions
--
2.34.1
[text/x-patch] v4-0002-add-function-DomainHaveVolatileConstraints.patch (2.6K, 4-v4-0002-add-function-DomainHaveVolatileConstraints.patch)
download | inline diff:
From bca9beb0d2da988aec25fe776ea1bfda84897d9a Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 6 Mar 2025 09:58:56 +0800
Subject: [PATCH v4 2/3] add function DomainHaveVolatileConstraints
bool DomainHaveVolatileConstraints(Oid type_id, bool *have_volatile);
Returns true if the Domain has any constraints.
If you want check this domain have any volatile check constraints,
make sure have_volatile is not NULL.
discussion: https://postgr.es/m/CACJufxE_+iZBR1i49k_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
---
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 5a3b3788d02..617d0ec27cf 100644
--- a/src/backend/utils/cache/typcache.c
+++ b/src/backend/utils/cache/typcache.c
@@ -1498,6 +1498,43 @@ DomainHasConstraints(Oid type_id)
}
+/*
+ * Returns true if the Domain has any constraints.
+ * To check for the presence of volatile constraints, ensure
+ * have_volatile is not NULL. If a volatile constraint exists,
+ * have_volatile will be true.
+ */
+bool
+DomainHaveVolatileConstraints(Oid type_id, bool *have_volatile)
+{
+ 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);
+
+ if (typentry->domainData != NULL)
+ {
+ ListCell *lc;
+
+ foreach(lc, typentry->domainData->constraints)
+ {
+ DomainConstraintState *r = (DomainConstraintState *) lfirst(lc);
+
+ if (r->constrainttype == DOM_CONSTRAINT_CHECK &&
+ contain_volatile_functions((Node *) r->check_expr))
+ {
+ *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 562a581333a..36257c4240c 100644
--- a/src/include/utils/typcache.h
+++ b/src/include/utils/typcache.h
@@ -183,6 +183,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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: support fast default for domain with constraints
2025-03-06 13:53 Re: support fast default for domain with constraints jian he <[email protected]>
2025-03-24 11:14 ` Re: support fast default for domain with constraints jian he <[email protected]>
@ 2025-05-19 07:05 ` jian he <[email protected]>
2025-06-02 09:56 ` Re: support fast default for domain with constraints jian he <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: jian he @ 2025-05-19 07:05 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
On Mon, Mar 24, 2025 at 7:14 PM jian he <[email protected]> wrote:
>
> v4-0003 table with empty rows aligned with master behavior.
> also will do table rewrite if the new column is domain with volatile
> check constraints,
> so less surprising behavior.
I found out that my v4-0003 is wrong.
For example, the following ALTER TABLE ADD COLUMN should not fail.
CREATE DOMAIN domain5 AS int check(value > 10) default 8;
CREATE TABLE t3(a int);
ALTER TABLE t3 ADD COLUMN b domain5 default 1; --ok, table rewrite
I also reduced the bloated tests.
summary of the behavior that is different from master:
if domain constraint is not volatile *and* domain's default expression satisfy
constraint's condition then no need table rewrite.
Attachments:
[text/x-patch] v5-0002-add-function-DomainHaveVolatileConstraints.patch (2.7K, 2-v5-0002-add-function-DomainHaveVolatileConstraints.patch)
download | inline diff:
From 8c3760aa64773412b5127347cc56dccd0042b592 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 19 May 2025 11:11:01 +0800
Subject: [PATCH v5 2/3] add function DomainHaveVolatileConstraints
the signature is:
extern bool DomainHaveVolatileConstraints(Oid type_id, bool *have_volatile);
Returns true if the Domain has any constraints. If you want check this domain
have any volatile check constraints, make sure have_volatile is not NULL.
discussion: https://postgr.es/m/CACJufxE_+iZBR1i49k_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
---
src/backend/utils/cache/typcache.c | 40 ++++++++++++++++++++++++++++++
src/include/utils/typcache.h | 1 +
2 files changed, 41 insertions(+)
diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c
index f9aec38a11f..83f195d09d9 100644
--- a/src/backend/utils/cache/typcache.c
+++ b/src/backend/utils/cache/typcache.c
@@ -1500,6 +1500,46 @@ 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, the behavior is
+ * equivalent to that of DomainHasConstraints.
+ */
+bool
+DomainHaveVolatileConstraints(Oid type_id, bool *have_volatile)
+{
+ 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);
+
+ if (typentry->domainData != NULL)
+ {
+ ListCell *lc;
+
+ foreach(lc, typentry->domainData->constraints)
+ {
+ DomainConstraintState *r = (DomainConstraintState *) lfirst(lc);
+
+ if (r->constrainttype == DOM_CONSTRAINT_CHECK &&
+ contain_volatile_functions((Node *) r->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 1cb30f1818c..aa1c86e35c3 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
[text/x-patch] v5-0003-fast-default-for-domain-with-constraints.patch (10.8K, 3-v5-0003-fast-default-for-domain-with-constraints.patch)
download | inline diff:
From 003e9b6b6e3429de0910d01ff6840f4bf0f362c8 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 19 May 2025 14:14:28 +0800
Subject: [PATCH v5 3/3] fast default for domain with constraints
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
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_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
[1]: https://git.postgresql.org/cgit/postgresql.git/commit/?id=aaaf9449ec6be62cb0d30ed3588dc384f56274bf
---
src/backend/commands/tablecmds.c | 45 +++++++++++-------
src/test/regress/expected/fast_default.out | 55 ++++++++++++++++++++++
src/test/regress/sql/fast_default.sql | 44 +++++++++++++++++
3 files changed, 128 insertions(+), 16 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 54ad38247aa..dba2ea77cad 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7429,15 +7429,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.
@@ -7456,6 +7447,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(),
@@ -7473,8 +7465,17 @@ 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 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;
@@ -7516,14 +7517,18 @@ 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 can't be volatile (stable is OK).
+ *
+ * Note that contain_volatile_functions deems CoerceToDomain
+ * immutable. However we have computed CoerceToDomain is volatile
+ * or not via DomainHaveVolatileConstraints. We use soft error
+ * evaluation of CoerceToDomain, if evaluation failed, then set
+ * table rewrite to true.
*/
if (rel->rd_rel->relkind == RELKIND_RELATION &&
!colDef->generated &&
- !has_domain_constraints &&
+ !has_volatile &&
!contain_volatile_functions((Node *) defval))
{
EState *estate;
@@ -7533,10 +7538,18 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Evaluate the default expression */
estate = CreateExecutorState();
- exprState = ExecPrepareExpr(defval, estate);
+ exprState = ExecPrepareExprSafe(defval, estate);
+
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..861208a269a 100644
--- a/src/test/regress/expected/fast_default.out
+++ b/src/test/regress/expected/fast_default.out
@@ -317,11 +317,66 @@ 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 not null;
+CREATE DOMAIN domain7 as int check(value > 10) DEFAULT random(min=>10, max=>100);
+CREATE DOMAIN domain8 as int check((value + random(min=>11::int, max=>11)) > 12);
+--tests with empty table.
+CREATE TABLE t3(a int);
+ALTER TABLE t3 ADD COLUMN b domain5 default 1; --ok, table rewrite
+NOTICE: rewriting table t3 for reason 2
+ALTER TABLE t3 ADD COLUMN c domain6 default 11 + NULL; --ok, table rewrite
+NOTICE: rewriting table t3 for reason 2
+ALTER TABLE t3 ADD COLUMN d domain7 default 2; --ok, table rewrite
+NOTICE: rewriting table t3 for reason 2
+ALTER TABLE t3 ADD COLUMN e domain8 default 3; --ok, table rewrite
+NOTICE: rewriting table t3 for reason 2
+DROP TABLE t3;
+--tests with non-empty table.
+CREATE TABLE t3(a int);
+INSERT INTO t3 VALUES(1),(2);
+ALTER TABLE t3 ADD COLUMN b domain5; --table rewrite, then fail
+NOTICE: rewriting table t3 for reason 2
+ERROR: value for domain domain5 violates check constraint "domain5_check"
+ALTER TABLE t3 ADD COLUMN b domain6; --table rewrite, then fail
+NOTICE: rewriting table t3 for reason 2
+ERROR: domain domain6 does not allow null values
+ALTER TABLE t3 ADD COLUMN b domain5 default 12; --no table rewrite
+ALTER TABLE t3 ADD COLUMN c domain6 default 13; --no table rewrite
+--no table rewrite. explicit column default expression override domain default
+--expression
+ALTER TABLE t3 ADD COLUMN d domain7 default 15;
+SELECT attnum, attname, atthasmissing, atthasdef, attmissingval
+FROM pg_attribute
+WHERE attnum > 0 AND attrelid = 't3'::regclass AND attisdropped is false
+AND atthasmissing
+ORDER BY attnum;
+ attnum | attname | atthasmissing | atthasdef | attmissingval
+--------+---------+---------------+-----------+---------------
+ 2 | b | t | t | {12}
+ 3 | c | t | t | {13}
+ 4 | d | t | t | {15}
+(3 rows)
+
+--table rewrite. we are applying domain volatile default expresion
+ALTER TABLE t3 ADD COLUMN e domain7;
+NOTICE: rewriting table t3 for reason 2
+--table rewrite for volatile domain constraints.
+ALTER TABLE t3 ADD COLUMN f domain8 default 14; --table rewrite
+NOTICE: rewriting table t3 for reason 2
+ALTER TABLE t3 ADD COLUMN f1 domain8; --table rewrite
+NOTICE: rewriting table t3 for reason 2
DROP TABLE t2;
+DROP TABLE t3;
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..a98d86a6204 100644
--- a/src/test/regress/sql/fast_default.sql
+++ b/src/test/regress/sql/fast_default.sql
@@ -287,11 +287,55 @@ 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 not null;
+CREATE DOMAIN domain7 as int check(value > 10) DEFAULT random(min=>10, max=>100);
+CREATE DOMAIN domain8 as int check((value + random(min=>11::int, max=>11)) > 12);
+
+--tests with empty table.
+CREATE TABLE t3(a int);
+ALTER TABLE t3 ADD COLUMN b domain5 default 1; --ok, table rewrite
+ALTER TABLE t3 ADD COLUMN c domain6 default 11 + NULL; --ok, table rewrite
+ALTER TABLE t3 ADD COLUMN d domain7 default 2; --ok, table rewrite
+ALTER TABLE t3 ADD COLUMN e domain8 default 3; --ok, table rewrite
+DROP TABLE t3;
+
+--tests with non-empty table.
+CREATE TABLE t3(a int);
+INSERT INTO t3 VALUES(1),(2);
+
+ALTER TABLE t3 ADD COLUMN b domain5; --table rewrite, then fail
+ALTER TABLE t3 ADD COLUMN b domain6; --table rewrite, then fail
+ALTER TABLE t3 ADD COLUMN b domain5 default 12; --no table rewrite
+ALTER TABLE t3 ADD COLUMN c domain6 default 13; --no table rewrite
+--no table rewrite. explicit column default expression override domain default
+--expression
+ALTER TABLE t3 ADD COLUMN d domain7 default 15;
+
+SELECT attnum, attname, atthasmissing, atthasdef, attmissingval
+FROM pg_attribute
+WHERE attnum > 0 AND attrelid = 't3'::regclass AND attisdropped is false
+AND atthasmissing
+ORDER BY attnum;
+
+--table rewrite. we are applying domain volatile default expresion
+ALTER TABLE t3 ADD COLUMN e domain7;
+
+--table rewrite for volatile domain constraints.
+ALTER TABLE t3 ADD COLUMN f domain8 default 14; --table rewrite
+ALTER TABLE t3 ADD COLUMN f1 domain8; --table rewrite
+
DROP TABLE t2;
+DROP TABLE t3;
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
[text/x-patch] v5-0001-soft-error-variant-of-ExecPrepareExpr-ExecInitExp.patch (3.9K, 4-v5-0001-soft-error-variant-of-ExecPrepareExpr-ExecInitExp.patch)
download | inline diff:
From 42fd50504556d2528f46a19d7c90e6b230986834 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 24 Mar 2025 16:07:46 +0800
Subject: [PATCH v5 1/3] soft error variant of ExecPrepareExpr, ExecInitExpr
ExecInitExprSafe: soft error of ExecInitExpr.
ExecPrepareExprSafe: soft error of ExecPrepareExpr.
ExecPrepareExprSafe initialize for expression execution with soft error support.
not all expression node support it. Like node CoerceToDomain support it.
XXX more comments.
discussion: https://postgr.es/m/CACJufxE_+iZBR1i49k_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
---
src/backend/executor/execExpr.c | 63 +++++++++++++++++++++++++++++++++
src/include/executor/executor.h | 2 ++
2 files changed, 65 insertions(+)
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index f1569879b52..9182ba446a0 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -170,6 +170,47 @@ ExecInitExpr(Expr *node, PlanState *parent)
return state;
}
+/*
+ * ExecInitExprSafe: soft error variant of ExecInitExpr.
+ *
+ * use it only for expression nodes support soft errors, not all expression
+ * nodes support it.
+*/
+ExprState *
+ExecInitExprSafe(Expr *node, PlanState *parent)
+{
+ ExprState *state;
+ ExprEvalStep scratch = {0};
+
+ /* Special case: NULL expression produces a NULL ExprState pointer */
+ if (node == NULL)
+ return NULL;
+
+ /* Initialize ExprState with empty step list */
+ state = makeNode(ExprState);
+ state->expr = node;
+ state->parent = parent;
+ state->ext_params = NULL;
+ state->escontext = makeNode(ErrorSaveContext);
+ state->escontext->type = T_ErrorSaveContext;
+ state->escontext->error_occurred = false;
+ state->escontext->details_wanted = true;
+
+ /* Insert setup steps as needed */
+ ExecCreateExprSetupSteps(state, (Node *) node);
+
+ /* Compile the expression proper */
+ ExecInitExprRec(node, state, &state->resvalue, &state->resnull);
+
+ /* Finally, append a DONE step */
+ scratch.opcode = EEOP_DONE_RETURN;
+ ExprEvalPushStep(state, &scratch);
+
+ ExecReadyExpr(state);
+
+ return state;
+}
+
/*
* ExecInitExprWithParams: prepare a standalone expression tree for execution
*
@@ -778,6 +819,28 @@ ExecPrepareExpr(Expr *node, EState *estate)
return result;
}
+/*
+ * ExecPrepareExprSafe: soft error variant of ExecPrepareExpr.
+ *
+ * use it when expression node *support* soft error expression execution.
+ */
+ExprState *
+ExecPrepareExprSafe(Expr *node, EState *estate)
+{
+ ExprState *result;
+ MemoryContext oldcontext;
+
+ oldcontext = MemoryContextSwitchTo(estate->es_query_cxt);
+
+ node = expression_planner(node);
+
+ result = ExecInitExprSafe(node, NULL);
+
+ MemoryContextSwitchTo(oldcontext);
+
+ return result;
+}
+
/*
* ExecPrepareQual --- initialize for qual execution outside a normal
* Plan tree context.
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index ae99407db89..a26160042ee 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -346,6 +346,7 @@ ExecProcNode(PlanState *node)
* prototypes from functions in execExpr.c
*/
extern ExprState *ExecInitExpr(Expr *node, PlanState *parent);
+extern ExprState *ExecInitExprSafe(Expr *node, PlanState *parent);
extern ExprState *ExecInitExprWithParams(Expr *node, ParamListInfo ext_params);
extern ExprState *ExecInitQual(List *qual, PlanState *parent);
extern ExprState *ExecInitCheck(List *qual, PlanState *parent);
@@ -394,6 +395,7 @@ extern ProjectionInfo *ExecBuildUpdateProjection(List *targetList,
TupleTableSlot *slot,
PlanState *parent);
extern ExprState *ExecPrepareExpr(Expr *node, EState *estate);
+extern ExprState *ExecPrepareExprSafe(Expr *node, EState *estate);
extern ExprState *ExecPrepareQual(List *qual, EState *estate);
extern ExprState *ExecPrepareCheck(List *qual, EState *estate);
extern List *ExecPrepareExprList(List *nodes, EState *estate);
--
2.34.1
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: support fast default for domain with constraints
2025-03-06 13:53 Re: support fast default for domain with constraints jian he <[email protected]>
2025-03-24 11:14 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-05-19 07:05 ` Re: support fast default for domain with constraints jian he <[email protected]>
@ 2025-06-02 09:56 ` jian he <[email protected]>
2025-09-01 06:27 ` Re: support fast default for domain with constraints jian he <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: jian he @ 2025-06-02 09:56 UTC (permalink / raw)
To: pgsql-hackers
hi.
attached is to fix the regress test failure[0] in V5.
I also did some test simplification.
[0] https://api.cirrus-ci.com/v1/artifact/task/6014753866252288/log/src/test/regress/regression.diffs
Attachments:
[application/x-patch] v6-0001-soft-error-variant-of-ExecPrepareExpr-ExecInitExpr.patch (3.9K, 2-v6-0001-soft-error-variant-of-ExecPrepareExpr-ExecInitExpr.patch)
download | inline diff:
From 1df9fc6e8e645463e864f44492d532def74c8437 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 24 Mar 2025 16:07:46 +0800
Subject: [PATCH v6 1/3] soft error variant of ExecPrepareExpr, ExecInitExpr
ExecInitExprSafe: soft error of ExecInitExpr.
ExecPrepareExprSafe: soft error of ExecPrepareExpr.
ExecPrepareExprSafe initialize for expression execution with soft error support.
not all expression node support it. Like node CoerceToDomain support it.
XXX more comments.
discussion: https://postgr.es/m/CACJufxE_+iZBR1i49k_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
---
src/backend/executor/execExpr.c | 63 +++++++++++++++++++++++++++++++++
src/include/executor/executor.h | 2 ++
2 files changed, 65 insertions(+)
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index f1569879b52..9182ba446a0 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -170,6 +170,47 @@ ExecInitExpr(Expr *node, PlanState *parent)
return state;
}
+/*
+ * ExecInitExprSafe: soft error variant of ExecInitExpr.
+ *
+ * use it only for expression nodes support soft errors, not all expression
+ * nodes support it.
+*/
+ExprState *
+ExecInitExprSafe(Expr *node, PlanState *parent)
+{
+ ExprState *state;
+ ExprEvalStep scratch = {0};
+
+ /* Special case: NULL expression produces a NULL ExprState pointer */
+ if (node == NULL)
+ return NULL;
+
+ /* Initialize ExprState with empty step list */
+ state = makeNode(ExprState);
+ state->expr = node;
+ state->parent = parent;
+ state->ext_params = NULL;
+ state->escontext = makeNode(ErrorSaveContext);
+ state->escontext->type = T_ErrorSaveContext;
+ state->escontext->error_occurred = false;
+ state->escontext->details_wanted = true;
+
+ /* Insert setup steps as needed */
+ ExecCreateExprSetupSteps(state, (Node *) node);
+
+ /* Compile the expression proper */
+ ExecInitExprRec(node, state, &state->resvalue, &state->resnull);
+
+ /* Finally, append a DONE step */
+ scratch.opcode = EEOP_DONE_RETURN;
+ ExprEvalPushStep(state, &scratch);
+
+ ExecReadyExpr(state);
+
+ return state;
+}
+
/*
* ExecInitExprWithParams: prepare a standalone expression tree for execution
*
@@ -778,6 +819,28 @@ ExecPrepareExpr(Expr *node, EState *estate)
return result;
}
+/*
+ * ExecPrepareExprSafe: soft error variant of ExecPrepareExpr.
+ *
+ * use it when expression node *support* soft error expression execution.
+ */
+ExprState *
+ExecPrepareExprSafe(Expr *node, EState *estate)
+{
+ ExprState *result;
+ MemoryContext oldcontext;
+
+ oldcontext = MemoryContextSwitchTo(estate->es_query_cxt);
+
+ node = expression_planner(node);
+
+ result = ExecInitExprSafe(node, NULL);
+
+ MemoryContextSwitchTo(oldcontext);
+
+ return result;
+}
+
/*
* ExecPrepareQual --- initialize for qual execution outside a normal
* Plan tree context.
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index 104b059544d..1c4e4a8eebb 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -318,6 +318,7 @@ ExecProcNode(PlanState *node)
* prototypes from functions in execExpr.c
*/
extern ExprState *ExecInitExpr(Expr *node, PlanState *parent);
+extern ExprState *ExecInitExprSafe(Expr *node, PlanState *parent);
extern ExprState *ExecInitExprWithParams(Expr *node, ParamListInfo ext_params);
extern ExprState *ExecInitQual(List *qual, PlanState *parent);
extern ExprState *ExecInitCheck(List *qual, PlanState *parent);
@@ -366,6 +367,7 @@ extern ProjectionInfo *ExecBuildUpdateProjection(List *targetList,
TupleTableSlot *slot,
PlanState *parent);
extern ExprState *ExecPrepareExpr(Expr *node, EState *estate);
+extern ExprState *ExecPrepareExprSafe(Expr *node, EState *estate);
extern ExprState *ExecPrepareQual(List *qual, EState *estate);
extern ExprState *ExecPrepareCheck(List *qual, EState *estate);
extern List *ExecPrepareExprList(List *nodes, EState *estate);
--
2.34.1
[application/x-patch] v6-0003-fast-default-for-domain-with-constraints.patch (9.9K, 3-v6-0003-fast-default-for-domain-with-constraints.patch)
download | inline diff:
From 7d9619e24048d1238c2cf49f236e746bc8810144 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 2 Jun 2025 16:50:34 +0800
Subject: [PATCH v6 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_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
[1]: https://git.postgresql.org/cgit/postgresql.git/commit/?id=aaaf9449ec6be62cb0d30ed3588dc384f56274bf
---
src/backend/commands/tablecmds.c | 46 ++++++++++++++--------
src/test/regress/expected/fast_default.out | 44 +++++++++++++++++++++
src/test/regress/sql/fast_default.sql | 37 +++++++++++++++++
3 files changed, 111 insertions(+), 16 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index acf11e83c04..081930deed5 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7429,15 +7429,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.
@@ -7456,6 +7447,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(),
@@ -7473,8 +7465,18 @@ 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 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;
@@ -7516,14 +7518,18 @@ 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 computed CoerceToDomain is volatile
+ * or not via DomainHaveVolatileConstraints. We use soft error
+ * evaluation of CoerceToDomain, if evaluation failed, then set
+ * table rewrite to true.
*/
if (rel->rd_rel->relkind == RELKIND_RELATION &&
!colDef->generated &&
- !has_domain_constraints &&
+ !has_volatile &&
!contain_volatile_functions((Node *) defval))
{
EState *estate;
@@ -7533,10 +7539,18 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Evaluate the default expression */
estate = CreateExecutorState();
- exprState = ExecPrepareExpr(defval, estate);
+ exprState = ExecPrepareExprSafe(defval, estate);
+
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..aa522cf8bfa 100644
--- a/src/test/regress/expected/fast_default.out
+++ b/src/test/regress/expected/fast_default.out
@@ -317,11 +317,55 @@ 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 not null;
+CREATE DOMAIN domain7 as int check(value > 10) DEFAULT random(min=>11, max=>100);
+CREATE DOMAIN domain8 as int check((value + random(min=>11::int, max=>11)) > 12);
+--tests with non-empty table.
+CREATE TABLE t3(a int);
+INSERT INTO t3 VALUES(1),(2);
+ALTER TABLE t3 ADD COLUMN b domain5; --table rewrite, then fail
+NOTICE: rewriting table t3 for reason 2
+ERROR: value for domain domain5 violates check constraint "domain5_check"
+ALTER TABLE t3 ADD COLUMN b domain6; --table rewrite, then fail
+NOTICE: rewriting table t3 for reason 2
+ERROR: domain domain6 does not allow null values
+ALTER TABLE t3 ADD COLUMN b domain5 default 12; --no table rewrite
+ALTER TABLE t3 ADD COLUMN c domain6 default 13; --no table rewrite
+--explicit column default expression override domain's default
+--expression, so no need table rewrite.
+ALTER TABLE t3 ADD COLUMN d domain7 default 14;
+SELECT attnum, attname, atthasmissing, atthasdef, attmissingval
+FROM pg_attribute
+WHERE attnum > 0 AND attrelid = 't3'::regclass AND attisdropped is false
+AND atthasmissing
+ORDER BY attnum;
+ attnum | attname | atthasmissing | atthasdef | attmissingval
+--------+---------+---------------+-----------+---------------
+ 2 | b | t | t | {12}
+ 3 | c | t | t | {13}
+ 4 | d | t | t | {14}
+(3 rows)
+
+--need table rewrite when we are applying domain volatile default expresion
+ALTER TABLE t3 ADD COLUMN e domain7;
+NOTICE: rewriting table t3 for reason 2
+-- need table rewrite when new column is domain with volatile constraints.
+ALTER TABLE t3 ADD COLUMN f domain8 default 14;
+NOTICE: rewriting table t3 for reason 2
+ALTER TABLE t3 ADD COLUMN f1 domain8;
+NOTICE: rewriting table t3 for reason 2
DROP TABLE t2;
+DROP TABLE t3;
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..269a39edf77 100644
--- a/src/test/regress/sql/fast_default.sql
+++ b/src/test/regress/sql/fast_default.sql
@@ -287,11 +287,48 @@ 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 not null;
+CREATE DOMAIN domain7 as int check(value > 10) DEFAULT random(min=>11, max=>100);
+CREATE DOMAIN domain8 as int check((value + random(min=>11::int, max=>11)) > 12);
+
+--tests with non-empty table.
+CREATE TABLE t3(a int);
+INSERT INTO t3 VALUES(1),(2);
+
+ALTER TABLE t3 ADD COLUMN b domain5; --table rewrite, then fail
+ALTER TABLE t3 ADD COLUMN b domain6; --table rewrite, then fail
+ALTER TABLE t3 ADD COLUMN b domain5 default 12; --no table rewrite
+ALTER TABLE t3 ADD COLUMN c domain6 default 13; --no table rewrite
+
+--explicit column default expression override domain's default
+--expression, so no need table rewrite.
+ALTER TABLE t3 ADD COLUMN d domain7 default 14;
+
+SELECT attnum, attname, atthasmissing, atthasdef, attmissingval
+FROM pg_attribute
+WHERE attnum > 0 AND attrelid = 't3'::regclass AND attisdropped is false
+AND atthasmissing
+ORDER BY attnum;
+
+--need table rewrite when we are applying domain volatile default expresion
+ALTER TABLE t3 ADD COLUMN e domain7;
+
+-- need table rewrite when new column is domain with volatile constraints.
+ALTER TABLE t3 ADD COLUMN f domain8 default 14;
+ALTER TABLE t3 ADD COLUMN f1 domain8;
+
DROP TABLE t2;
+DROP TABLE t3;
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
[application/x-patch] v6-0002-add-function-DomainHaveVolatileConstraints.patch (2.7K, 4-v6-0002-add-function-DomainHaveVolatileConstraints.patch)
download | inline diff:
From d476f84ca48da396526cd15349b5d8ea93078cdf Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 19 May 2025 11:11:01 +0800
Subject: [PATCH v6 2/3] add function DomainHaveVolatileConstraints
the signature is:
extern bool DomainHaveVolatileConstraints(Oid type_id, bool *have_volatile);
Returns true if the Domain has any constraints. If you want check this domain
have any volatile check constraints, make sure have_volatile is not NULL.
discussion: https://postgr.es/m/CACJufxE_+iZBR1i49k_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
---
src/backend/utils/cache/typcache.c | 40 ++++++++++++++++++++++++++++++
src/include/utils/typcache.h | 1 +
2 files changed, 41 insertions(+)
diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c
index f9aec38a11f..83f195d09d9 100644
--- a/src/backend/utils/cache/typcache.c
+++ b/src/backend/utils/cache/typcache.c
@@ -1500,6 +1500,46 @@ 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, the behavior is
+ * equivalent to that of DomainHasConstraints.
+ */
+bool
+DomainHaveVolatileConstraints(Oid type_id, bool *have_volatile)
+{
+ 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);
+
+ if (typentry->domainData != NULL)
+ {
+ ListCell *lc;
+
+ foreach(lc, typentry->domainData->constraints)
+ {
+ DomainConstraintState *r = (DomainConstraintState *) lfirst(lc);
+
+ if (r->constrainttype == DOM_CONSTRAINT_CHECK &&
+ contain_volatile_functions((Node *) r->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 1cb30f1818c..aa1c86e35c3 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: support fast default for domain with constraints
2025-03-06 13:53 Re: support fast default for domain with constraints jian he <[email protected]>
2025-03-24 11:14 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-05-19 07:05 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-06-02 09:56 ` Re: support fast default for domain with constraints jian he <[email protected]>
@ 2025-09-01 06:27 ` jian he <[email protected]>
2026-01-26 07:52 ` Re: support fast default for domain with constraints jian he <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: jian he @ 2025-09-01 06:27 UTC (permalink / raw)
To: pgsql-hackers
hi.
in previous patches v6-0001 to v6-0003, we added support for ALTER TABLE ADD
COLUMN with fast defaults for domains having non-volatile constraints.
inspired by another patch of mine: https://commitfest.postgresql.org/patch/5907
I believe it's doable to perform only a table scan when using ALTER TABLE ADD
COLUMN with a domain that has volatile constraints.
some example:
CREATE DOMAIN domain8 as int check((value + random(min=>11::int,
max=>11)) > 12);
CREATE TABLE t3(a int);
INSERT INTO t3 VALUES(1),(2);
ALTER TABLE t3 ADD COLUMN f domain8 default 1; --error while coercing to domain
ALTER TABLE t3 ADD COLUMN f domain8 default 20; --ok
The idea is the same as mentioned in [1],
for struct NewColumnValue, add another field (scan_only) to indicate
that we use table scan to evaluate the CoerceToDomain node.
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.
[1] https://postgr.es/m/CACJufxFhWyWzf2sJS9txSKeyA8hstxGDb8q2QWWwbo5Q1smPMA@mail.gmail.com
Attachments:
[text/x-patch] v7-0003-fast-default-for-domain-with-constraints.patch (9.9K, 2-v7-0003-fast-default-for-domain-with-constraints.patch)
download | inline diff:
From 78cf6b0d9013267ad7d8e2e9a1ac2fac564df077 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 2 Jun 2025 16:50:34 +0800
Subject: [PATCH v7 3/4] 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_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
[1]: https://git.postgresql.org/cgit/postgresql.git/commit/?id=aaaf9449ec6be62cb0d30ed3588dc384f56274bf
---
src/backend/commands/tablecmds.c | 46 ++++++++++++++--------
src/test/regress/expected/fast_default.out | 44 +++++++++++++++++++++
src/test/regress/sql/fast_default.sql | 37 +++++++++++++++++
3 files changed, 111 insertions(+), 16 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 082a3575d62..c58094a39c0 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7428,15 +7428,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.
@@ -7455,6 +7446,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(),
@@ -7472,8 +7464,18 @@ 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 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;
@@ -7515,14 +7517,18 @@ 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 computed CoerceToDomain is volatile
+ * or not via DomainHaveVolatileConstraints. We use soft error
+ * evaluation of CoerceToDomain, if evaluation failed, then set
+ * table rewrite to true.
*/
if (rel->rd_rel->relkind == RELKIND_RELATION &&
!colDef->generated &&
- !has_domain_constraints &&
+ !has_volatile &&
!contain_volatile_functions((Node *) defval))
{
EState *estate;
@@ -7532,10 +7538,18 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
/* Evaluate the default expression */
estate = CreateExecutorState();
- exprState = ExecPrepareExpr(defval, estate);
+ exprState = ExecPrepareExprSafe(defval, estate);
+
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..aa522cf8bfa 100644
--- a/src/test/regress/expected/fast_default.out
+++ b/src/test/regress/expected/fast_default.out
@@ -317,11 +317,55 @@ 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 not null;
+CREATE DOMAIN domain7 as int check(value > 10) DEFAULT random(min=>11, max=>100);
+CREATE DOMAIN domain8 as int check((value + random(min=>11::int, max=>11)) > 12);
+--tests with non-empty table.
+CREATE TABLE t3(a int);
+INSERT INTO t3 VALUES(1),(2);
+ALTER TABLE t3 ADD COLUMN b domain5; --table rewrite, then fail
+NOTICE: rewriting table t3 for reason 2
+ERROR: value for domain domain5 violates check constraint "domain5_check"
+ALTER TABLE t3 ADD COLUMN b domain6; --table rewrite, then fail
+NOTICE: rewriting table t3 for reason 2
+ERROR: domain domain6 does not allow null values
+ALTER TABLE t3 ADD COLUMN b domain5 default 12; --no table rewrite
+ALTER TABLE t3 ADD COLUMN c domain6 default 13; --no table rewrite
+--explicit column default expression override domain's default
+--expression, so no need table rewrite.
+ALTER TABLE t3 ADD COLUMN d domain7 default 14;
+SELECT attnum, attname, atthasmissing, atthasdef, attmissingval
+FROM pg_attribute
+WHERE attnum > 0 AND attrelid = 't3'::regclass AND attisdropped is false
+AND atthasmissing
+ORDER BY attnum;
+ attnum | attname | atthasmissing | atthasdef | attmissingval
+--------+---------+---------------+-----------+---------------
+ 2 | b | t | t | {12}
+ 3 | c | t | t | {13}
+ 4 | d | t | t | {14}
+(3 rows)
+
+--need table rewrite when we are applying domain volatile default expresion
+ALTER TABLE t3 ADD COLUMN e domain7;
+NOTICE: rewriting table t3 for reason 2
+-- need table rewrite when new column is domain with volatile constraints.
+ALTER TABLE t3 ADD COLUMN f domain8 default 14;
+NOTICE: rewriting table t3 for reason 2
+ALTER TABLE t3 ADD COLUMN f1 domain8;
+NOTICE: rewriting table t3 for reason 2
DROP TABLE t2;
+DROP TABLE t3;
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..269a39edf77 100644
--- a/src/test/regress/sql/fast_default.sql
+++ b/src/test/regress/sql/fast_default.sql
@@ -287,11 +287,48 @@ 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 not null;
+CREATE DOMAIN domain7 as int check(value > 10) DEFAULT random(min=>11, max=>100);
+CREATE DOMAIN domain8 as int check((value + random(min=>11::int, max=>11)) > 12);
+
+--tests with non-empty table.
+CREATE TABLE t3(a int);
+INSERT INTO t3 VALUES(1),(2);
+
+ALTER TABLE t3 ADD COLUMN b domain5; --table rewrite, then fail
+ALTER TABLE t3 ADD COLUMN b domain6; --table rewrite, then fail
+ALTER TABLE t3 ADD COLUMN b domain5 default 12; --no table rewrite
+ALTER TABLE t3 ADD COLUMN c domain6 default 13; --no table rewrite
+
+--explicit column default expression override domain's default
+--expression, so no need table rewrite.
+ALTER TABLE t3 ADD COLUMN d domain7 default 14;
+
+SELECT attnum, attname, atthasmissing, atthasdef, attmissingval
+FROM pg_attribute
+WHERE attnum > 0 AND attrelid = 't3'::regclass AND attisdropped is false
+AND atthasmissing
+ORDER BY attnum;
+
+--need table rewrite when we are applying domain volatile default expresion
+ALTER TABLE t3 ADD COLUMN e domain7;
+
+-- need table rewrite when new column is domain with volatile constraints.
+ALTER TABLE t3 ADD COLUMN f domain8 default 14;
+ALTER TABLE t3 ADD COLUMN f1 domain8;
+
DROP TABLE t2;
+DROP TABLE t3;
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
[text/x-patch] v7-0004-table-scan-only-when-adding-domain-with-volatile-constraints.patch (9.0K, 3-v7-0004-table-scan-only-when-adding-domain-with-volatile-constraints.patch)
download | inline diff:
From b2c056c2e0d5d7ca52bcb859beb2416f8802cb21 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 1 Sep 2025 13:47:47 +0800
Subject: [PATCH v7 4/4] table scan only when adding domain with volatile
constraints
When adding a new column, a table scan is sufficient to evaluate domains with
volatile check constraints.
example demo:
CREATE DOMAIN domain8 as int check((value + random(min=>11::int, max=>11)) > 12);
CREATE TABLE t3(a int);
INSERT INTO t3 VALUES(1),(2);
ALTER TABLE t3 ADD COLUMN f domain8 default 1; --error while coercing to domain
ALTER TABLE t3 ADD COLUMN f domain8 default 20; --ok
discussion: https://postgr.es/m/CACJufxE_+iZBR1i49k_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
---
src/backend/commands/tablecmds.c | 72 ++++++++++++++++++----
src/test/regress/expected/fast_default.out | 27 +++++++-
src/test/regress/sql/fast_default.sql | 15 ++++-
3 files changed, 98 insertions(+), 16 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index c58094a39c0..fcb378e79d5 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -230,6 +230,9 @@ typedef struct NewConstraint
* are just copied from the old table during ATRewriteTable. Note that the
* expr is an expression over *old* table values, except when is_generated
* is true; then it is an expression over columns of the *new* tuple.
+ *
+ * If scan_only is true, it means in phase3, table scan is enough to evaulate expr.
+ * Currently, this is supported when expr is CoerceToDomain.
*/
typedef struct NewColumnValue
{
@@ -237,6 +240,8 @@ typedef struct NewColumnValue
Expr *expr; /* expression to compute */
ExprState *exprstate; /* execution state */
bool is_generated; /* is it a GENERATED expression? */
+ bool scan_only; /* use table scan to evaulate expression,
+ * useful only when table rewrite is false */
} NewColumnValue;
/*
@@ -6008,7 +6013,8 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
* rebuild data.
*/
if (tab->constraints != NIL || tab->verify_new_notnull ||
- tab->partition_constraint != NULL)
+ tab->partition_constraint != NULL ||
+ tab->newvals)
ATRewriteTable(tab, InvalidOid);
/*
@@ -6118,7 +6124,9 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
Relation newrel;
TupleDesc oldTupDesc;
TupleDesc newTupDesc;
+ TupleDesc oldTupDescTemp;
bool needscan = false;
+ bool scan_only = false;
List *notnull_attrs;
List *notnull_virtual_attrs;
int i;
@@ -6135,6 +6143,7 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
*/
oldrel = table_open(tab->relid, NoLock);
oldTupDesc = tab->oldDesc;
+ oldTupDescTemp = CreateTupleDescCopy(oldTupDesc);
newTupDesc = RelationGetDescr(oldrel); /* includes all mods */
if (OidIsValid(OIDNewHeap))
@@ -6203,6 +6212,11 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
/* expr already planned */
ex->exprstate = ExecInitExpr((Expr *) ex->expr, NULL);
+ if (ex->scan_only && !tab->rewrite && !scan_only)
+ {
+ needscan = true;
+ scan_only = true;
+ }
}
notnull_attrs = notnull_virtual_attrs = NIL;
@@ -6431,6 +6445,43 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
* new constraints etc.
*/
insertslot = oldslot;
+
+ /*
+ * The tupdesc (newTupDesc) in oldslot already have the updated
+ * attribute changes. If we use it for below ExecEvalExpr, then
+ * CheckVarSlotCompatibility will fail. Therefore, we need to
+ * temporarily set oldslot's tts_tupleDescriptor equal to oldTupDesc.
+ * Essentially, what we're doing here is evaluating the
+ * CoerceToDomain node against the existing table slot.
+ */
+ if (scan_only)
+ {
+ Datum values pg_attribute_unused();
+ bool isnull pg_attribute_unused();
+
+ insertslot->tts_tupleDescriptor = oldTupDescTemp;
+ econtext->ecxt_scantuple = insertslot;
+
+ foreach(l, tab->newvals)
+ {
+ NewColumnValue *ex = lfirst(l);
+
+ if (!ex->scan_only)
+ continue;
+
+ /*
+ * we can not use ExecEvalExprNoReturn here, because we
+ * use ExecInitExpr compile NewColumnValue->expr. Here,
+ * we only check whether the oldslot value satisfies the
+ * domain constraint. So it is ok to override the value
+ * evaluated by ExecEvalExpr.
+ */
+ values = ExecEvalExpr(ex->exprstate, econtext, &isnull);
+ values = (Datum) 0;
+ isnull = true;
+ }
+ insertslot->tts_tupleDescriptor = newTupDesc;
+ }
}
/* Now check any constraints on the possibly-changed tuple */
@@ -7466,15 +7517,6 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
has_domain_constraints = DomainHaveVolatileConstraints(attribute->atttypid, &has_volatile);
- /*
- * Adding 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 */
if (!defval && has_domain_constraints)
{
@@ -7511,6 +7553,13 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
newval->expr = defval;
newval->is_generated = (colDef->generated != '\0');
+ /*
+ * If the domain has volatile constraints, table scan is enough to
+ * evaluate CoerceToDomain, no need table rewrite.
+ */
+ if (has_volatile && IsA(defval, CoerceToDomain))
+ newval->scan_only = true;
+
tab->newvals = lappend(tab->newvals, newval);
/*
@@ -7528,7 +7577,6 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
*/
if (rel->rd_rel->relkind == RELKIND_RELATION &&
!colDef->generated &&
- !has_volatile &&
!contain_volatile_functions((Node *) defval))
{
EState *estate;
@@ -7560,7 +7608,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
}
FreeExecutorState(estate);
}
- else
+ else if (!has_volatile)
{
/*
* Failed to use missing mode. We have to do a table rewrite
diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out
index aa522cf8bfa..136454a289d 100644
--- a/src/test/regress/expected/fast_default.out
+++ b/src/test/regress/expected/fast_default.out
@@ -351,11 +351,34 @@ ORDER BY attnum;
--need table rewrite when we are applying domain volatile default expresion
ALTER TABLE t3 ADD COLUMN e domain7;
NOTICE: rewriting table t3 for reason 2
--- need table rewrite when new column is domain with volatile constraints.
-ALTER TABLE t3 ADD COLUMN f domain8 default 14;
+--No need table rewrite when we are applying domain constraint volatile expresion,
+--but table scan is required.
+ALTER TABLE t3 ADD COLUMN f domain8 default 1; --error while coercing to domain
NOTICE: rewriting table t3 for reason 2
+ERROR: value for domain domain8 violates check constraint "domain8_check"
+ALTER TABLE t3 ADD COLUMN f domain8 default 20; --ok
+ALTER DOMAIN domain8 ADD CHECK(VALUE IS NOT NULL);
+--table rewrite then error, because f1 default value (NULL), does not satisfied with domain constraint
ALTER TABLE t3 ADD COLUMN f1 domain8;
NOTICE: rewriting table t3 for reason 2
+ERROR: value for domain domain8 violates check constraint "domain8_check1"
+SELECT f FROM t3;
+ f
+----
+ 20
+ 20
+(2 rows)
+
+SELECT attnum, attname, atthasmissing, atthasdef, attmissingval
+FROM pg_attribute
+WHERE attnum > 0 AND attrelid = 't3'::regclass AND attisdropped is false
+AND atthasmissing
+ORDER BY attnum;
+ attnum | attname | atthasmissing | atthasdef | attmissingval
+--------+---------+---------------+-----------+---------------
+ 6 | f | t | t | {20}
+(1 row)
+
DROP TABLE t2;
DROP TABLE t3;
DROP DOMAIN domain1;
diff --git a/src/test/regress/sql/fast_default.sql b/src/test/regress/sql/fast_default.sql
index 269a39edf77..178249de960 100644
--- a/src/test/regress/sql/fast_default.sql
+++ b/src/test/regress/sql/fast_default.sql
@@ -315,10 +315,21 @@ ORDER BY attnum;
--need table rewrite when we are applying domain volatile default expresion
ALTER TABLE t3 ADD COLUMN e domain7;
--- need table rewrite when new column is domain with volatile constraints.
-ALTER TABLE t3 ADD COLUMN f domain8 default 14;
+--No need table rewrite when we are applying domain constraint volatile expresion,
+--but table scan is required.
+ALTER TABLE t3 ADD COLUMN f domain8 default 1; --error while coercing to domain
+ALTER TABLE t3 ADD COLUMN f domain8 default 20; --ok
+ALTER DOMAIN domain8 ADD CHECK(VALUE IS NOT NULL);
+--table rewrite then error, because f1 default value (NULL), does not satisfied with domain constraint
ALTER TABLE t3 ADD COLUMN f1 domain8;
+SELECT f FROM t3;
+SELECT attnum, attname, atthasmissing, atthasdef, attmissingval
+FROM pg_attribute
+WHERE attnum > 0 AND attrelid = 't3'::regclass AND attisdropped is false
+AND atthasmissing
+ORDER BY attnum;
+
DROP TABLE t2;
DROP TABLE t3;
DROP DOMAIN domain1;
--
2.34.1
[text/x-patch] v7-0002-add-function-DomainHaveVolatileConstraints.patch (2.7K, 4-v7-0002-add-function-DomainHaveVolatileConstraints.patch)
download | inline diff:
From da5efb88c112d49a8800e81f857006bb58deee92 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 19 May 2025 11:11:01 +0800
Subject: [PATCH v7 2/4] add function DomainHaveVolatileConstraints
the signature is:
extern bool DomainHaveVolatileConstraints(Oid type_id, bool *have_volatile);
Returns true if the Domain has any constraints. If you want check this domain
have any volatile check constraints, make sure have_volatile is not NULL.
discussion: https://postgr.es/m/CACJufxE_+iZBR1i49k_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
---
src/backend/utils/cache/typcache.c | 40 ++++++++++++++++++++++++++++++
src/include/utils/typcache.h | 1 +
2 files changed, 41 insertions(+)
diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c
index 6a347698edf..da9ed0d29c8 100644
--- a/src/backend/utils/cache/typcache.c
+++ b/src/backend/utils/cache/typcache.c
@@ -1499,6 +1499,46 @@ 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, the behavior is
+ * equivalent to that of DomainHasConstraints.
+ */
+bool
+DomainHaveVolatileConstraints(Oid type_id, bool *have_volatile)
+{
+ 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);
+
+ if (typentry->domainData != NULL)
+ {
+ ListCell *lc;
+
+ foreach(lc, typentry->domainData->constraints)
+ {
+ DomainConstraintState *r = (DomainConstraintState *) lfirst(lc);
+
+ if (r->constrainttype == DOM_CONSTRAINT_CHECK &&
+ contain_volatile_functions((Node *) r->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 1cb30f1818c..aa1c86e35c3 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
[text/x-patch] v7-0001-soft-error-variant-of-ExecPrepareExpr-ExecInitExpr.patch (3.9K, 5-v7-0001-soft-error-variant-of-ExecPrepareExpr-ExecInitExpr.patch)
download | inline diff:
From 12f6bfc936b1d51d7956b035066eacafe287c3f7 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 24 Mar 2025 16:07:46 +0800
Subject: [PATCH v7 1/4] soft error variant of ExecPrepareExpr, ExecInitExpr
ExecInitExprSafe: soft error of ExecInitExpr.
ExecPrepareExprSafe: soft error of ExecPrepareExpr.
ExecPrepareExprSafe initialize for expression execution with soft error support.
not all expression node support it. Like node CoerceToDomain support it.
XXX more comments.
discussion: https://postgr.es/m/CACJufxE_+iZBR1i49k_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
---
src/backend/executor/execExpr.c | 63 +++++++++++++++++++++++++++++++++
src/include/executor/executor.h | 2 ++
2 files changed, 65 insertions(+)
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index f1569879b52..9182ba446a0 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -170,6 +170,47 @@ ExecInitExpr(Expr *node, PlanState *parent)
return state;
}
+/*
+ * ExecInitExprSafe: soft error variant of ExecInitExpr.
+ *
+ * use it only for expression nodes support soft errors, not all expression
+ * nodes support it.
+*/
+ExprState *
+ExecInitExprSafe(Expr *node, PlanState *parent)
+{
+ ExprState *state;
+ ExprEvalStep scratch = {0};
+
+ /* Special case: NULL expression produces a NULL ExprState pointer */
+ if (node == NULL)
+ return NULL;
+
+ /* Initialize ExprState with empty step list */
+ state = makeNode(ExprState);
+ state->expr = node;
+ state->parent = parent;
+ state->ext_params = NULL;
+ state->escontext = makeNode(ErrorSaveContext);
+ state->escontext->type = T_ErrorSaveContext;
+ state->escontext->error_occurred = false;
+ state->escontext->details_wanted = true;
+
+ /* Insert setup steps as needed */
+ ExecCreateExprSetupSteps(state, (Node *) node);
+
+ /* Compile the expression proper */
+ ExecInitExprRec(node, state, &state->resvalue, &state->resnull);
+
+ /* Finally, append a DONE step */
+ scratch.opcode = EEOP_DONE_RETURN;
+ ExprEvalPushStep(state, &scratch);
+
+ ExecReadyExpr(state);
+
+ return state;
+}
+
/*
* ExecInitExprWithParams: prepare a standalone expression tree for execution
*
@@ -778,6 +819,28 @@ ExecPrepareExpr(Expr *node, EState *estate)
return result;
}
+/*
+ * ExecPrepareExprSafe: soft error variant of ExecPrepareExpr.
+ *
+ * use it when expression node *support* soft error expression execution.
+ */
+ExprState *
+ExecPrepareExprSafe(Expr *node, EState *estate)
+{
+ ExprState *result;
+ MemoryContext oldcontext;
+
+ oldcontext = MemoryContextSwitchTo(estate->es_query_cxt);
+
+ node = expression_planner(node);
+
+ result = ExecInitExprSafe(node, NULL);
+
+ MemoryContextSwitchTo(oldcontext);
+
+ return result;
+}
+
/*
* ExecPrepareQual --- initialize for qual execution outside a normal
* Plan tree context.
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index 10dcea037c3..eb5ecf9d59f 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -320,6 +320,7 @@ ExecProcNode(PlanState *node)
* prototypes from functions in execExpr.c
*/
extern ExprState *ExecInitExpr(Expr *node, PlanState *parent);
+extern ExprState *ExecInitExprSafe(Expr *node, PlanState *parent);
extern ExprState *ExecInitExprWithParams(Expr *node, ParamListInfo ext_params);
extern ExprState *ExecInitQual(List *qual, PlanState *parent);
extern ExprState *ExecInitCheck(List *qual, PlanState *parent);
@@ -368,6 +369,7 @@ extern ProjectionInfo *ExecBuildUpdateProjection(List *targetList,
TupleTableSlot *slot,
PlanState *parent);
extern ExprState *ExecPrepareExpr(Expr *node, EState *estate);
+extern ExprState *ExecPrepareExprSafe(Expr *node, EState *estate);
extern ExprState *ExecPrepareQual(List *qual, EState *estate);
extern ExprState *ExecPrepareCheck(List *qual, EState *estate);
extern List *ExecPrepareExprList(List *nodes, EState *estate);
--
2.34.1
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: support fast default for domain with constraints
2025-03-06 13:53 Re: support fast default for domain with constraints jian he <[email protected]>
2025-03-24 11:14 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-05-19 07:05 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-06-02 09:56 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-09-01 06:27 ` Re: support fast default for domain with constraints jian he <[email protected]>
@ 2026-01-26 07:52 ` jian he <[email protected]>
2026-03-10 17:18 ` Re: support fast default for domain with constraints Andrew Dunstan <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: jian he @ 2026-01-26 07:52 UTC (permalink / raw)
To: pgsql-hackers
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/
Attachments:
[text/x-patch] v8-0002-add-function-DomainHaveVolatileConstraints.patch (2.9K, 2-v8-0002-add-function-DomainHaveVolatileConstraints.patch)
download | inline diff:
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_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@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
[text/x-patch] v8-0001-Enable-soft-error-handling-in-ExecPrepareExpr-and-ExecInitExpr.patch (3.2K, 3-v8-0001-Enable-soft-error-handling-in-ExecPrepareExpr-and-ExecInitExpr.patch)
download | inline diff:
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_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@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
[text/x-patch] v8-0003-fast-default-for-domain-with-constraints.patch (11.1K, 4-v8-0003-fast-default-for-domain-with-constraints.patch)
download | inline diff:
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_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: support fast default for domain with constraints
2025-03-06 13:53 Re: support fast default for domain with constraints jian he <[email protected]>
2025-03-24 11:14 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-05-19 07:05 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-06-02 09:56 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-09-01 06:27 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-01-26 07:52 ` Re: support fast default for domain with constraints jian he <[email protected]>
@ 2026-03-10 17:18 ` Andrew Dunstan <[email protected]>
2026-03-11 03:43 ` Re: support fast default for domain with constraints jian he <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Andrew Dunstan @ 2026-03-10 17:18 UTC (permalink / raw)
To: jian he <[email protected]>; pgsql-hackers
On 2026-01-26 Mo 2:52 AM, jian he wrote:
> 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.
>
>
>
here's an updated patch set.
main changes:
. renamed DomainHaveVolatileConstraints renamed to
DomainHasVolatileConstraints, improved the comments and code structure
. squashed two commits into one, as there's only one user for the
soft-error functions
. rename ExecPrepareExprExtended to ExecPrepareExprWithContext and
ExecInitExprExtended to ExecInitExprWithContext, with improved comments.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Attachments:
[text/x-patch] v9-0001-Add-DomainHasVolatileConstraints-to-check-constraint.patch (3.1K, 2-v9-0001-Add-DomainHasVolatileConstraints-to-check-constraint.patch)
download | inline diff:
From 05fbed0d9a7d7402c7a9f7d782405ef9b5ff59f8 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 10 Mar 2026 11:06:53 -0400
Subject: [v9 1/2] Add DomainHasVolatileConstraints() to check constraint
volatility
Add a utility function that checks whether a domain type has any
constraints, and optionally whether any CHECK constraint contains
volatile expressions. This is needed by a subsequent commit that
enables the fast default optimization for domains with non-volatile
constraints: we can safely evaluate such constraints once at ALTER TABLE
time, but volatile constraints require a full table rewrite.
Discussion: https://postgr.es/m/CACJufxE_+iZBR1i49k_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
Commitfest: https://commitfest.postgresql.org/patch/5641
---
src/backend/utils/cache/typcache.c | 45 ++++++++++++++++++++++++++++++
src/include/utils/typcache.h | 1 +
2 files changed, 46 insertions(+)
diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c
index 627e534609a..e33d4b807bd 100644
--- a/src/backend/utils/cache/typcache.c
+++ b/src/backend/utils/cache/typcache.c
@@ -1502,6 +1502,51 @@ DomainHasConstraints(Oid type_id)
}
+/*
+ * DomainHasVolatileConstraints --- check if a domain has constraints with
+ * volatile expressions
+ *
+ * Returns true if the domain has any constraints at all. If have_volatile
+ * is not NULL, also checks whether any CHECK constraint contains a volatile
+ * expression and sets *have_volatile accordingly.
+ *
+ * The caller must initialize *have_volatile before calling (typically to
+ * false). This function only ever sets it to true, never to false.
+ *
+ * This is defined to return false, not fail, if type is not a domain.
+ */
+bool
+DomainHasVolatileConstraints(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)
+ {
+ if (have_volatile)
+ {
+ foreach_node(DomainConstraintState, constrstate,
+ typentry->domainData->constraints)
+ {
+ if (constrstate->constrainttype == DOM_CONSTRAINT_CHECK &&
+ contain_volatile_functions((Node *) constrstate->check_expr))
+ {
+ *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..38366ff159e 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 DomainHasVolatileConstraints(Oid type_id, bool *have_volatile);
extern TupleDesc lookup_rowtype_tupdesc(Oid type_id, int32 typmod);
--
2.43.0
[text/x-patch] v9-0002-Enable-fast-default-for-domains-with-non-volatile-co.patch (15.3K, 3-v9-0002-Enable-fast-default-for-domains-with-non-volatile-co.patch)
download | inline diff:
From 3f148f4be50862185a57ba473c990479723bd5e8 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 10 Mar 2026 11:08:29 -0400
Subject: [v9 2/2] Enable fast default for domains with non-volatile
constraints
Previously, ALTER TABLE ADD COLUMN always forced a table rewrite when
the column type was a domain with constraints (CHECK or NOT NULL), even
if the default value satisfied those constraints. This was because
contain_volatile_functions() considers CoerceToDomain immutable, so
the code conservatively assumed any constrained domain might fail.
Improve this by using soft error handling (ErrorSaveContext) to evaluate
the CoerceToDomain expression at ALTER TABLE time. If the default value
passes the domain's constraints, the value is stored as a "missing"
attribute default and no table rewrite is needed. If the constraint
check fails, we fall back to a table rewrite, preserving the historical
behavior that constraint violations are only raised when the table
actually contains rows.
Domains with volatile constraint expressions always require a table
rewrite since the constraint result could differ per evaluation and
cannot be cached.
Discussion: https://postgr.es/m/CACJufxE_+iZBR1i49k_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
Commitfest: https://commitfest.postgresql.org/patch/5641
---
src/backend/commands/tablecmds.c | 61 ++++++++++++++++------
src/backend/executor/execExpr.c | 35 ++++++++++++-
src/include/executor/executor.h | 2 +
src/test/regress/expected/fast_default.out | 50 ++++++++++++++++++
src/test/regress/sql/fast_default.sql | 40 ++++++++++++++
5 files changed, 170 insertions(+), 18 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 85242dcc245..2edb036449a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7478,15 +7478,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.
@@ -7505,6 +7496,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(),
@@ -7522,8 +7514,21 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
else
defval = (Expr *) build_column_default(rel, attribute->attnum);
+ has_domain_constraints =
+ DomainHasVolatileConstraints(attribute->atttypid, &has_volatile);
+
+ /*
+ * If the domain has volatile constraints, we must do a table rewrite
+ * since the constraint result could differ per row and cannot be
+ * evaluated once and cached as a missing value.
+ */
+ 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;
@@ -7565,27 +7570,49 @@ 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 expressions can't be volatile (stable is OK).
+ *
+ * Note that contain_volatile_functions considers CoerceToDomain
+ * immutable, so we rely on DomainHasVolatileConstraints (called
+ * above) rather than checking defval alone.
+ *
+ * For domains with non-volatile constraints, we evaluate the
+ * default using soft error handling: if the constraint check
+ * fails (e.g., CHECK(value > 10) with DEFAULT 8), we fall back to
+ * a table rewrite. This preserves the historical behavior that
+ * such a failure is only raised when the table has rows.
*/
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 */
+ /* Evaluate the default expression with soft errors */
estate = CreateExecutorState();
- exprState = ExecPrepareExpr(defval, estate);
+ exprState = ExecPrepareExprWithContext(defval, estate,
+ (Node *) &escontext);
missingval = ExecEvalExpr(exprState,
GetPerTupleExprContext(estate),
&missingIsNull);
+
+ /*
+ * If the domain constraint check failed, fall back to a table
+ * rewrite. Phase 3 will re-evaluate with hard errors, so the
+ * user gets an error only if the table has rows.
+ */
+ if (SOFT_ERROR_OCCURRED(&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/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 088eca24021..c15749646e0 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -141,6 +141,26 @@ static void ExecInitJsonCoercion(ExprState *state, JsonReturning *returning,
*/
ExprState *
ExecInitExpr(Expr *node, PlanState *parent)
+{
+ return ExecInitExprWithContext(node, parent, NULL);
+}
+
+/*
+ * ExecInitExprWithContext: same as ExecInitExpr, but with an optional
+ * ErrorSaveContext for soft error handling.
+ *
+ * When 'escontext' is non-NULL, expression nodes that support soft errors
+ * (currently CoerceToDomain's NOT NULL and CHECK constraint steps) will use
+ * errsave() instead of ereport(), allowing the caller to detect and handle
+ * failures without a transaction abort.
+ *
+ * The escontext must be provided at initialization time (not after), because
+ * it is copied into per-step data during expression compilation.
+ *
+ * Not all expression node types support soft errors. If in doubt, pass NULL.
+ */
+ExprState *
+ExecInitExprWithContext(Expr *node, PlanState *parent, Node *escontext)
{
ExprState *state;
ExprEvalStep scratch = {0};
@@ -154,6 +174,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 +784,18 @@ ExecBuildUpdateProjection(List *targetList,
*/
ExprState *
ExecPrepareExpr(Expr *node, EState *estate)
+{
+ return ExecPrepareExprWithContext(node, estate, NULL);
+}
+
+/*
+ * ExecPrepareExprWithContext: same as ExecPrepareExpr, but with an optional
+ * ErrorSaveContext for soft error handling during domain constraint evaluation.
+ *
+ * See ExecInitExprWithContext for details on the escontext parameter.
+ */
+ExprState *
+ExecPrepareExprWithContext(Expr *node, EState *estate, Node *escontext)
{
ExprState *result;
MemoryContext oldcontext;
@@ -771,7 +804,7 @@ ExecPrepareExpr(Expr *node, EState *estate)
node = expression_planner(node);
- result = ExecInitExpr(node, NULL);
+ result = ExecInitExprWithContext(node, NULL, escontext);
MemoryContextSwitchTo(oldcontext);
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index d46ba59895d..82c442d23f8 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 *ExecInitExprWithContext(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 *ExecPrepareExprWithContext(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);
diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out
index ccbcdf8403f..bb3a83385f6 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 overrides 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..597a547ff72 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 overrides 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.43.0
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: support fast default for domain with constraints
2025-03-06 13:53 Re: support fast default for domain with constraints jian he <[email protected]>
2025-03-24 11:14 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-05-19 07:05 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-06-02 09:56 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-09-01 06:27 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-01-26 07:52 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-03-10 17:18 ` Re: support fast default for domain with constraints Andrew Dunstan <[email protected]>
@ 2026-03-11 03:43 ` jian he <[email protected]>
2026-03-12 03:36 ` Re: support fast default for domain with constraints jian he <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: jian he @ 2026-03-11 03:43 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: pgsql-hackers
On Wed, Mar 11, 2026 at 1:18 AM Andrew Dunstan <[email protected]> wrote:
>
> here's an updated patch set.
>
+/*
+ * ExecPrepareExprWithContext: same as ExecPrepareExpr, but with an optional
+ * ErrorSaveContext for soft error handling during domain constraint
evaluation.
+ *
+ * See ExecInitExprWithContext for details on the escontext parameter.
+ */
+ExprState *
+ExecPrepareExprWithContext(Expr *node, EState *estate, Node *escontext)
Since ExecPrepareExprWithContext can be used more broadly, we should delete the
mention of domain constraint from the above comments.
I checked alter_table.sgml again, no need to change it, I think.
Slightly changed the regression test comments.
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v10-0002-Enable-fast-default-for-domains-with-non-volatile-constraints.patch (15.4K, 2-v10-0002-Enable-fast-default-for-domains-with-non-volatile-constraints.patch)
download | inline diff:
From 46b911d647dcb41f602056bef9b178d0d4f3f6f5 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Wed, 11 Mar 2026 11:37:32 +0800
Subject: [PATCH v10 2/2] Enable fast default for domains with non-volatile
constraints
Previously, ALTER TABLE ADD COLUMN always forced a table rewrite when
the column type was a domain with constraints (CHECK or NOT NULL), even
if the default value satisfied those constraints. This was because
contain_volatile_functions() considers CoerceToDomain immutable, so
the code conservatively assumed any constrained domain might fail.
Improve this by using soft error handling (ErrorSaveContext) to evaluate
the CoerceToDomain expression at ALTER TABLE time. If the default value
passes the domain's constraints, the value is stored as a "missing"
attribute default and no table rewrite is needed. If the constraint
check fails, we fall back to a table rewrite, preserving the historical
behavior that constraint violations are only raised when the table
actually contains rows.
Domains with volatile constraint expressions always require a table
rewrite since the constraint result could differ per evaluation and
cannot be cached.
Discussion: https://postgr.es/m/CACJufxE_+iZBR1i49k_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
Commitfest: https://commitfest.postgresql.org/patch/5641
---
src/backend/commands/tablecmds.c | 61 ++++++++++++++++------
src/backend/executor/execExpr.c | 35 ++++++++++++-
src/include/executor/executor.h | 2 +
src/test/regress/expected/fast_default.out | 51 ++++++++++++++++++
src/test/regress/sql/fast_default.sql | 41 +++++++++++++++
5 files changed, 172 insertions(+), 18 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 85242dcc245..2edb036449a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -7478,15 +7478,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.
@@ -7505,6 +7496,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(),
@@ -7522,8 +7514,21 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
else
defval = (Expr *) build_column_default(rel, attribute->attnum);
+ has_domain_constraints =
+ DomainHasVolatileConstraints(attribute->atttypid, &has_volatile);
+
+ /*
+ * If the domain has volatile constraints, we must do a table rewrite
+ * since the constraint result could differ per row and cannot be
+ * evaluated once and cached as a missing value.
+ */
+ 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;
@@ -7565,27 +7570,49 @@ 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 expressions can't be volatile (stable is OK).
+ *
+ * Note that contain_volatile_functions considers CoerceToDomain
+ * immutable, so we rely on DomainHasVolatileConstraints (called
+ * above) rather than checking defval alone.
+ *
+ * For domains with non-volatile constraints, we evaluate the
+ * default using soft error handling: if the constraint check
+ * fails (e.g., CHECK(value > 10) with DEFAULT 8), we fall back to
+ * a table rewrite. This preserves the historical behavior that
+ * such a failure is only raised when the table has rows.
*/
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 */
+ /* Evaluate the default expression with soft errors */
estate = CreateExecutorState();
- exprState = ExecPrepareExpr(defval, estate);
+ exprState = ExecPrepareExprWithContext(defval, estate,
+ (Node *) &escontext);
missingval = ExecEvalExpr(exprState,
GetPerTupleExprContext(estate),
&missingIsNull);
+
+ /*
+ * If the domain constraint check failed, fall back to a table
+ * rewrite. Phase 3 will re-evaluate with hard errors, so the
+ * user gets an error only if the table has rows.
+ */
+ if (SOFT_ERROR_OCCURRED(&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/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 088eca24021..4c64d0efb2b 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -141,6 +141,26 @@ static void ExecInitJsonCoercion(ExprState *state, JsonReturning *returning,
*/
ExprState *
ExecInitExpr(Expr *node, PlanState *parent)
+{
+ return ExecInitExprWithContext(node, parent, NULL);
+}
+
+/*
+ * ExecInitExprWithContext: same as ExecInitExpr, but with an optional
+ * ErrorSaveContext for soft error handling.
+ *
+ * When 'escontext' is non-NULL, expression nodes that support soft errors
+ * (currently CoerceToDomain's NOT NULL and CHECK constraint steps) will use
+ * errsave() instead of ereport(), allowing the caller to detect and handle
+ * failures without a transaction abort.
+ *
+ * The escontext must be provided at initialization time (not after), because
+ * it is copied into per-step data during expression compilation.
+ *
+ * Not all expression node types support soft errors. If in doubt, pass NULL.
+ */
+ExprState *
+ExecInitExprWithContext(Expr *node, PlanState *parent, Node *escontext)
{
ExprState *state;
ExprEvalStep scratch = {0};
@@ -154,6 +174,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 +784,18 @@ ExecBuildUpdateProjection(List *targetList,
*/
ExprState *
ExecPrepareExpr(Expr *node, EState *estate)
+{
+ return ExecPrepareExprWithContext(node, estate, NULL);
+}
+
+/*
+ * ExecPrepareExprWithContext: same as ExecPrepareExpr, but with an optional
+ * ErrorSaveContext for soft error handling.
+ *
+ * See ExecInitExprWithContext for details on the escontext parameter.
+ */
+ExprState *
+ExecPrepareExprWithContext(Expr *node, EState *estate, Node *escontext)
{
ExprState *result;
MemoryContext oldcontext;
@@ -771,7 +804,7 @@ ExecPrepareExpr(Expr *node, EState *estate)
node = expression_planner(node);
- result = ExecInitExpr(node, NULL);
+ result = ExecInitExprWithContext(node, NULL, escontext);
MemoryContextSwitchTo(oldcontext);
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index d46ba59895d..82c442d23f8 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 *ExecInitExprWithContext(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 *ExecPrepareExprWithContext(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);
diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out
index ccbcdf8403f..c8eadb6cc16 100644
--- a/src/test/regress/expected/fast_default.out
+++ b/src/test/regress/expected/fast_default.out
@@ -317,11 +317,62 @@ 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;
+CREATE TABLE test_add_domain_col(a int);
+-- table rewrite, not fail because test_add_domain_col is empty table
+ALTER TABLE test_add_domain_col ADD COLUMN a1 domain5;
+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);
+-- tests with non-empty table
+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 overrides 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..3a0c0a4c7e1 100644
--- a/src/test/regress/sql/fast_default.sql
+++ b/src/test/regress/sql/fast_default.sql
@@ -287,11 +287,52 @@ 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;
+
+CREATE TABLE test_add_domain_col(a int);
+-- table rewrite, not fail because test_add_domain_col is empty table
+ALTER TABLE test_add_domain_col ADD COLUMN a1 domain5;
+ALTER TABLE test_add_domain_col DROP COLUMN a1;
+INSERT INTO test_add_domain_col VALUES(1),(2);
+
+-- tests with non-empty table
+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 overrides 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
[text/x-patch] v10-0001-Add-DomainHasVolatileConstraints-to-check-constraint-volatility.patch (3.1K, 3-v10-0001-Add-DomainHasVolatileConstraints-to-check-constraint-volatility.patch)
download | inline diff:
From 070da693d0aaba70b452b40776200b7b0698df86 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Wed, 11 Mar 2026 10:04:13 +0800
Subject: [PATCH v10 1/2] Add DomainHasVolatileConstraints() to check
constraint volatility
Add a utility function that checks whether a domain type has any
constraints, and optionally whether any CHECK constraint contains
volatile expressions. This is needed by a subsequent commit that
enables the fast default optimization for domains with non-volatile
constraints: we can safely evaluate such constraints once at ALTER TABLE
time, but volatile constraints require a full table rewrite.
Discussion: https://postgr.es/m/CACJufxE_+iZBR1i49k_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@mail.gmail.com
Commitfest: https://commitfest.postgresql.org/patch/5641
---
src/backend/utils/cache/typcache.c | 45 ++++++++++++++++++++++++++++++
src/include/utils/typcache.h | 1 +
2 files changed, 46 insertions(+)
diff --git a/src/backend/utils/cache/typcache.c b/src/backend/utils/cache/typcache.c
index 627e534609a..e33d4b807bd 100644
--- a/src/backend/utils/cache/typcache.c
+++ b/src/backend/utils/cache/typcache.c
@@ -1502,6 +1502,51 @@ DomainHasConstraints(Oid type_id)
}
+/*
+ * DomainHasVolatileConstraints --- check if a domain has constraints with
+ * volatile expressions
+ *
+ * Returns true if the domain has any constraints at all. If have_volatile
+ * is not NULL, also checks whether any CHECK constraint contains a volatile
+ * expression and sets *have_volatile accordingly.
+ *
+ * The caller must initialize *have_volatile before calling (typically to
+ * false). This function only ever sets it to true, never to false.
+ *
+ * This is defined to return false, not fail, if type is not a domain.
+ */
+bool
+DomainHasVolatileConstraints(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)
+ {
+ if (have_volatile)
+ {
+ foreach_node(DomainConstraintState, constrstate,
+ typentry->domainData->constraints)
+ {
+ if (constrstate->constrainttype == DOM_CONSTRAINT_CHECK &&
+ contain_volatile_functions((Node *) constrstate->check_expr))
+ {
+ *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..38366ff159e 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 DomainHasVolatileConstraints(Oid type_id, bool *have_volatile);
extern TupleDesc lookup_rowtype_tupdesc(Oid type_id, int32 typmod);
--
2.34.1
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: support fast default for domain with constraints
2025-03-06 13:53 Re: support fast default for domain with constraints jian he <[email protected]>
2025-03-24 11:14 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-05-19 07:05 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-06-02 09:56 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-09-01 06:27 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-01-26 07:52 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-03-10 17:18 ` Re: support fast default for domain with constraints Andrew Dunstan <[email protected]>
2026-03-11 03:43 ` Re: support fast default for domain with constraints jian he <[email protected]>
@ 2026-03-12 03:36 ` jian he <[email protected]>
2026-03-12 10:15 ` Re: support fast default for domain with constraints Viktor Holmberg <[email protected]>
2026-03-12 22:07 ` Re: support fast default for domain with constraints Andrew Dunstan <[email protected]>
0 siblings, 2 replies; 13+ messages in thread
From: jian he @ 2026-03-12 03:36 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; Viktor Holmberg <[email protected]>; +Cc: pgsql-hackers
On Thu, Mar 12, 2026 at 3:50 AM Andrew Dunstan <[email protected]> wrote:
> Also added some tests.
V11 looks good to me.
On Wed, Mar 11, 2026 at 6:34 PM Viktor Holmberg <[email protected]> wrote:
>
> I’ve been burned my this issue in the past so would be great if this could get in.
>
> + /*
> + * If the domain has volatile constraints, we must do a table rewrite
> + * since the constraint result could differ per row and cannot be
> + * evaluated once and cached as a missing value.
> + */
> + if (has_volatile)
> + {
> + Assert(has_domain_constraints);
> + tab->rewrite |= AT_REWRITE_DEFAULT_VAL;
> + }
>
> I'm not sure. But seems to me this makes the pre-existing guard for virtual columns
> redundant?
> I mean this code on line 7633:
> if (colDef->generated != ATTRIBUTE_GENERATED_VIRTUAL)
> tab->rewrite |= AT_REWRITE_DEFAULT_VAL;
>
If the first `if (has_volatile)` is false, then
> if (colDef->generated != ATTRIBUTE_GENERATED_VIRTUAL)
> tab->rewrite |= AT_REWRITE_DEFAULT_VAL;
is still reachable.
> Also, perhaps virtual generated columns could use a test?
Virtual generated columns based on domain are not currently supported.
I have a patch for it: https://commitfest.postgresql.org/patch/5725
but it's not doable now because of
https://git.postgresql.org/cgit/postgresql.git/commit/?id=0cd69b3d7ef357f2b43258de5831c4de0bd51dec
You may also be interested in https://commitfest.postgresql.org/patch/5907
--
jian
https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: support fast default for domain with constraints
2025-03-06 13:53 Re: support fast default for domain with constraints jian he <[email protected]>
2025-03-24 11:14 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-05-19 07:05 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-06-02 09:56 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-09-01 06:27 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-01-26 07:52 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-03-10 17:18 ` Re: support fast default for domain with constraints Andrew Dunstan <[email protected]>
2026-03-11 03:43 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-03-12 03:36 ` Re: support fast default for domain with constraints jian he <[email protected]>
@ 2026-03-12 10:15 ` Viktor Holmberg <[email protected]>
1 sibling, 0 replies; 13+ messages in thread
From: Viktor Holmberg @ 2026-03-12 10:15 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; jian he <[email protected]>; +Cc: pgsql-hackers
On 12 Mar 2026 at 04:36 +0100, jian he <[email protected]>, wrote:
> On Thu, Mar 12, 2026 at 3:50 AM Andrew Dunstan <[email protected]> wrote:
> > Also added some tests.
>
> V11 looks good to me.
>
>
> On Wed, Mar 11, 2026 at 6:34 PM Viktor Holmberg <[email protected]> wrote:
> >
> > I’ve been burned my this issue in the past so would be great if this could get in.
> >
> > + /*
> > + * If the domain has volatile constraints, we must do a table rewrite
> > + * since the constraint result could differ per row and cannot be
> > + * evaluated once and cached as a missing value.
> > + */
> > + if (has_volatile)
> > + {
> > + Assert(has_domain_constraints);
> > + tab->rewrite |= AT_REWRITE_DEFAULT_VAL;
> > + }
> >
> > I'm not sure. But seems to me this makes the pre-existing guard for virtual columns
> > redundant?
> > I mean this code on line 7633:
> > if (colDef->generated != ATTRIBUTE_GENERATED_VIRTUAL)
> > tab->rewrite |= AT_REWRITE_DEFAULT_VAL;
> >
>
> If the first `if (has_volatile)` is false, then
> > if (colDef->generated != ATTRIBUTE_GENERATED_VIRTUAL)
> > tab->rewrite |= AT_REWRITE_DEFAULT_VAL;
> is still reachable.
>
> > Also, perhaps virtual generated columns could use a test?
>
> Virtual generated columns based on domain are not currently supported.
> I have a patch for it: https://commitfest.postgresql.org/patch/5725
> but it's not doable now because of
> https://git.postgresql.org/cgit/postgresql.git/commit/?id=0cd69b3d7ef357f2b43258de5831c4de0bd51dec
> You may also be interested in https://commitfest.postgresql.org/patch/5907
>
> --
> jian
> https://www.enterprisedb.com/
Nice, v11 looks good to me. I’ll change the status of the commitfest entry to ready for committer.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: support fast default for domain with constraints
2025-03-06 13:53 Re: support fast default for domain with constraints jian he <[email protected]>
2025-03-24 11:14 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-05-19 07:05 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-06-02 09:56 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-09-01 06:27 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-01-26 07:52 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-03-10 17:18 ` Re: support fast default for domain with constraints Andrew Dunstan <[email protected]>
2026-03-11 03:43 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-03-12 03:36 ` Re: support fast default for domain with constraints jian he <[email protected]>
@ 2026-03-12 22:07 ` Andrew Dunstan <[email protected]>
2026-03-13 10:13 ` Re: support fast default for domain with constraints Andrew Dunstan <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Andrew Dunstan @ 2026-03-12 22:07 UTC (permalink / raw)
To: jian he <[email protected]>; Viktor Holmberg <[email protected]>; +Cc: pgsql-hackers
On 2026-03-11 We 11:36 PM, jian he wrote:
> On Thu, Mar 12, 2026 at 3:50 AM Andrew Dunstan<[email protected]> wrote:
>> Also added some tests.
> V11 looks good to me.
Pushed after further minor review.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: support fast default for domain with constraints
2025-03-06 13:53 Re: support fast default for domain with constraints jian he <[email protected]>
2025-03-24 11:14 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-05-19 07:05 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-06-02 09:56 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-09-01 06:27 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-01-26 07:52 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-03-10 17:18 ` Re: support fast default for domain with constraints Andrew Dunstan <[email protected]>
2026-03-11 03:43 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-03-12 03:36 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-03-12 22:07 ` Re: support fast default for domain with constraints Andrew Dunstan <[email protected]>
@ 2026-03-13 10:13 ` Andrew Dunstan <[email protected]>
2026-03-13 10:17 ` Re: support fast default for domain with constraints Viktor Holmberg <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Andrew Dunstan @ 2026-03-13 10:13 UTC (permalink / raw)
To: Viktor Holmberg <[email protected]>; +Cc: pgsql-hackers
On 2026-03-13 Fr 4:43 AM, Viktor Holmberg wrote:
> Nice that this was pushed. On a minor note, I saw that my email got
> confused in the commit ([email protected]
> <https://mailto:[email protected];) instead of [email protected].
> (I don’t know what aiven.io is). I don’t know if there is a way to
> change this without messing up the git log? If not it’s no problem,
> probably unlikely that anyone will contact me about it anyways.
It's not really possible. My deepest apologies. Some how or other the
gadget I have for ensuring I credit everyone I should got confused. I
will disable it until I can figure out where it went wrong.
cheers
andrew
--
Andrew Dunstan
EDB:https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: support fast default for domain with constraints
2025-03-06 13:53 Re: support fast default for domain with constraints jian he <[email protected]>
2025-03-24 11:14 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-05-19 07:05 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-06-02 09:56 ` Re: support fast default for domain with constraints jian he <[email protected]>
2025-09-01 06:27 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-01-26 07:52 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-03-10 17:18 ` Re: support fast default for domain with constraints Andrew Dunstan <[email protected]>
2026-03-11 03:43 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-03-12 03:36 ` Re: support fast default for domain with constraints jian he <[email protected]>
2026-03-12 22:07 ` Re: support fast default for domain with constraints Andrew Dunstan <[email protected]>
2026-03-13 10:13 ` Re: support fast default for domain with constraints Andrew Dunstan <[email protected]>
@ 2026-03-13 10:17 ` Viktor Holmberg <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Viktor Holmberg @ 2026-03-13 10:17 UTC (permalink / raw)
To: Andrew Dunstan <[email protected]>; +Cc: pgsql-hackers
Ok, understand. No worries at all, just thought I’d flag it up.
/Viktor
On 13 Mar 2026 at 11:13 +0100, Andrew Dunstan <[email protected]>, wrote:
>
> On 2026-03-13 Fr 4:43 AM, Viktor Holmberg wrote:
> > Nice that this was pushed. On a minor note, I saw that my email got confused in the commit ([email protected]) instead of [email protected]. (I don’t know what aiven.io is). I don’t know if there is a way to change this without messing up the git log? If not it’s no problem, probably unlikely that anyone will contact me about it anyways.
>
> It's not really possible. My deepest apologies. Some how or other the gadget I have for ensuring I credit everyone I should got confused. I will disable it until I can figure out where it went wrong.
>
> cheers
>
> andrew
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2026-03-13 10:17 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-06 13:53 Re: support fast default for domain with constraints jian he <[email protected]>
2025-03-24 11:14 ` jian he <[email protected]>
2025-05-19 07:05 ` jian he <[email protected]>
2025-06-02 09:56 ` jian he <[email protected]>
2025-09-01 06:27 ` jian he <[email protected]>
2026-01-26 07:52 ` jian he <[email protected]>
2026-03-10 17:18 ` Andrew Dunstan <[email protected]>
2026-03-11 03:43 ` jian he <[email protected]>
2026-03-12 03:36 ` jian he <[email protected]>
2026-03-12 10:15 ` Viktor Holmberg <[email protected]>
2026-03-12 22:07 ` Andrew Dunstan <[email protected]>
2026-03-13 10:13 ` Andrew Dunstan <[email protected]>
2026-03-13 10:17 ` Viktor Holmberg <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox