"Claudio Lapidus" <clapidus@hotmail.com> writes:
> I need to modify a column which is currently defined as varchar(30) to
> varchar(40). I can see from the docs (and trial) that I cannot directly
> alter a column this way, so I intend to do the following:
> ALTER TABLE t1 ADD COLUMN duplicate varchar(40);
> UPDATE t1 SET duplicate=original;
> ALTER TABLE t1 DROP COLUMN original;
> ALTER TABLE t1 RENAME duplicate TO original;
> But I'm worried about messing up things if I run this queries while the
> database is live, i.e. there are other processes writing to the table.
As you should be.
> if I enclose the above into a BEGIN/COMMIT pair, would you say it is safe to
> run concurrently with other transactions?
Yes, because the first ALTER will take an exclusive lock on table t1,
which will be held through the rest of the transaction. So it will be
safe a fortiori. However, if the table is large you may regret holding
an exclusive lock for all the time it takes to do that UPDATE.
Personally, being a database hacker, I would solve this problem with a
quick modification of the atttypmod field that expresses the column
length:
UPDATE pg_attribute SET atttypmod = 40 + 4 -- +4 for varchar overhead
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 't1')
AND attname = 'original';
Since you are increasing the length limit, and it's varchar not char,
there is nothing that need be done to the data itself, so this is
sufficient.
I would strongly recommend practicing on a scratch database until you
are sure you've got the hang of this ;-). Also you might want to do a
BEGIN first, and not COMMIT until you're sure \d display of the table
looks right.
regards, tom lane