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
