Hi.
CREATE DOMAIN domain7 as int CHECK(random(min=>1::int, max=>11) < value);
CREATE TABLE test_add_domain_col(a int);
INSERT INTO test_add_domain_col VALUES (1), (2);
ALTER TABLE test_add_domain_col ADD COLUMN x domain7[] default '{5}';
The above ALTER TABLE sometimes fails and sometimes not fail, but
there no table rewrite.
IMHO, We should do table rewrites for container types whose element
types include constrained domains.
Note, this issue exists before
https://git.postgresql.org/cgit/postgresql.git/commit/?id=a0b6ef29a51818a4073a5f390ed10ef6453d5c11
The attached POC fix is for HEAD only (after
a0b6ef29a51818a4073a5f390ed10ef6453d5c11)
--
jian
https://www.enterprisedb.com/
From 6e93dd2a710b13b8eff723414b4ce82622f3ed99 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 16 Mar 2026 12:04:26 +0800
Subject: [PATCH v1 1/1] table rewrite for container type include constrained
domain
CREATE DOMAIN domain7 as int CHECK(random(min=>1::int, max=>11) < value);
CREATE TABLE test_add_domain_col(a int);
ALTER TABLE test_add_domain_col ADD COLUMN x domain7[] default '{5}';
The above ALTER TABLE ADD COLUMN should do table rewrite.
discussion: https://postgr.es/m/
commitfest: https://commitfest.postgresql.org/patch/
---
src/backend/commands/tablecmds.c | 103 ++++++++++++++++++++-
src/test/regress/expected/fast_default.out | 28 ++++++
src/test/regress/sql/fast_default.sql | 23 +++++
3 files changed, 151 insertions(+), 3 deletions(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index a2e3b72f156..33c5a4f9000 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -503,6 +503,8 @@ static ObjectAddress ATExecAddColumn(List **wqueue, AlteredTableInfo *tab,
bool recurse, bool recursing,
LOCKMODE lockmode, AlterTablePass cur_pass,
AlterTableUtilityContext *context);
+static void type_has_constrained_domain(Oid typoid, bool *has_constr,
+ bool *has_volatile);
static bool check_for_column_name_collision(Relation rel, const char *colname,
bool if_not_exists);
static void add_column_datatype_dependency(Oid relid, int32 attnum, Oid typid);
@@ -7506,7 +7508,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
*/
if (RELKIND_HAS_STORAGE(relkind))
{
- bool has_domain_constraints;
+ bool has_domain_constraints = false;
bool has_missing = false;
bool has_volatile = false;
@@ -7526,8 +7528,9 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
else
defval = (Expr *) build_column_default(rel, attribute->attnum);
- has_domain_constraints =
- DomainHasConstraints(attribute->atttypid, &has_volatile);
+ type_has_constrained_domain(attribute->atttypid,
+ &has_domain_constraints,
+ &has_volatile);
/*
* If the domain has volatile constraints, we must do a table rewrite
@@ -7714,6 +7717,100 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
return address;
}
+ /*
+ * Checks whether a type contains a constrained domain. This recurses into
+ * container types (arrays, composites, ranges, multiranges) to examine their
+ * element or subtypes.
+ *
+ * The caller must initialize *has_constr to false before calling. If a
+ * constrained domain is found at any level, has_constr is set to true.
+ * Obviously, has_constr will set to true for simple constrained domain.
+ *
+ * has_volatile is set to true if the element/subtypes type is a domain and
+ * that domain contains a volatile expression.
+ */
+static void
+type_has_constrained_domain(Oid typoid, bool *has_constr, bool *has_volatile)
+{
+ char att_typtype = get_typtype(typoid);
+
+ /* since this function recurses, it could be driven to stack overflow */
+ check_stack_depth();
+
+ Assert(has_constr != NULL);
+
+ if (has_volatile)
+ {
+ if (*has_volatile)
+ return;
+ }
+ else if (*has_constr)
+ return;
+
+ if (att_typtype == TYPTYPE_DOMAIN)
+ {
+ *has_constr = DomainHasConstraints(typoid, has_volatile);
+ return;
+ }
+ else if (att_typtype == TYPTYPE_COMPOSITE)
+ {
+ /*
+ * For a composite type, recurse into its attributes. Use the
+ * typcache to avoid opening the relation directly.
+ */
+ TupleDesc tupdesc = lookup_rowtype_tupdesc(typoid, -1);
+
+ for (int i = 0; i < tupdesc->natts; i++)
+ {
+ Form_pg_attribute attr = TupleDescAttr(tupdesc, i);
+
+ if (attr->attisdropped)
+ continue;
+
+ type_has_constrained_domain(attr->atttypid,
+ has_constr,
+ has_volatile);
+
+ if (has_volatile)
+ {
+ if (*has_volatile)
+ break;
+ }
+ else if (*has_constr)
+ break;
+ }
+ ReleaseTupleDesc(tupdesc);
+ return;
+ }
+ else if (att_typtype == TYPTYPE_RANGE)
+ {
+ type_has_constrained_domain(get_range_subtype(typoid),
+ has_constr,
+ has_volatile);
+ return;
+ }
+ else if (att_typtype == TYPTYPE_MULTIRANGE)
+ {
+ type_has_constrained_domain(get_multirange_range(typoid),
+ has_constr,
+ has_volatile);
+ return;
+ }
+ else
+ {
+ Oid att_typelem = get_element_type(typoid);
+
+ if (OidIsValid(att_typelem))
+ {
+ /* recurse into array element type */
+ type_has_constrained_domain(att_typelem,
+ has_constr,
+ has_volatile);
+ return;
+ }
+ }
+}
+
/*
* If a new or renamed column will collide with the name of an existing
* column and if_not_exists is false then error out, else do nothing.
diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out
index ffbc47089b1..16339d6d209 100644
--- a/src/test/regress/expected/fast_default.out
+++ b/src/test/regress/expected/fast_default.out
@@ -372,8 +372,36 @@ NOTICE: rewriting table test_add_domain_col for reason 2
ALTER TABLE test_add_domain_col ADD COLUMN h domain5
GENERATED ALWAYS AS (a + 20) VIRTUAL; -- error
ERROR: virtual generated column "h" cannot have a domain type
+-- Test container type contains volatile domain for table rewrite
+CREATE TYPE domain6range AS range(subtype=domain6);
+CREATE TYPE comp_with_domain6 AS (a domain6);
+CREATE TYPE domain7range AS range(subtype=domain7);
+CREATE TYPE comp_with_domain7 AS (a domain7);
+-- all of the following should have table rewrite
+ALTER TABLE test_add_domain_col ADD COLUMN x domain7[] DEFAULT '{20}';
+NOTICE: rewriting table test_add_domain_col for reason 2
+ALTER TABLE test_add_domain_col ADD COLUMN x1 domain7[];
+NOTICE: rewriting table test_add_domain_col for reason 2
+ALTER TABLE test_add_domain_col ADD COLUMN x2 domain7range DEFAULT '[12, 22)';
+NOTICE: rewriting table test_add_domain_col for reason 2
+ALTER TABLE test_add_domain_col ADD COLUMN x3 domain7range[] DEFAULT '{"[12,22)"}';
+NOTICE: rewriting table test_add_domain_col for reason 2
+ALTER TABLE test_add_domain_col ADD COLUMN x4 domain7multirange DEFAULT '{[12, 22]}';
+NOTICE: rewriting table test_add_domain_col for reason 2
+ALTER TABLE test_add_domain_col ADD COLUMN x5 domain7multirange[] DEFAULT '{"{[12,22]}"}';
+NOTICE: rewriting table test_add_domain_col for reason 2
+ALTER TABLE test_add_domain_col ADD COLUMN x6 comp_with_domain7 DEFAULT '(12)';
+NOTICE: rewriting table test_add_domain_col for reason 2
+ALTER TABLE test_add_domain_col ADD COLUMN x7 comp_with_domain7[] DEFAULT '{(12)}';
+NOTICE: rewriting table test_add_domain_col for reason 2
+-- all of the above should have table rewrite
+-- No need table rewrite, as domain volatile default expressions are irrelevant for container types
+ALTER TABLE test_add_domain_col ADD COLUMN x10 comp_with_domain6 DEFAULT '(12)';
+ALTER TABLE test_add_domain_col ADD COLUMN x11 comp_with_domain6[] DEFAULT '{(12)}';
+ALTER TABLE test_add_domain_col ADD COLUMN x12 domain8[] DEFAULT '{12}';
DROP TABLE t2;
DROP TABLE test_add_domain_col;
+DROP TYPE domain7range, comp_with_domain7, comp_with_domain6, domain6range;
DROP DOMAIN domain1;
DROP DOMAIN domain2;
DROP DOMAIN domain3;
diff --git a/src/test/regress/sql/fast_default.sql b/src/test/regress/sql/fast_default.sql
index 8ff29cf2697..0bf11ed786c 100644
--- a/src/test/regress/sql/fast_default.sql
+++ b/src/test/regress/sql/fast_default.sql
@@ -332,8 +332,31 @@ ALTER TABLE test_add_domain_col ADD COLUMN g domain9 DEFAULT 14;
ALTER TABLE test_add_domain_col ADD COLUMN h domain5
GENERATED ALWAYS AS (a + 20) VIRTUAL; -- error
+-- Test container type contains volatile domain for table rewrite
+CREATE TYPE domain6range AS range(subtype=domain6);
+CREATE TYPE comp_with_domain6 AS (a domain6);
+CREATE TYPE domain7range AS range(subtype=domain7);
+CREATE TYPE comp_with_domain7 AS (a domain7);
+
+-- all of the following should have table rewrite
+ALTER TABLE test_add_domain_col ADD COLUMN x domain7[] DEFAULT '{20}';
+ALTER TABLE test_add_domain_col ADD COLUMN x1 domain7[];
+ALTER TABLE test_add_domain_col ADD COLUMN x2 domain7range DEFAULT '[12, 22)';
+ALTER TABLE test_add_domain_col ADD COLUMN x3 domain7range[] DEFAULT '{"[12,22)"}';
+ALTER TABLE test_add_domain_col ADD COLUMN x4 domain7multirange DEFAULT '{[12, 22]}';
+ALTER TABLE test_add_domain_col ADD COLUMN x5 domain7multirange[] DEFAULT '{"{[12,22]}"}';
+ALTER TABLE test_add_domain_col ADD COLUMN x6 comp_with_domain7 DEFAULT '(12)';
+ALTER TABLE test_add_domain_col ADD COLUMN x7 comp_with_domain7[] DEFAULT '{(12)}';
+-- all of the above should have table rewrite
+
+-- No need table rewrite, as domain volatile default expressions are irrelevant for container types
+ALTER TABLE test_add_domain_col ADD COLUMN x10 comp_with_domain6 DEFAULT '(12)';
+ALTER TABLE test_add_domain_col ADD COLUMN x11 comp_with_domain6[] DEFAULT '{(12)}';
+ALTER TABLE test_add_domain_col ADD COLUMN x12 domain8[] DEFAULT '{12}';
+
DROP TABLE t2;
DROP TABLE test_add_domain_col;
+DROP TYPE domain7range, comp_with_domain7, comp_with_domain6, domain6range;
DROP DOMAIN domain1;
DROP DOMAIN domain2;
DROP DOMAIN domain3;
--
2.34.1
