Обсуждение: [PATCH] no table rewrite when set column type to constrained domain

hi.

the attached patch is to implement the $subject feature.
i was mainly intrigued by the discussion in
https://www.postgresql.org/message-id/20190226061450.GA1665944@rfd.leadboat.com

the main gotcha is struct NewColumnValue.
we do ``palloc0(sizeof(NewColumnValue));`` in ATExecAddColumn,
ATExecSetExpression, ATPrepAlterColumnType.

ATExecAddColumn:  Adding a new column with domain with constraints will cause
                  table rewrite.
ATExecSetExpression: for stored generated column will cause table rewrite, we do
                     not support domain over virtual generated columns now.
ATPrepAlterColumnType: we only do table rewriting occasionally.
see ATColumnChangeRequiresRewrite.

If table rewrite is required, then there is nothing we can do. so
we only need to focus on ATPrepAlterColumnType.
we can add a new boolean field, coerce_to_domain, to NewColumnValue. this field
is set to true only when changing an existing column's type to a constrained
domain. In such cases, a table scan is enough—no table rewrite is needed.
coerce_to_domain will set to false, if table rewrite is required.

On Thu, Jul 10, 2025 at 2:00 AM jian he <jian.universality@gmail.com> wrote:
>
> we can add a new boolean field, coerce_to_domain, to NewColumnValue. this field
> is set to true only when changing an existing column's type to a constrained
> domain. In such cases, a table scan is enough—no table rewrite is needed.
> coerce_to_domain will set to false, if table rewrite is required.

I realized that "coerce_to_domain" is not so good in this context.
maybe there are other scenarios, we added a NewColumnValue and we also
only need table scan.
so I changed it to scan_only.


/*
 * ....
 * If scan_only is true, it means only a table scan is required.
 * Currently, this is supported only by the ALTER COLUMN SET DATA TYPE command,
 * where the column's data type is being changed to a constrained domain.
 */
typedef struct NewColumnValue
{
    AttrNumber    attnum;         /* which column */
    Expr       *expr;                    /* expression to compute */
    ExprState  *exprstate;         /* execution state */
    bool        is_generated;       /* is it a GENERATED expression? */
    bool        scan_only;           /* table scan only */
} NewColumnValue;

On Tue, Aug 26, 2025 at 11:26 AM jian he <jian.universality@gmail.com> wrote:
>
> typedef struct NewColumnValue
> {
>     AttrNumber    attnum;         /* which column */
>     Expr       *expr;                    /* expression to compute */
>     ExprState  *exprstate;         /* execution state */
>     bool        is_generated;       /* is it a GENERATED expression? */
>     bool        scan_only;           /* table scan only */
> } NewColumnValue;

I changed scan_only to need_compute.

+ *
+ * If need_compute is true, we will evaluate the new column value in Phase 3.
+ * Currently, this is only used in ALTER COLUMN SET DATA TYPE
command, where the
+ * column’s data type is being changed to a constrained domain, and all the
+ * domain's constraints are non-volatile. In case table rewrite, we also set it
+ * to true.
  */
 typedef struct NewColumnValue
 {
@@ -238,6 +244,7 @@ typedef struct NewColumnValue
     Expr       *expr;            /* expression to compute */
     ExprState  *exprstate;        /* execution state */
     bool        is_generated;    /* is it a GENERATED expression? */
+    bool        need_compute;    /* compute this new expression in Phase 3 */
 } NewColumnValue;

I use domain over domain for regress tests.
I also constrained the no–table-rewrite behavior to cases where the coercion is
to a domain type and all constraints of the new domain are non-volatile.

Demo:

CREATE DOMAIN domain1 AS INT CHECK(VALUE > 1) NOT NULL;
CREATE DOMAIN domain11 AS domain1 CHECK(VALUE > 1) NOT NULL;
CREATE DOMAIN domain21 AS domain1 CHECK(VALUE > random(min=>10,
max=>10)) NOT NULL;
CREATE DOMAIN domain3 AS INT8;
CREATE TABLE t22(a INT, b INT);
INSERT INTO t22 VALUES(-2, -1);

-- no table rewrite, but fail at domain constraint check
ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain11 USING a::domain11;
-- no table rewrite, but fail at domain constraint check
ALTER TABLE t22 ALTER COLUMN b SET DATA TYPE domain11 USING b::domain11;

-- table rewrite
ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain21;
ALTER TABLE t22 ALTER COLUMN b SET DATA TYPE domain3;
ALTER TABLE t22 ALTER COLUMN a SET DATA TYPE domain1 USING (a+0)::domain1;



--
jian
https://www.enterprisedb.com

On Sun, Mar 15, 2026 at 12:32 AM Viktor Holmberg <v@viktorh.net> 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
inthis 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
couldexplain 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/

hi.

https://postgr.es/m/CAEze2Wi4M1grsR0q27etuB-jncJ6qN-1FMdRX-2PkQxcFpM3sQ@mail.gmail.com
Make me realize that ArrayCoerceExpr (one type of array cast to
another type of array) table rewrite is necessary.
For example, the following will cause a table rewrite:
+CREATE TABLE t22(a INT, b INT, c text COLLATE "C", col1 INT[]);
+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;

Now: if the new type is a constrained domain over the old type,
tablescan is enough.
ATColumnChangeRequiresRewrite works just as before.



--
jian
https://www.enterprisedb.com/

On Tue, Mar 24, 2026 at 12:18 AM Viktor Holmberg <v@viktorh.net> wrote:
>
> This appears to address some of my comments but not this one?
>
> CREATE DOMAIN domain1 AS INT CHECK(VALUE > 1) NOT NULL;
> CREATE TABLE t_const_using(a INT);
> INSERT INTO t_const_using VALUES(-2);
> ALTER TABLE t_const_using ALTER COLUMN a SET DATA TYPE domain1 USING 5;
> SELECT a FROM t_const_using; -- should be 5 after rewrite, not -2
>  a
> ----
>  -2

Sure, these tests are added to v6.

--
jian
https://www.enterprisedb.com/