Re: Cases where alter table set type varchar(longer length) still needs table rewrite

On 2/17/20 7:01 AM, Jeremy Finzel wrote:
> On Mon, Feb 17, 2020 at 8:21 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> Jeremy Finzel <finzelj(at)gmail(dot)com <mailto:finzelj(at)gmail(dot)com>> writes:
> > I have a table foo with 100 million rows, and a column:
> >    - id character varying(20)
> > The following command is the one that we expect to execute very
> quickly (we
> > are not seeing any locking), but it is instead taking a very long
> time:
> >    - ALTER TABLE foo ALTER COLUMN id TYPE varchar(100);
>
> Hm, the code is supposed to avoid a table rewrite, but I wonder if
> there's something else that's not being avoided, such as an index
> rebuild or foreign-key verification.  Could we see the whole table
> definition, eg from psql \d+ ?
>
>                         regards, tom lane
>
>
> Based on your feedback, I quickly identified that indeed, the following
> index is causing the re-type to be slow:
>
> "id_idx" btree ("substring"(id::text, 4, 7))
>
> I'm still not sure why a rebuild of this index would be required,
> technically speaking.  But perhaps in any case the docs should have
> something to the effect that expression indexes may require rebuild
> under specific circumstances?

How about?:

https://www.postgresql.org/docs/9.6/sql-altertable.html

"Adding a column with a DEFAULT clause or changing the type of an
existing column will require the entire table and its indexes to be
rewritten. As an exception when changing the type of an existing column,
if the USING 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; but any indexes on the
affected columns must still be rebuilt. Adding or removing a system oid
column also requires rewriting the entire table. Table and/or index
rebuilds may take a significant amount of time for a large table; and
will temporarily require as much as double the disk space."

>
> Thanks!
> Jeremy

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com