On Mon, 2010-07-12 at 08:56 -0700, Waldo Nell wrote:
> On 2010-07-12, at 06:43 , Brad Nicholson wrote:
> 
> > (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).
> > 
> 
> Do I need to run this in an EXECUTE SCRIPT? Or directly on the DB?  While Slony is running?

I don't know about running this in an EXECUTE SCRIPT.  I think that
renaming replicated tables doesn't work well with EXECUTE SCRIPTS.

In both cases, you need to make sure that there is no new data going
into the table.  If there is, you will lose it.

How you do this would depend on the Slony version you are running.

With 1.2.x, you would drop the table from replication, perform the above
on the master only (directly, not through Slony), and resubscribe it -
with a full data copy.  This will probably take some time for a table of
that size, which may or may not be acceptable to you.

With Slony 2.0, you would drop the table from replication, perform the
transformation directly on all nodes, and subscribe the table back with
the "OMIT COPY" option.  This assume that the data on all nodes is is
sync, and does not do the copy command.  If the data is not is sync, you
will break things.

With either of these, test them out, and be sure that you understand
them fully.  

If not, stick with Andrew's suggestion.  It is the simplest, but the
most time consuming.


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