On Sat, 2010-07-10 at 09:00 -0400, Steve Singer wrote:
> On Fri, 9 Jul 2010, Waldo Nell wrote:
> 
> > I need to run an update statement pretty much like:
> >
> > update XXX set Y = 'Y' where Z = 1;
> >
> > but table XXX contains approximately 130 million rows.  This will 
> > translate to 130 million update statements via Slony-I to the other 
> > replicated node.  Any more efficient way to update this?  If not, will 
> > Slony-I break on this update?
> 
> If you can afford to take an outage on other activity on your database (not 
> just that table but all replicated tables) you can submit the update 
> statement through EXECUTE SCRIPT.  That way only the UPDATE statement will 
> be sent via slony and it will execute on each of the nodes (as a single big 
> update).

In most cases - updating 130 million records is going to take a really,
really long time.  If your updates are not doing HOT updates, you'll be
left with a horrible bloated table which will then need a cluster or a
dreaded VACUUM FULL+REINDEX, which will take an additional really long
time.  This is a Postgres issue that has nothing to do with Slony.
Updating large tables can be nasty.

If you are using Slony 2.0 - the quickest way to do this, again, if you
can do this in an outage:

(assuming Y and Z are the only columns, if there are others, include
them accordingly)

CREATE TABLE XXX_new LIKE XXX (exclude the indexes)
INSERT INTO XXX_new (Y,Z) SELECT Y,Z where  Z <> 1;
INSERT INTO XXX_new (Y,Z) SELECT 'Y',Z where  Z = 1;

ALTER TABLE XXX RENAME XXX_old;
ALTER TABLE XXX_new RENAME XXX;

Then add the indexes back on (rename the indexes on XXX_old first if you
want to maintain the index names).

You can use the same approach with Slony 1.2, but you will need to do a
full resubscribe of the table afterwards, which may not be acceptable.

If that's the case - Andrews batch update approach is pretty much the
only way to go.

-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.