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