Thread: increasing varchar column size is taking too much time

increasing varchar column size is taking too much time

From
AI Rumman
Date:
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:
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