* Brad Nicholson <bnichols at ca.afilias.info> [061212 20:42]:
> On Tue, 2006-12-12 at 20:22 +0100, Andreas Kostyrka wrote:
> > * Andrew Sullivan <ajs at crankycanuck.ca> [061212 19:27]:
> > > On Tue, Dec 12, 2006 at 06:48:16PM +0100, hubert depesz lubaczewski wrote:
> > > > question is - what is the worst case scenario? what should happen for me to
> > > > get punished for this?
> > > 
> > > - Inserts to the master break
> > > - Replication attempts break (on a set containing the data that's
> > > causing the problem) -- which blocks all subsequent replication too,
> > > note.
> > > 
> > > > as for now - even with bad (kvvvv instead of kvvvvv) triggers i still get
> > > > good results (kudos to dev team).
> > > 
> > > No, you're _not_ getting good results.  You're getting lucky.  The
> > > problem crops up in a way that makes it hard to predict when it will
> > > happen (it's not indeterminate, it's just got a lot of variables).
> > > 
> > > > is it safe but not sugested, or there is some scenario which could lead to
> > > > something bad? if yes, then what scenario and what bad?
> > >
> > > No, it is not safe.  This is the basis of the discussion recently. 
> > > See the recap above.
> > 
> > Ok, so how does the 
> > psql -c 'alter table x add column y text;' -h slave
> > psql -c 'alter table x add column y text;' -h master
> > 
> > break? I need to ask as I've been using it for over 6 months without
> > troubles.
> 
> As mentioned, the slony triggers are aware of the columns on the tables.
> When you add a column manually, it is not aware of that column.
Interestingly it seems to replicate the column anyway? At least nobody
ever complained about columns not getting replicated to me in the last
9 months, and this is quite unprobable, as most readonly queries are
done against slaves herearound nowadays => if something wouldn't get
replicated I'm almost certain somebody would have came to me to hunt
for my scalp already.
> 
> > The only thing that I've noticed is, that in some cases (related to
> > foreign key constraints when dropping columns and probably the way
> > that slony breaks the schema data), one needs to apply the
> > changes to the slaves via EXECUTE SCRIPT on the slave nodes only.
> > 
> > > 
> > > If you need to do DDL on a resplicated table, you REALLY REALLY do
> > > need to allow the blocking.  Sorry.  (And are you really telling me,
> > > anyway, that you can't block for even 5 minutes one time in a
> > > planned way?  Slony does not provide "five 9s", you know.)
> > <sarcasm source=some_devs_at_the_office>
> > Well, mysql seems to have "sensible" replication that handles DDLs
> > sensibly. Well, that's why PostgreSQL and slony are scheduled here to
> > be phased out ;)
> > </sarcasm>
> 
> MySQL replication is littered with all sorts of gotchas (some involving
> data loss to the replicas) - under normal operation.  
> 
> Here's the official ones:
> 
> http://dev.mysql.com/doc/refman/5.0/en/replication-features.html
> 
> There are others floating around that involve data being silently lost.
> Google around a bit.
> 
> > Yes, it's possible to do planned shutdowns. It's just very bothersome,
> > and doesn't look to well, when all the other components are being
> > optimized for upgrades under load, and I need to kill the DB
> > connections to add a field? While I can rollout major upgrades to the
> > application without interrupting service?
> 
> Is this not better than explaining to your superiors why you tanked your
> replicas, or worse, you data, by ignoring absolutes about the way your
> tool works?
See above, it seems to work in practice. Nobody complained about
columns not getting replicated. And these guys use these new columns
by writing to the master and reading from the slave. I'd expect that
the slony triggers take the current record that is provided somehow,
because it works for dropping columns too. And if the trigger would
reference the old field, it should completly break, or
complain on the drop field. 

Actually my downtimes everytime the schema was changed basically led
to the decision to port to mysql, because mysql replication handles
DDL trivially :((( (Actually, mysql was just the first questionable
decisions in the wake of the perception that slony is hard to use ;) )

Andreas