am Mon, dem 21.04.2008, um 0:19:34 +0200 mailte [EMAIL PROTECTED] 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 and without 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 a status-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't care or want a abort or "all or nothing" like SQL mandates. The > idea is basically that either this update succeeds or it 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 specific problem - that PG is a MVCC > database and will keep my old versions of the rows before the update alive. > This is very bad. > > 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
