Re: Cases where alter table set type varchar(longer length) stillneeds table rewrite - Mailing list pgsql-general

From Ron
Subject Re: Cases where alter table set type varchar(longer length) stillneeds table rewrite
Date
Msg-id d51cdfcc-2cbb-c23b-0590-434931a85905@gmail.com
Whole thread Raw
In response to Re: Cases where alter table set type varchar(longer length) stillneeds table rewrite  (Jeremy Finzel <finzelj@gmail.com>)
List pgsql-general
On 2/17/20 9:01 AM, Jeremy Finzel wrote:
On Mon, Feb 17, 2020 at 8:21 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jeremy Finzel <finzelj@gmail.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.

pgsql-general by date:

Previous
From: Jeremy Finzel
Date:
Subject: Re: Cases where alter table set type varchar(longer length) stillneeds table rewrite
Next
From: Jason Swails
Date:
Subject: Re: Cannot connect to postgresql-11 from another machine after boot