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