Altering a column (increasing size) in Postgres takes long time

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

 



We have a huge table with hundred million records. We need to increase the size of an existing column with varchar type, and it has been running for more than 12 hours.

 

We’re using: ALTER TABLE Property ALTER COLUMN "situs-number" TYPE varchar(30);

 

The index on that column has been dropped before issuing the above statement.

 

1)       Can you explain what’s happening internally that make this a very long process? Does the table get re-created?

 

2)       Assuming the Alter statement finished successfully, And if I didn’t drop the index (on that column), do I have to rebuild the index? Does the index get invalidated for just alter the indexed column?

 

3)       Some folks referred to directly updating Postgres internal tables (pg_attribute) which takes seconds to make the column change happen. How safe if this and would potentially cause any corruption?

 

SET SESSION AUTHORIZATION 'postgres';

 

BEGIN;

 

update pg_attribute

set atttypmod = 21 + 4

where attrelid = 'property'::regclass

and attname = 'situs-number';

 

update pg_attribute

set atttypmod = 21 + 4

where attrelid = 'interim-refresh'::regclass

and attname = 'situs-number';

 

update pg_attribute

set atttypmod = 21 + 4

where attrelid = 'interim-drefresh'::regclass

and attname = 'situs-number';

 

update pg_attribute

set atttypmod = 21 + 4

where attrelid = 'interim-update-property'::regclass

and attname = 'situs-number';

 

update pg_attribute                                             

set atttypmod = 21 + 4

where attrelid = 'ix-property-address'::regclass

and attname = 'situs-number';

 

RESET SESSION AUTHORIZATION;

 

 

4)       Is there a more practical and safe method  to alter a huge table with reasonable amount of time? 

 

Please advise. Your help is much appreciated.

We’re running Postgres 8.3.7 on RedHat Enterprise AS 4.7 on HP585DL.

 

Regards,

      Husam

****************************************************************************************** 
This message may contain confidential or proprietary information intended only for the use of the 
addressee(s) named above or may contain information that is legally privileged. If you are 
not the intended addressee, or the person responsible for delivering it to the intended addressee, 
you are hereby notified that reading, disseminating, distributing or copying this message is strictly 
prohibited. If you have received this message by mistake, please immediately notify us by  
replying to the message and delete the original message and any copies immediately thereafter. 

Thank you. 
****************************************************************************************** 
FACLD

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux