On Mon, Jan 25, 2010 at 09:39:51AM -0500, Andrew Sullivan wrote:
> > Would this work or did I overlooked something?
> 
> This is actually what Slony does.  So it will work.  But it still
> locks everything, note.

"Everything" as in "each object" or "the table in access exclusive" ?

For what I understand (not so much actually), we have :
"execute script"
  -> begin;
  -> ddlScript_prepare()
     for each replicated table:
       -> alterTableRestore(tab_id)
          -> lock table foo in in access exclusive mode
  -> ddls
  -> ddlScript_complete()
     for each replicated table:
       -> alterTableForReplication(tab_id)
          -> lock table foo in in access exclusive mode
  -> commit;

So if I get things well alterTable* functions only locks one table (plus
a select for update on sl_table) while "execute script" locks them all
successively in a large transaction. I tested this by manually locking 
unrelated tables from the replication set on both master and subscribers,
while running the previously mentioned scenario (with just a SHARE MODE
lock for this transaction).

The ugly, dangerous but selective manual alterTable* call seems a lesser
"everything" than create script ;)

> You need to do everything _last_ on the origin node because if it gets
> the extra column at a logical point in transaction history time when
> any of the replicas aren't ready for such a column, then that replica
> will be broken.

Ah, this makes sense.
Thank you very much for this advice - I would have overlooked this !

> I encourage you very strongly to test everything out in a lab first,
> several times, before you do any of this.

Yeah, my curse is I have no very realistic "lab" offhand, a large and busy
dataset which I'm not allowed to block, a replication setup we can't afford
to rebuild in case of problem, and no choice but altering every other day
as developers changes their schemas :(