Re: Need to update all my 60 million rows at once without transactional integrity

am  Mon, dem 21.04.2008, um  0:19:34 +0200 mailte christian_behrens@gmx.net folgendes:
> Hi!
>
> How can I make a Update of a column in a very large table for all rows without using the double amount of disc space
andwithout any need for atomic operation? 
>
> I have a very large table with about 60 million rows. I sometimes need to do a simple update to ALL rows that resets
astatus-flag to zero. 
>
> I don't need to have transactional integrity (but of course if the system crashes, there should be no data
corruption.A separate flag in the file system can well save the fact that that bulk update was in progress) for this, I
don'tcare or want a abort or "all or nothing" like SQL mandates. The idea is basically that either this update succeeds
orit succeeds or - there is no "not". It must succeed. It must be tried until it works. If the update is halfway
finished,that's okay. 
>
> If I just do an
> UPDATE table SET flag=0;
> then Pg will make a copy of every row which must be cleaned up by vaccuum. I understand - and don't like during this
specificproblem - that PG is a MVCC database and will keep my old versions of the rows before the update alive. This is
verybad. 
>
> If I do a batched loop like this:
> UPDATE table SET flag=0 where id>=0 and id <200;
> UPDATE table SET flag=0 where id>=200 and id <400;
> UPDATE table SET flag=0 where id>=400 and id <600;


Don't forget to VACUUM after every Update...


>
> Is there any other way to go?

Update to 8.3 and profit from the new HOT feature (wild guess: there is
no index on this flag-column)


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net