Re: feature request - adding columns with default value

fortunately, the vacuum after the update does only take
approx. 30 seconds thanks to the ultra-fast machine we
are using...


--

Mit freundlichem Gruß

Henrik Steffen
Geschäftsführer

top concepts Internetmarketing GmbH
Am Steinkamp 7 - D-21684 Stade - Germany
--------------------------------------------------------
http://www.topconcepts.com          Tel. +49 4141 991230
mail: steffen@topconcepts.com       Fax. +49 4141 991233
--------------------------------------------------------
24h-Support Hotline:  +49 1908 34697 (EUR 1.86/Min,topc)
--------------------------------------------------------
Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
System-Partner gesucht: http://www.franchise.city-map.de
--------------------------------------------------------
Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
--------------------------------------------------------

----- Original Message -----
From: "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
To: "pgsql" <pgsql-general@postgresql.org>
Sent: Friday, April 04, 2003 10:24 AM
Subject: Re: [GENERAL] feature request - adding columns with default value


> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org