* Andrew Sullivan <ajs at crankycanuck.ca> [061212 23:10]:
> On Tue, Dec 12, 2006 at 08:22:35PM +0100, Andreas Kostyrka wrote:
> > 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.
> 
> You do have troubles; you just don't know it.  That's what I keep
> trying to impress upon everyone; I don't understand what it is about
> "your trigger doesn't know about all your data, and will behave
> unpredictably" is failing to set off your alarm bells.
> 
> > 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 ;)
> 
> Yes, "seems" is the right word there.  No sarcasm on.  It will
> possibly work for you, and if so, good.  
> 
> > 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?
> 
> Your contribution of a design in which DDL can be safely added (for
> sure, without breaking all the other safeguards that are built into
> Slony as befits an industrial tool) is eagerly awaited.
> 
> This was the compromise we came to: either DDL is unsafe and possibly
I didn't tell that any DDL statement is safe to apply. Actually, it was
some experimentation, thinking, and some shoot feet (aka broken
replication cluster that needed dropping nodes and resubscribing).

> breaks the application of data, or else we have to lock to prevent
> the DDL.  If someone can invent the foot-gun setting that does not
> break things for everyone who likes to operate safely, but that
> allows people to do things unlocked, I expect that there would be
> some interested in it.  Better, if someone can figure out how to
> deliver the results in the agreeable order without imposing the locks
> we already have, I'd be interested.

Warning, test these before you use them in production:

Well, the basic principles are, IMHO, and they seem to be safe, but
I'll recheck to be sure:

1.) rows from master must be insertable into slaves. Aka, it's ok to
have more columns on slaves as long they have a workable default value.

Now to the experimental part of the fun:

2.) changing properties (default, not null) of columns is always ok to
do outside of slony.

3.) adding columns is always safe, as long rule 1 is fulfilled. E.g.
adding a column without a default might involve two steps if necessary.

4.) dropping columns seems to be safe completly outside of slony when
the table has no foreign key constraints.

5.) If dropping a column on a slave gives an error that does not make
sense (e.g. it complains about the type of objects not making sense),
you need to apply the changes to the slaves via EXECUTE SCRIPT with
the EXECUTE ONLY ON option. These is usually not painful as usual,
because locks are easier on readonly replicas :)

Just to make sure, I've rechecked it on a test setup, adding columns
seems safe in practice:

dba at dm01x:~$ psql -c 'insert into test (id) values (100);' test1
INSERT 0 1
dba at dm01x:~$ psql -c 'select * from test' test2 
 id 
----
(0 rows)

dba at dm01x:~$ issue_slonik test "subscribe set(id=1, provider=1, receiver=2, forward=no);"
dba at dm01x:~$ NOTICE:  truncate of "public"."test" succeeded

dba at dm01x:~$ psql -c 'select * from test' test2
 id  
-----
 100
(1 row)

dba at dm01x:~$ psql -c "alter table test add x text;" test2
ALTER TABLE
dba at dm01x:~$ psql -c 'insert into test (id) values (101);' test1
INSERT 0 1
dba at dm01x:~$ psql -c 'select * from test' test2
 id  | x 
-----+---
 100 | 
 101 | 
(2 rows)

dba at dm01x:~$ psql -c "alter table test add x text;" test1
ALTER TABLE
dba at dm01x:~$ psql -c 'insert into test (id) values (102);' test1
INSERT 0 1
dba at dm01x:~$ psql -c 'select * from test' test2
 id  | x 
-----+---
 100 | 
 101 | 
 102 | 
(3 rows)

dba at dm01x:~$ psql -c "insert into test (id,x) values (103,'test');" test1
INSERT 0 1
dba at dm01x:~$ psql -c 'select * from test' test2
 id  |  x   
-----+------
 100 | 
 101 | 
 102 | 
 103 | test
(4 rows)
	       
> 
> So far, all I hear are hand-wavy proposals about how this is all
> easy, and suggestions that having triggers on system catalogues would
> solve all this for us.  Nobody ever replies to the reasons why that's

Nope, it works in practice with slony 1.1.5 and has for about 6-9
months on middle-sized site. It's just something that is really really
hard to automate in Slony. It takes some brain usage to transform a
given set of DDL statements into something that works well with slony.

It works so well, that my ticket (in our trac, not the slony one) to
implement set-specific locking for EXECUTE SCRIPT has been reduced
from Critical to Low priority.

btw, just telling users to use any DDL with EXECUTE SCRIPT is wrong
too (Hint: On the first DROP TABLE you'll get some sharp items thrown
your way.)

> false.  If you really have studied MySQL's replication, and think
> it's good enough for you, I'm happy to hear it.  My data turns out to

No, I'm not happy with mysql. But the decision was partly taken
because it took me half an year to get up to speed, because while the
documentation for Slony is complete (in the reference way), it's a
little bit short on the tutorial side (well beside the initial cluster
tutorial which is ok). E.g. I guess I somehow missed the part that
slony 1.1.5 needs a watchdog setup. Or it was missing from the docs at
the time. It took some broken clusters till I understood why I need a
cronjob that creates SYNC events. It took months till I understood the
decision process when to drop a node from replication (e.g. hardware
is down), and when to let it catch up. 

(Hint: when you've got enough data written into the DB, as in hour
case, any downtime beyond 6 hours forces me to drop the node, or the
slony system becomes sluggish because of all the unprocessed events
sitting around.

> be worth more than my cost-benefit analysis tells me I'll get on
> the benefit side, so I'm going to go for the safe system.  That
> doesn't mean I don't think this would be a useful feature.  It would

As I've pointed out, it's very hard to do as a feature, because for
many DDLs one has to transform them first. It's doable with a
documentation explaining what is ok. Plus if it's documented, future
slony versions shouldn't break the practice. 

Andreas