MIME-Version: 1.0
References: 
 <CACJufxE9rmyJv4a-gBxMykFPgzom1OYHp0XnNSYGfzEKQA=N_w@mail.gmail.com>
In-Reply-To: 
 <CACJufxE9rmyJv4a-gBxMykFPgzom1OYHp0XnNSYGfzEKQA=N_w@mail.gmail.com>
From: Matthias van de Meent <boekewurm+postgres@gmail.com>
Date: Wed, 18 Mar 2026 12:47:56 +0100
Message-ID: 
 <CAEze2Wi4M1grsR0q27etuB-jncJ6qN-1FMdRX-2PkQxcFpM3sQ@mail.gmail.com>
Subject: Re: Avoid some table rewrites for ALTER TABLE .. SET DATA TYPE array
 coerce
To: jian he <jian.universality@gmail.com>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAEze2Wi4M1grsR0q27etuB-jncJ6qN-1FMdRX-2PkQxcFpM3sQ%40mail.gmail.com>
Precedence: bulk

On Wed, 18 Mar 2026 at 12:06, jian he <jian.universality@gmail.com> wrote:
>
> Hi.
>
> Context: https://git.postgresql.org/cgit/postgresql.git/commit/?id=3c5926301aea476025f118159688a6a88b2738bc
> Also see build_coercion_expression, COERCION_PATH_ARRAYCOERCE handling.
>
> It's doable to skip a table rewrite when changing a column's data type from
> one array type to another. We just need some logic to handle
> ArrayCoerceExpr within ATColumnChangeRequiresRewrite.

Are you sure about that?

IIRC, arrays store the element data type's OID on disk (^1), and if we
move from arrays of type X to arrays of type Y then that should also
change the array's on-disk OID: Generic array functions like array_out
rely on the array's type oid to select the lower-level functions to
call into when recursing; and would get the wrong type information if
we don't update this.
This could cause visible differences in output when you change the
time zone of your database after changing the column type from e.g.
timestamptz[] to timestamp[], or enum[] to oid[] if we ever implement
a binary coercible cast from enum to oid (or int, but oid seems more
appropriate).


Kind regards,

Matthias van de Meent
Databricks (https://www.databricks.com)

(^1): Possibly DOMAIN-typed arrays only store the base type, in which
case this would work in the restricted case of coercing to
domain-typed arrays, but I don't think the general case of
binary-coercible types is allowable.