On Fri, 4 Apr 2003, Henrik Steffen wrote:
> when doing an "alter table blub add column blah int2"
> and then issuing an "alter table blub alter column blah set default 0"
> I will always have to manually "update blub set blah=0" to initialize
> all existing records.... this is quite annoying while performing this
> update on a running database with 100 simultaneous clients accessing
> the database and about 443482 rows in the table.
>
> it takes about 10 minutes to perform this update, and for about 6 minutes
> all other updates to the table are in status "waiting", this leads to
> a very high load and all max_connection setting is reached soon
> (on a Dual-Xeon 2.4 GHz machine with 2 GB RAM)
>
> i would suggest to change the "alter table add column" function to
> adding a default value while adding the column if this is possible
> and if this minimizes "down-time".
IIRC, ADD COLUMN adds the column to the end of the list, but doesn't
modify the actual rows stored on disk. I think when the column past the
end is accessed on a row that doesn't have it a NULL is returned. SET
DEFAULT just sets the default up but also doesn't modify the actual rows
stored on disk. Presumably SET DEFAULT should probably do the update, but
it's not likely to be appreciably faster than you doing the update.
Other options would include things like updating the rows on select (that
sounds like it could lead to wierd deadlocks between a select and an
update), returning the default rather than NULL for the access (but that
doesn't work for non-immutable defaults unless you update the row during
the select) or building a new copy of the table (requires potentially alot
of disk space). In addition, since the transaction in question probably
has an exclusive lock to the table, the other transactions need to wait
while the alter runs (so a new copy doesn't save you from locking reads
while the table is building unless you drop the lock, which is probably
dangerous). I think this was discussed in the past, so the archives can
probably do a better job than I can (since I wasn't paying all that much
attention).
