On Sun, Mar 15, 2026 at 12:32 AM Viktor Holmberg <[email protected]> wrote:
>
> As I’m sure you know Jian this needs a rebase now that a0b6ef29a518 has been
> merged. It’s a bit hard for me to review in this state when I can’t apply the
> patches cleanly.
>
> + /*
> + * 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;
>
> I don’t understand this piece of code, and why value is re-assigned right
> away. Not saying it’s wrong but if you could explain why it is like that to
> someone not well versed in C. Would something like (void)
> ExecEvalExpr(ex->exprstate, econtext, &isnull); do?
>
CREATE TABLE t(a int);
INSERT INTO t VALUES (1);
CREATE DOMAIN d1 AS INT check (value <> 1);
ALTER TABLE t ALTER COLUMN a SET DATA TYPE d1;
ATPrepAlterColumnType->coerce_to_target_type will return a COERCETODOMAIN Node
{COERCETODOMAIN
:arg
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varcollid 0
:varnullingrels (b)
:varlevelsup 0
:varreturningtype 0
:varnosyn 1
:varattnosyn 1
:location -1
}
:resulttype 18235
:resulttypmod -1
:resultcollid 0
:coercionformat 2
:location -1
}
A table rewrite means copying the existing, unaffected columns as-is. For the
column changing its data type, we first compute the COERCETODOMAIN node and
write the new value to the new table.
See ATRewriteTable->table_scan_getnextslot, The ExecEvalExpr below is
used to compute the new value of the changing column,
namely evaluating the COERCETODOMAIN node.
````
foreach(l, tab->newvals)
{
NewColumnValue *ex = lfirst(l);
if (ex->is_generated)
continue;
newslot->tts_values[ex->attnum - 1]
= ExecEvalExpr(ex->exprstate,
econtext,
&newslot->tts_isnull[ex->attnum - 1]);
}
````
For ALTER TABLE t ALTER COLUMN a SET DATA TYPE d1;
If we want to skip the table rewrite and do a table scan only,
We still need to use ExecEvalExpr() to evaluate the expression.
ExecEvalExpr() for COERCETODOMAIN may fail
(e.g `SELECT 1::d1`, where the value 1 cannot be cast to domain d1).
The last step of ExecInitExpr() is EEOP_DONE_RETURN, which means that when
ExecEvalExpr() evaluates an expression, if it does not
fail (No ``ereport(ERROR)`` happened), it will return a value.
see [1].
[1]
https://git.postgresql.org/cgit/postgresql.git/commit/?id=8dd7c7cd0a2605d5301266a6b67a569d6a305106
> There are other things I don’t quite understand so will give it another pass
> once it’s been rebased.
>
This attached is more bullet-proof.
It can now cope with a domain over an array of another domain, where a
table scan
should be possible (as shown below).
+CREATE DOMAIN domain1 AS INT CHECK(VALUE > 1) NOT NULL;
+CREATE DOMAIN domain2 AS domain1 CHECK(VALUE > 1) NOT NULL;
+CREATE DOMAIN domain6 AS domain2[];
+ALTER TABLE t22 ALTER COLUMN col1 SET DATA TYPE domain6 USING col1;
--
jian
https://www.enterprisedb.com/
From 08c107636dd15fa7bb96226b1b4f1dd67dc683bb Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Tue, 17 Mar 2026 16:04:30 +0800
Subject: [PATCH v4 1/1] Skipping table rewrites for changing column types in
some cases
If a table rewrite is required, there's nothing we can do about it. We can add
a new boolean field need_compute to NewColumnValue. This field is currently set
to true only when changing an existing column's type to a constrained domain.
In such cases, a table scan alone is sufficient.
This only works if the new domain type all constraint are non-volatile. and new
domain base type is binary coercible with the old column type.
[1]: https://www.postgresql.org/message-id/[email protected]
discussion: https://postgr.es/m/cacjufxfx0dupbf5+dbnf3mxcfntz1y7jpt11+tcd_fcyadh...@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/5907
---
doc/src/sgml/ref/alter_table.sgml | 5 +-
src/backend/commands/tablecmds.c | 188 ++++++++++++++++++++-
src/test/regress/expected/fast_default.out | 67 ++++++++
src/test/regress/sql/fast_default.sql | 39 +++++
4 files changed, 293 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 8591a6b5014..5b355526522 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -1667,8 +1667,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
As an exception, when changing the type of an existing column,
if the <literal>USING</literal> clause does not change the column
contents and the old type is either binary coercible to the new type
- or an unconstrained domain over the new type, a table rewrite is not
- needed. However, indexes will still be rebuilt unless the system
+ or an unconstrained domain over the new type, or domain over new type has no
+ volatile constraint, a table rewrite is not needed.
+ However, indexes will still be rebuilt unless the system
can verify that the new index would be logically equivalent to the
existing one. For example, if the collation for a column has been
changed, an index rebuild is required because the new sort
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 67e42e5df29..958639f4fc7 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -232,6 +232,12 @@ 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 need_compute is true, we will evaluate the new column value in Phase 3.
+ * Currently, this is only used in the ALTER COLUMN SET DATA TYPE command, where
+ * the column’s data type is being changed to a constrained domain or a
+ * container type containing a constrained domain, and all the domain's
+ * constraints are non-volatile. In case of a table rewrite, need_compute is set to true too.
*/
typedef struct NewColumnValue
{
@@ -239,6 +245,7 @@ typedef struct NewColumnValue
Expr *expr; /* expression to compute */
ExprState *exprstate; /* execution state */
bool is_generated; /* is it a GENERATED expression? */
+ bool need_compute;
} NewColumnValue;
/*
@@ -659,6 +666,10 @@ static void ATPrepAlterColumnType(List **wqueue,
AlterTableCmd *cmd, LOCKMODE lockmode,
AlterTableUtilityContext *context);
static bool ATColumnChangeRequiresRewrite(Node *expr, AttrNumber varattno);
+static bool ATColumnChangeScanOnly(Oid inputType, Oid targetType, Node *expr);
+static bool ValidTableScanOnlyCoercionExpr(Node *expr);
+static void TypeCastTableScanOnly(Oid inputType, Oid targetType,
+ bool *scan_only);
static ObjectAddress ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
AlterTableCmd *cmd, LOCKMODE lockmode);
static void RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
@@ -6077,7 +6088,7 @@ 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);
/*
@@ -6272,6 +6283,9 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
/* expr already planned */
ex->exprstate = ExecInitExpr(ex->expr, NULL);
+
+ if (ex->need_compute)
+ needscan = true;
}
notnull_attrs = notnull_virtual_attrs = NIL;
@@ -6500,6 +6514,48 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap)
* new constraints etc.
*/
insertslot = oldslot;
+
+ /*
+ * To safely evaluate the NewColumnValue expression against
+ * the original table slot, temporarily switch oldslot's
+ * tts_tupleDescriptor to oldTupDesc, and then switch it back
+ * to newTupDesc afterwards.
+ *
+ * Rationale: 1. If there is no table rewrite, the contents of
+ * oldslot are guaranteed to be fully compatible with
+ * oldTupDesc. See the comments in slot_deform_heap_tuple
+ * regarding slot_getmissingattrs.
+ *
+ * 2. Passing the updated newTupDesc into ExecEvalExpr would
+ * cause CheckVarSlotCompatibility to fail, we have to
+ * temporarily use oldTupDesc
+ */
+ if (tab->newvals != NIL)
+ {
+ bool isnull pg_attribute_unused();
+
+ insertslot->tts_tupleDescriptor = oldTupDesc;
+ econtext->ecxt_scantuple = insertslot;
+
+ foreach(l, tab->newvals)
+ {
+ NewColumnValue *ex = lfirst(l);
+
+ if (!ex->need_compute)
+ continue;
+
+ /*
+ * ExecEvalExprNoReturn cannot be used here because
+ * the expression was compiled via ExecInitExpr. We
+ * only need to check if oldslot satisfies new
+ * expression (NewColumnValue->expr), so it's fine to
+ * igrnore value returned by ExecEvalExpr.
+ */
+ (void) ExecEvalExpr(ex->exprstate, econtext, &isnull);
+ }
+
+ insertslot->tts_tupleDescriptor = newTupDesc;
+ }
}
/* Now check any constraints on the possibly-changed tuple */
@@ -14776,10 +14832,20 @@ ATPrepAlterColumnType(List **wqueue,
newval->attnum = attnum;
newval->expr = (Expr *) transform;
newval->is_generated = false;
+ newval->need_compute = false;
- tab->newvals = lappend(tab->newvals, newval);
- if (ATColumnChangeRequiresRewrite(transform, attnum))
+ if (!tab->rewrite && ATColumnChangeScanOnly(attTup->atttypid,
+ targettype,
+ transform))
+ {
+ newval->need_compute = true;
+ }
+ else if (ATColumnChangeRequiresRewrite(transform, attnum))
+ {
tab->rewrite |= AT_REWRITE_COLUMN_REWRITE;
+ newval->need_compute = true;
+ }
+ tab->newvals = lappend(tab->newvals, newval);
}
else if (transform)
ereport(ERROR,
@@ -14910,7 +14976,6 @@ ATPrepAlterColumnType(List **wqueue,
* rewrite in these cases:
*
* - the old type is binary coercible to the new type
- * - the new type is an unconstrained domain over the old type
* - {NEW,OLD} or {OLD,NEW} is {timestamptz,timestamp} and the timezone is UTC
*
* In the case of a constrained domain, we could get by with scanning the
@@ -14959,6 +15024,121 @@ ATColumnChangeRequiresRewrite(Node *expr, AttrNumber varattno)
}
}
+/*
+ * Used for ALTER COLUMN SET DATA TYPE
+ *
+ * In some cases, We can just using table scan to validate the data without a
+ * table rewrite. Returns true if checking the old contents via a table scan is
+ * enough to ensure exists content is compatibility with the new data type.
+ */
+static bool
+ATColumnChangeScanOnly(Oid inputType, Oid targetType, Node *expr)
+{
+ bool scan_only = true;
+
+ if (!ValidTableScanOnlyCoercionExpr(expr))
+ return false;
+
+ TypeCastTableScanOnly(inputType,
+ targetType,
+ &scan_only);
+
+ return scan_only;
+}
+
+/*
+ * Used for ALTER COLUMN SET DATA TYPE
+ *
+ * Return false means table rewrite is required for changing the column type.
+ */
+static bool
+ValidTableScanOnlyCoercionExpr(Node *expr)
+{
+ for (;;)
+ {
+ /* only one varno, so no need to check that */
+ if (IsA(expr, Var))
+ return true;
+ else if (IsA(expr, Const))
+ return true;
+ else if (IsA(expr, CaseTestExpr))
+ return true;
+ else if (IsA(expr, RelabelType))
+ expr = (Node *) ((RelabelType *) expr)->arg;
+ else if (IsA(expr, CoerceToDomain))
+ {
+ CoerceToDomain *d = (CoerceToDomain *) expr;
+
+ expr = (Node *) d->arg;
+ }
+ else if (IsA(expr, CoerceViaIO))
+ {
+ CoerceViaIO *c = (CoerceViaIO *) expr;
+
+ expr = (Node *) c->arg;
+ }
+ else if (IsA(expr, ArrayCoerceExpr))
+ {
+ ArrayCoerceExpr *a = (ArrayCoerceExpr *) expr;
+
+ expr = (Node *) a->elemexpr;
+ }
+ else
+ return false;
+ }
+}
+
+static void
+TypeCastTableScanOnly(Oid inputType, Oid targetType,
+ bool *scan_only)
+{
+ char target_typtype = get_typtype(targetType);
+
+ /* since this function recurses, it could be driven to stack overflow */
+ check_stack_depth();
+
+ if (!(*scan_only))
+ return;
+
+ /*
+ * IsBinaryCoercible can cope with source type as domain, hence no need to
+ * check source type is domain or not
+ */
+ if (target_typtype == TYPTYPE_DOMAIN)
+ {
+ bool has_volatile = false;
+
+ (void) DomainHasConstraints(targetType, &has_volatile);
+
+ if (has_volatile)
+ {
+ *scan_only = false;
+ return;
+ }
+
+ TypeCastTableScanOnly(inputType,
+ getBaseType(targetType),
+ scan_only);
+ return;
+ }
+ else
+ {
+ Oid input_typelem = get_element_type(inputType);
+ Oid target_typelem = get_element_type(targetType);
+
+ if (OidIsValid(input_typelem) && OidIsValid(target_typelem))
+ {
+ /* recurse into array element type */
+ TypeCastTableScanOnly(input_typelem,
+ target_typelem,
+ scan_only);
+ return;
+ }
+ }
+
+ *scan_only = IsBinaryCoercible(inputType, targetType);
+}
+
/*
* ALTER COLUMN .. SET DATA TYPE
*
diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out
index ffbc47089b1..eefb7a2a0fc 100644
--- a/src/test/regress/expected/fast_default.out
+++ b/src/test/regress/expected/fast_default.out
@@ -384,6 +384,65 @@ DROP DOMAIN domain7;
DROP DOMAIN domain8;
DROP DOMAIN domain9;
DROP FUNCTION foo(INT);
+-- Test set column data type to domain
+CREATE DOMAIN domain1 AS INT CHECK(VALUE > 1) NOT NULL;
+CREATE DOMAIN domain2 AS domain1 CHECK(VALUE > 1) NOT NULL;
+CREATE DOMAIN domain3 AS INT8;
+CREATE DOMAIN domain4 AS domain1 CHECK(VALUE > random(min=>10, max=>10)) CHECK(VALUE > 1) NOT NULL;
+CREATE DOMAIN domain5 AS TEXT COLLATE "POSIX" CHECK (value <> 'hello');
+CREATE DOMAIN domain6 AS domain2[];
+CREATE TABLE t22(a INT, b INT, c text COLLATE "C", col1 INT[]);
+INSERT INTO t22 VALUES(-2, -1, 'a', '{-2,null}');
+-- no table rewrite, but fail at domain constraint check
+ALTER TABLE t22 ALTER COLUMN col1 SET DATA TYPE domain2[] USING col1::domain2[];
+ERROR: value for domain domain2 violates check constraint "domain1_check"
+ALTER TABLE t22 ALTER COLUMN col1 SET DATA TYPE domain2[] USING col1;
+ERROR: value for domain domain2 violates check constraint "domain1_check"
+ALTER TABLE t22 ALTER COLUMN col1 SET DATA TYPE domain6 USING col1;
+ERROR: value for domain domain2 violates check constraint "domain1_check"
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain1;
+ERROR: value for domain domain1 violates check constraint "domain1_check"
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain2;
+ERROR: value for domain domain2 violates check constraint "domain1_check"
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain2 USING a;
+ERROR: value for domain domain2 violates check constraint "domain1_check"
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain2 USING a::domain2;
+ERROR: value for domain domain2 violates check constraint "domain1_check"
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain2 USING b;
+ERROR: value for domain domain2 violates check constraint "domain1_check"
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain2 USING b::domain2;
+ERROR: value for domain domain2 violates check constraint "domain1_check"
+ALTER TABLE t22 ALTER COLUMN c SET DATA TYPE domain5 USING 'hello' COLLATE "C";
+ERROR: value for domain domain5 violates check constraint "domain5_check"
+ALTER TABLE t22 ALTER COLUMN c SET DATA TYPE domain5; -- no table rewrite
+CREATE INDEX t22_idx_c ON t22(c);
+SET client_min_messages to debug1;
+ALTER TABLE t22 ALTER COLUMN c SET DATA TYPE varchar COLLATE "POSIX"; -- no table rewrite, no index rebuild
+DEBUG: verifying table "t22"
+-- no table rewrite, but index will rebuild because index collation changes
+ALTER TABLE t22 ALTER COLUMN c SET DATA TYPE varchar COLLATE "C";
+DEBUG: building index "t22_idx_c" on table "t22" serially
+DEBUG: index "t22_idx_c" can safely use deduplication
+DEBUG: verifying table "t22"
+RESET client_min_messages;
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain3; -- table rewrite
+NOTICE: rewriting table t22 for reason 4
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain4; -- table rewrite, then fail
+NOTICE: rewriting table t22 for reason 4
+ERROR: value for domain domain4 violates check constraint "domain1_check"
+ALTER TABLE t22 ALTER COLUMN b SET DATA TYPE domain3; -- table rewrite
+NOTICE: rewriting table t22 for reason 4
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain1 USING ((a)); -- table rewrite
+NOTICE: rewriting table t22 for reason 4
+ERROR: value for domain domain1 violates check constraint "domain1_check"
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain1 USING (a+0)::domain2; -- table rewrite, then fail
+NOTICE: rewriting table t22 for reason 4
+ERROR: value for domain domain2 violates check constraint "domain1_check"
+DROP TABLE t22;
+DROP DOMAIN domain1, domain2, domain3, domain4, domain5;
+ERROR: cannot drop desired object(s) because other objects depend on them
+DETAIL: type domain6 depends on type domain2[]
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- Fall back to full rewrite for volatile expressions
CREATE TABLE T(pk INT NOT NULL PRIMARY KEY);
INSERT INTO T VALUES (1);
@@ -986,6 +1045,14 @@ DROP TABLE has_volatile;
DROP EVENT TRIGGER has_volatile_rewrite;
DROP FUNCTION log_rewrite;
DROP SCHEMA fast_default;
+ERROR: cannot drop schema fast_default because other objects depend on it
+DETAIL: type domain1 depends on schema fast_default
+type domain2 depends on schema fast_default
+type domain3 depends on schema fast_default
+type domain4 depends on schema fast_default
+type domain5 depends on schema fast_default
+type domain6 depends on schema fast_default
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
-- Leave a table with an active fast default in place, for pg_upgrade testing
set search_path = public;
create table has_fast_default(f1 int);
diff --git a/src/test/regress/sql/fast_default.sql b/src/test/regress/sql/fast_default.sql
index 8ff29cf2697..13af7728c9b 100644
--- a/src/test/regress/sql/fast_default.sql
+++ b/src/test/regress/sql/fast_default.sql
@@ -345,6 +345,45 @@ DROP DOMAIN domain8;
DROP DOMAIN domain9;
DROP FUNCTION foo(INT);
+-- Test set column data type to domain
+CREATE DOMAIN domain1 AS INT CHECK(VALUE > 1) NOT NULL;
+CREATE DOMAIN domain2 AS domain1 CHECK(VALUE > 1) NOT NULL;
+CREATE DOMAIN domain3 AS INT8;
+CREATE DOMAIN domain4 AS domain1 CHECK(VALUE > random(min=>10, max=>10)) CHECK(VALUE > 1) NOT NULL;
+CREATE DOMAIN domain5 AS TEXT COLLATE "POSIX" CHECK (value <> 'hello');
+CREATE DOMAIN domain6 AS domain2[];
+CREATE TABLE t22(a INT, b INT, c text COLLATE "C", col1 INT[]);
+INSERT INTO t22 VALUES(-2, -1, 'a', '{-2,null}');
+
+-- no table rewrite, but fail at domain constraint check
+ALTER TABLE t22 ALTER COLUMN col1 SET DATA TYPE domain2[] USING col1::domain2[];
+ALTER TABLE t22 ALTER COLUMN col1 SET DATA TYPE domain2[] USING col1;
+ALTER TABLE t22 ALTER COLUMN col1 SET DATA TYPE domain6 USING col1;
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain1;
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain2;
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain2 USING a;
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain2 USING a::domain2;
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain2 USING b;
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain2 USING b::domain2;
+ALTER TABLE t22 ALTER COLUMN c SET DATA TYPE domain5 USING 'hello' COLLATE "C";
+
+ALTER TABLE t22 ALTER COLUMN c SET DATA TYPE domain5; -- no table rewrite
+CREATE INDEX t22_idx_c ON t22(c);
+SET client_min_messages to debug1;
+ALTER TABLE t22 ALTER COLUMN c SET DATA TYPE varchar COLLATE "POSIX"; -- no table rewrite, no index rebuild
+-- no table rewrite, but index will rebuild because index collation changes
+ALTER TABLE t22 ALTER COLUMN c SET DATA TYPE varchar COLLATE "C";
+RESET client_min_messages;
+
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain3; -- table rewrite
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain4; -- table rewrite, then fail
+ALTER TABLE t22 ALTER COLUMN b SET DATA TYPE domain3; -- table rewrite
+
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain1 USING ((a)); -- table rewrite
+ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain1 USING (a+0)::domain2; -- table rewrite, then fail
+DROP TABLE t22;
+DROP DOMAIN domain1, domain2, domain3, domain4, domain5;
+
-- Fall back to full rewrite for volatile expressions
CREATE TABLE T(pk INT NOT NULL PRIMARY KEY);
--
2.34.1
