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

On 2/17/20 9: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 much faster would "it" be if you dropped the index, ran ALTER and
rebuilt the index?  Or is it too late?

--
Angular momentum makes the world go 'round.