Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, Feb 17, 2020 at 8:21 AM Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Jeremy Finzel <finzelj@xxxxxxxxx> 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?

Thanks!
Jeremy

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux