On Mon, 19 Mar 2007, Andrew Sullivan wrote:

> This piece of functionality seems to me to be exactly the sort of area
> where using savepoints on the remote note might be a good idea, but
> I'm worried about the potential for really nasty locks.  I think in
> the medium term, we probably need a complete proposal on how to
> improve EXECUTE SCRIPT's safety, or some sort of comprehensive
> discussion about the various locks that are being taken and the like.
> I'm slightly worried that some of the design in that area is happening
> a little _ad hoc_, and I think a more complete idea of what the
> trade-offs are, and which ones we can countenance, would be really
> nice to have.

In this case are we better off instead having slonik connect to the 
subscriber that the script is destined for and applying it there directly? 
For example if only_on is specified to be a subscriber then allow the event 
node to  be a subscriber as well?  You should be able to get this behavior 
today by creating an empty set on your subscriber/target where that 
subscriber is the origin to that set.   There are certain use-cases that 
this approach would disallow (Cases where slonik can't connect directly to 
the subscriber, if in the future we had a all_nodes_except_origin option to 
execute script; but I'm not sure if these use cases are realistic enough to 
be concerned about)

Once you start performing execute scripts only on some nodes then your data 
and/or schema won't be the same everywhere in the cluster and in any 
master/slave replication system if you don't do this very carefully your 
going to get burned.

Maybe we should start with trying to decide what the purpose of execute 
script is.

The main uses for execute script that I see are:

1. DDL changes
2. If you want to change a run a query that changes data without having to 
replicate the results statement by statement.  Ie something like 'INSERT 
INTO x ...SELECT FROM y'
3. You want to fire off a stored procedure on all nodes that might have side 
effects that need to executed on all nodes

Feel free to add what I've missed.


The manual says, "Executes a script containing arbitrary SQL statements on 
all nodes that are subscribed to a set at a common controlled point within 
the replication transaction stream"

It's that 'arbitrary' part that makes this very hard.

At this stage in the discussion I think we want to review the thread on 2pc 
from last June.
http://lists.slony.info/pipermail/slony1-general/2006-July/004609.html

Nothing has really changed since then. In some circumstances a global outage 
is acceptable so you can run the script on all nodes before committing to any 
of them.  In other environments this isn't always an option 
(log-shipping among others) and there wil always be 'some' risk of things 
failing on a subscriber.  The more your subscriber is different than your 
provider the more this risk goes up.

Steve


>
>
> -- 
> Andrew Sullivan                         204-4141 Yonge Street
> Afilias Canada                        Toronto, Ontario Canada
> <andrew at ca.afilias.info>                              M2P 2A8
> jabber: ajsaf at jabber.org                 +1 416 646 3304 x4110
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-general
>