Avoid some table rewrites for ALTER TABLE .. SET DATA TYPE array coerce
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.
ArrayCoerceExpr have two node expression, ``arg`` and ``elemexpr``, therefore
ATColumnChangeRequiresRewrite, the old single ``FOR(;;)`` loop is not enough, we
need recursive walking through ArrayCoerceExpr->arg and
ArrayCoerceExpr->elemexpr.
Please see the attached POC.
DEMO:
create table rewriteme (id serial primary key, foo float, bar timestamptz);
begin;
alter table rewriteme add column ttz_arr timestamptz[];
set timezone to 'UTC';
alter table rewriteme alter column bar type timestamp;
---- no table rewrite with PATCH, require table rewrite in HEAD
alter table rewriteme alter column ttz_arr type timestamp[];
rollback;
CREATE DOMAIN domain1 as INT;
CREATE TABLE test_set_col_type(a INT[]);
---- no table rewrite with PATCH, require table rewrite in HEAD
ALTER TABLE test_set_col_type ALTER COLUMN a SET DATA TYPE domain1[];
---- no table rewrite with PATCH, require table rewrite in HEAD
ALTER TABLE test_set_col_type ALTER COLUMN a SET DATA TYPE INT[];
Attachments:
v1-0001-Avoid-some-table-rewrites-for-ALTER-TABLE-.-SET-DATA-TYPE-array-c.patchtext/x-patch; charset=US-ASCII; name=v1-0001-Avoid-some-table-rewrites-for-ALTER-TABLE-.-SET-DATA-TYPE-array-c.patchDownload+132-30
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.