On Friday 04 April 2003 13:38, you wrote:
> hello,
>
> 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".
May be I don't get it right, but altering defaults on live table, would yield
inconsistent data. Some NULLS in past rows and zeros being inserted in new
rows and that is not correct. Am I right here?
Furthermore mass updating close to 450,000 row would give you a nice waste of
dead tuples and your next vacuum will take loooong time unless you are
vacuuming in tight loops while updating.
I recommend you schedule a downtime, dump the table and reload it. This way
there would be no wasted tuples. Any change in database schema should go thr.
a scheduled maintenance , if you ask me.
Shridhar