On Wed, Mar 5, 2025 at 11:13 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > > This patch appears to summarily throw away a couple of > backwards-compatibility concerns that the previous round > took care to preserve: > > * not throwing an error if the default would fail the domain > constraints, but the table is empty so there is no need to > instantiate the default. > hi. Thanks for pointing this out. I noticed an empty table scarenio, but didn't check it thoroughly. The attached patch preserves this backwards-compatibility. now it's aligned with master behavior, i think.
main gotcha is: ALTER TABLE ADD COLUMN... If no explicitly DEFAULT, the defval either comes from pg_type.typdefaultbin, or constructed via makeNullConst branch. In that case, we need to use soft error evaluation, because we allow these cases for an empty table; In other cases, we can directly evaluate explicitly the DEFAULT clause. > * not assuming that the domain constraints are immutable. > > Now it's fair to question how important the second point is > considering that we mostly treat domain constraints as immutable > elsewhere. But I think the first point has actual practical uses > --- for example, if you want to set things up so that inserts must > specify that column explicitly. So I don't think it's okay to > discard that behavior. > in v2-0003. I created a new function: bool DomainHaveVolatileConstraints(Oid type_id, bool *have_volatile) within DomainHaveVolatileConstraints i use contain_volatile_functions to test whether check_expr is volatile or not. contain_volatile_functions won't be expensive, i think. if true then have_volatile is set to true. if have_volatile is true then we need table rewrite.
From 40364a9f3926a9ebc8cad4534ab2221e2a1d2574 Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> Date: Wed, 5 Mar 2025 20:38:27 +0800 Subject: [PATCH v2 2/3] fast default for domain with constraints This is primarily done by evaluating CoerceToDomain with soft error support. If we evaluate CoerceToDomain to false, it means in ATExecAddColumn, the defval node evaluation value cannot be cast to the domain. However, we cannot fail at the Phase 2 stage in cases where the table is empty. 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. NOTE: this patch does not consider domain with volatile check constraints. discussion: https://postgr.es/m/cacjufxe_+izbr1i49k_ahigpppwltji6km8nosc7fwvkdem...@mail.gmail.com [1]: https://git.postgresql.org/cgit/postgresql.git/commit/?id=aaaf9449ec6be62cb0d30ed3588dc384f56274bf --- src/backend/commands/tablecmds.c | 43 ++++++++-- src/test/regress/expected/fast_default.out | 91 ++++++++++++++++++++++ src/test/regress/sql/fast_default.sql | 57 ++++++++++++++ 3 files changed, 186 insertions(+), 5 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 8826ca5c32c..f7c8348c7ff 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -7331,6 +7331,7 @@ 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. * + * XXXX this para should be removed? * 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 @@ -7358,6 +7359,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, { bool has_domain_constraints; bool has_missing = false; + bool no_explicit_defval = false; /* * For an identity column, we can't use build_column_default(), @@ -7375,6 +7377,21 @@ 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 value, no explicit DEFAULT expression specified. + * In that case we need evaluate defval error safe way, so + * domain specification such as ``check(value > 10) default 8 `` + * can be added 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) + no_explicit_defval = true; + } + /* Build CoerceToDomain(NULL) expression if needed */ has_domain_constraints = DomainHasConstraints(attribute->atttypid); if (!defval && has_domain_constraints) @@ -7397,6 +7414,12 @@ 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 + * equivalent to not-null, we only want it failure when table have + * some rows. so do it in soft error way. + */ + no_explicit_defval = true; } if (defval) @@ -7419,13 +7442,13 @@ 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. + * 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 && !contain_volatile_functions((Node *) defval)) { EState *estate; @@ -7435,10 +7458,20 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, /* Evaluate the default expression */ estate = CreateExecutorState(); - exprState = ExecPrepareExpr(defval, estate); + if (!no_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..1ab610a1410 100644 --- a/src/test/regress/expected/fast_default.out +++ b/src/test/regress/expected/fast_default.out @@ -317,11 +317,102 @@ 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 domain domain9 as int check(value is not null) default 9; +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 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 +ALTER TABLE t3 ADD COLUMN d domain5; --ok. because t3 is empty +NOTICE: rewriting table t3 for reason 2 +INSERT INTO t3(a,b,c,d) values(1,2, 3, default); --should fail +ERROR: value for domain domain5 violates check constraint "domain5_check" +INSERT INTO t3(a,b,c,d) values(1,default, 3, 12); --should fail +ERROR: domain domain6 does not allow null values +INSERT INTO t3(a,b,c,d) values(1,2, default, 12); --should fail +ERROR: value for domain domain7 violates check constraint "domain7_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 we consider explicit column default expression, not domain default +ALTER TABLE t3 ADD COLUMN e domain8 default 15; +ALTER TABLE t3 ADD COLUMN f domain9; --no table rewrite +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} + 6 | f | t | f | {9} +(6 rows) + +--table rewrite. we are applying domain volatile default expresion +ALTER TABLE t3 ADD COLUMN g 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 attisdropped is false +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 | + 7 | g | f | f | +(7 rows) + +SELECT a,b,c,d,e,f, g > 10 as f_ok FROM t3 ORDER BY a; + a | b | c | d | e | f | f_ok +---+----+----+----+----+---+------ + 1 | 12 | 13 | 14 | 15 | 9 | t + 2 | 12 | 13 | 14 | 15 | 9 | t +(2 rows) + 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 DOMAIN domain9; 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..e3139ce8b15 100644 --- a/src/test/regress/sql/fast_default.sql +++ b/src/test/regress/sql/fast_default.sql @@ -287,11 +287,68 @@ 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 domain domain9 as int check(value is not null) default 9; + +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 b domain6; --ok. because t3 is empty +ALTER TABLE t3 ADD COLUMN c domain7; --ok. because t3 is empty +ALTER TABLE t3 ADD COLUMN d domain5; --ok. because t3 is empty +INSERT INTO t3(a,b,c,d) values(1,2, 3, default); --should fail +INSERT INTO t3(a,b,c,d) values(1,default, 3, 12); --should fail +INSERT INTO t3(a,b,c,d) values(1,2, default, 12); --should fail +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 we consider explicit column default expression, not domain default +ALTER TABLE t3 ADD COLUMN e domain8 default 15; +ALTER TABLE t3 ADD COLUMN f domain9; --no table rewrite + +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 g domain8; + +SELECT attnum, attname, atthasmissing, atthasdef, attmissingval +FROM pg_attribute +WHERE attnum > 0 AND attrelid = 't3'::regclass AND attisdropped is false +ORDER BY attnum; + +SELECT a,b,c,d,e,f, g > 10 as f_ok FROM t3 ORDER BY a; + 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 DOMAIN domain9; DROP FUNCTION foo(INT); -- Fall back to full rewrite for volatile expressions -- 2.34.1
From ae57f10e34862295b00fdd1a0560995a1597c692 Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> Date: Wed, 5 Mar 2025 20:37:31 +0800 Subject: [PATCH v2 1/3] soft error variant of ExecPrepareExpr, ExecInitExpr ExecPrepareExprSafe and ExecInitExprSafe. ExecPrepareExprSafe initialize for expression execution with soft error support. not all expression node support it. some like CoerceToDomain do support it. XXX more comments. discussion: https://postgr.es/m/cacjufxe_+izbr1i49k_ahigpppwltji6km8nosc7fwvkdem...@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 03566c4d181..04f8b839d30 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -170,6 +170,46 @@ ExecInitExpr(Expr *node, PlanState *parent) return state; } +/* + * ExecInitExpr: 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; + ExprEvalPushStep(state, &scratch); + + ExecReadyExpr(state); + + return state; +} + /* * ExecInitExprWithParams: prepare a standalone expression tree for execution * @@ -778,6 +818,29 @@ ExecPrepareExpr(Expr *node, EState *estate) return result; } +/* + * ExecPrepareExprSafe: soft error variant of ExecPrepareExpr. + * + * use it when expression node *support* soft error expression execution. + * ExecPrepareExpr comments apply to here too. + */ +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 d12e3f451d2..b7ab95437fe 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
From f1791a42aebdfd5be76682c200e71aeafe75e7c0 Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> Date: Wed, 5 Mar 2025 20:39:42 +0800 Subject: [PATCH v2 3/3] no fast default for domain with voltile constraints this patch force table rewrite when ALTER TABLE ADD COLUMN, the to be added column is a domain with volatile check constraints. discussion: https://postgr.es/m/cacjufxe_+izbr1i49k_ahigpppwltji6km8nosc7fwvkdem...@mail.gmail.com --- src/backend/commands/tablecmds.c | 11 +++++- src/backend/utils/cache/typcache.c | 37 +++++++++++++++++++ src/include/utils/typcache.h | 1 + src/test/regress/expected/fast_default.out | 42 ++++++++++++++++++++++ src/test/regress/sql/fast_default.sql | 31 ++++++++++++++++ 5 files changed, 121 insertions(+), 1 deletion(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index f7c8348c7ff..8edd7da5d74 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -7360,6 +7360,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, bool has_domain_constraints; bool has_missing = false; bool no_explicit_defval = false; + bool has_volatile = false; /* * For an identity column, we can't use build_column_default(), @@ -7393,7 +7394,14 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, } /* Build CoerceToDomain(NULL) expression if needed */ - has_domain_constraints = DomainHasConstraints(attribute->atttypid); + has_domain_constraints = DomainHaveVolatileConstraints(attribute->atttypid, &has_volatile); + + if (has_volatile) + { + Assert(has_domain_constraints); + tab->rewrite |= AT_REWRITE_DEFAULT_VAL; + } + if (!defval && has_domain_constraints) { Oid baseTypeId; @@ -7449,6 +7457,7 @@ 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; 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); diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out index 1ab610a1410..8d16beab74c 100644 --- a/src/test/regress/expected/fast_default.out +++ b/src/test/regress/expected/fast_default.out @@ -402,8 +402,48 @@ SELECT a,b,c,d,e,f, g > 10 as f_ok FROM t3 ORDER BY a; 2 | 12 | 13 | 14 | 15 | 9 | t (2 rows) +------test table rewrite for volatile domain constraints. +create domain domain10 as int check((value + random(min=>11::int, max=>11)) > 12); --volatile +create domain domain11 as int check((value + random(min=>11::int, max=>11)) > 12) default 1; --volatile +--test with empty table +CREATE TABLE t4(a int); +ALTER TABLE t4 ADD COLUMN b domain10 default -1; +NOTICE: rewriting table t4 for reason 2 +ALTER TABLE t4 ADD COLUMN c domain11 default -1; +NOTICE: rewriting table t4 for reason 2 +ALTER TABLE t4 ADD COLUMN d domain11; +NOTICE: rewriting table t4 for reason 2 +INSERT INTO t4 default values; +ERROR: value for domain domain10 violates check constraint "domain10_check" +DROP TABLE T4; +CREATE TABLE t4(a int); +INSERT INTO t4 VALUES(1),(2); +--all these will table rewrite then error out. +ALTER TABLE t4 ADD COLUMN b domain10 default -1; +NOTICE: rewriting table t4 for reason 2 +ERROR: value for domain domain10 violates check constraint "domain10_check" +ALTER TABLE t4 ADD COLUMN b domain11 default -1; +NOTICE: rewriting table t4 for reason 2 +ERROR: value for domain domain11 violates check constraint "domain11_check" +ALTER TABLE t4 ADD COLUMN b domain11; +NOTICE: rewriting table t4 for reason 2 +ERROR: value for domain domain11 violates check constraint "domain11_check" +--all these will table rewrite and be ok. +ALTER TABLE t4 ADD COLUMN b domain10; --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; @@ -413,6 +453,8 @@ DROP DOMAIN domain6; DROP DOMAIN domain7; DROP DOMAIN domain8; DROP DOMAIN domain9; +DROP DOMAIN domain10; +DROP DOMAIN domain11; 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 e3139ce8b15..e080a38daa8 100644 --- a/src/test/regress/sql/fast_default.sql +++ b/src/test/regress/sql/fast_default.sql @@ -338,8 +338,37 @@ ORDER BY attnum; SELECT a,b,c,d,e,f, g > 10 as f_ok FROM t3 ORDER BY a; +------test table rewrite for volatile domain constraints. +create domain domain10 as int check((value + random(min=>11::int, max=>11)) > 12); --volatile +create domain domain11 as int check((value + random(min=>11::int, max=>11)) > 12) default 1; --volatile + +--test with empty table +CREATE TABLE t4(a int); +ALTER TABLE t4 ADD COLUMN b domain10 default -1; +ALTER TABLE t4 ADD COLUMN c domain11 default -1; +ALTER TABLE t4 ADD COLUMN d domain11; +INSERT INTO t4 default values; +DROP TABLE T4; + + +CREATE TABLE t4(a int); +INSERT INTO t4 VALUES(1),(2); +--all these will table rewrite then error out. +ALTER TABLE t4 ADD COLUMN b domain10 default -1; +ALTER TABLE t4 ADD COLUMN b domain11 default -1; +ALTER TABLE t4 ADD COLUMN b domain11; + +--all these will table rewrite and be ok. +ALTER TABLE t4 ADD COLUMN b domain10; --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; @@ -349,6 +378,8 @@ DROP DOMAIN domain6; DROP DOMAIN domain7; DROP DOMAIN domain8; DROP DOMAIN domain9; +DROP DOMAIN domain10; +DROP DOMAIN domain11; DROP FUNCTION foo(INT); -- Fall back to full rewrite for volatile expressions -- 2.34.1
