Thread: increasing varchar column size is taking too much time

increasing varchar column size is taking too much time

From

AI Rumman

Date:

14 February 2015, 05:42:22

Hi,

I started the following query in Postgresql 9.1 where only this sql is running on the host and it has been taking more than an hour and still running.

alter table userdata.table1 alter column name type varchar(512);

Here is the table description:

d+ userdata.table1
Table "userdata.table1"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+------------------------+----------+-------------------------
id | character varying(50) | not null | extended |
code | character varying(32) | not null | extended |
accid | character varying(50) | not null | extended |
name | character varying(100) | not null | extended |
create_time | timestamp with time zone | not null default now() | plain |
modified_time | timestamp with time zone | not null default now() | plain |
install_date | timestamp without time zone | | plain |
recent_scan_date | timestamp without time zone | | plain |
update_date | timestamp without time zone | | plain |
setting | character varying(100) | | extended |
name | character varying(100) | | extended |
type | character varying(8) | | extended |
version | character varying(128) | | extended |
package | character varying(255) | | extended |
permission | text | | extended |
trigger | character varying(10) | | extended |
reasons | character varying(200) | | extended |
note | character varying(255) | | extended |
size | bigint | | plain |
usage | bigint | | plain |
running | character varying(4) | | extended |
location | character varying(60) | | extended |
can_stop | character(1) | | extended |
can_uninstall | character(1) | | extended |
flagged_status | character(1) | | extended |
status | character(1) | | extended |
consultation_status | character(1) | | extended |
trust | character(1) | | extended |
Indexes:
"table1_pk" PRIMARY KEY, btree (id, code)
"table1_accid_id_hashcode_idx" btree (accid, id, code)
"table1_accid_idx" btree (accid)
"table1_id_idx" btree (id)
Triggers:
table1s_delete_trigger BEFORE DELETE ON table1 FOR EACH ROW EXECUTE PROCEDURE delete_jangles_table1()
table1s_insert_trigger BEFORE INSERT ON table1 FOR EACH ROW EXECUTE PROCEDURE insert_jangles_table1()
Child tables: table1_0,
table1_1,
table1_10,
table1_2,
table1_3,
table1_4,
table1_5,
table1_6,
table1_7,
table1_8,
table1_9
Has OIDs: no

Here are the number of rows and pages in partition:

relname | reltuples | relpages
---------------+-----------+----------
table1_0 | 10076840 | 362981
table1_1 | 10165073 | 366548
table1_2 | 10046372 | 361838
table1_3 | 10114727 | 364360
table1_4 | 10155816 | 366054
table1_5 | 10188953 | 367023
table1_6 | 10275270 | 370887
table1_7 | 10163937 | 366245
table1_8 | 10262516 | 369350
table1_9 | 10359893 | 372099
table1_10 | 10434026 | 375327
table1 | 0 | 0

Any idea why the above ALTER statement is taking that much time?

Is it because of the number of rows we have in each partition?

Any suggestion for it?

Re: increasing varchar column size is taking too much time

From

Tom Lane

Date:

14 February 2015, 18:41:52

AI Rumman <rummandba@gmail.com> writes:
> I started the following query in Postgresql 9.1 where only this sql is
> running on the host and it has been taking more than an hour and still
> running.
>  alter table userdata.table1 alter column name type varchar(512);

Pre-9.2 releases don't realize that that doesn't require a table rewrite.

You can either wait it out, or if you're feeling brave, manually modify
the column's pg_attribute.atttypmod field.

            regards, tom lane