From f1791a42aebdfd5be76682c200e71aeafe75e7c0 Mon Sep 17 00:00:00 2001 From: jian he 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_AHigppPwLTJi6km8NOsC7FWvKdEmmXg@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