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

From: Jeremy Finzel <finzelj(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Cases where alter table set type varchar(longer length) still needs table rewrite
Date: 2020-02-17 14:04:53
Message-ID: CAMa1XUiqdpJZHq_bDAsvbEZw=RjtOuH3z1xz-Xd27a6b1Pf9xA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Good morning!

We are a little bit puzzled because running the following command on a 9.6
cluster is apparently requiring a table rewrite, or at least a very long
operation of some kind, even though the docs say that as of 9.2:

-

Increasing the length limit for a varchar or varbit column, or removing
the limit altogether, no longer requires a table rewrite. Similarly,
increasing the allowable precision of a numeric column, or changing a
column from constrained numeric to unconstrained numeric, no longer
requires a table rewrite. Table rewrites are also avoided in similar cases
involving the interval, timestamp, and timestamptz types.

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);

I attempted the same operation instead cast to text - same problem.

Why do the docs seem wrong in our case? I have a guess: if the table was
created prior to version 9.2, perhaps they are not binary coercible to text
after 9.2? In any case, I would be very grateful for an explanation!

Thank you!
Jeremy

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2020-02-17 14:21:12 Re: Cases where alter table set type varchar(longer length) still needs table rewrite
Previous Message Nick Renders 2020-02-17 11:36:24 Re: PL/pgSQL question about EXCEPTION clause & corrupt records