Avoid some table rewrites for ALTER TABLE .. SET DATA TYPE array coerce

From: jian he <jian(dot)universality(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Avoid some table rewrites for ALTER TABLE .. SET DATA TYPE array coerce
Date: 2026-03-18 11:06:14
Message-ID: CACJufxE9rmyJv4a-gBxMykFPgzom1OYHp0XnNSYGfzEKQA=N_w@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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[];

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

Attachment Content-Type Size
v1-0001-Avoid-some-table-rewrites-for-ALTER-TABLE-.-SET-DATA-TYPE-array-c.patch text/x-patch 9.5 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Aleksander Alekseev 2026-03-18 11:14:27 Re: Add uuid_to_base32hex() and base32hex_to_uuid() built-in functions
Previous Message Peter Eisentraut 2026-03-18 10:56:24 Re: [PATCH] Add `headerscheck` run_target to meson