On Dec 19, 2007, at 6:37 PM, Josh Harrison wrote:

> Hi
> I have a couple of questions regarding applying  schema changes in  
> slony
> The manual says
> "When you run EXECUTE SCRIPT, this causes the slonik to request,  
> for each table in the specified set, an exclusive table lock"
> So all the tables in the master and slave are locked for that time?  
> So the applications accessing that specified set of tables will  
> receive some error or something atthat particular time? How long is  
> this time generally?

The time it takes is a function of:

how long it takes to acquire the lock (all other transactions must  
complete first)
how long the ddl you are executing takes to run.

The latter could be anywhere from instantaneous to many hours  
depending on what you are doing and on how much data.

Note that you can control when the execute script actually runs on  
the secondaries by stopping and starting the slon daemons. This  
allows you to switch your applications to a secondary while the  
script is executing on the primary, and vice-versa when it executes  
on the secondaries. Of course while you are switched to the  
secondaries, your application cannot write to replicated tables,  
which are read-only there.

> In the production environment is this okay? Anyone had tried this?  
> Are there any other problems that you had encountered using execute  
> script in the production env?  Is there any other alternatives?

Whether it is ok or not is a total matter of policy. Execute script  
is typically not problematic in and of itself, but it's locking  
behavior is something that needs careful consideration. It means that  
you can have no client transactions on the replicated table set for  
the duration of the execute script. If you have clients executing  
transactions concurrently with execute script, you will find that it  
creates deadlocks rather readily.

Note that structural changes to your database (alter table, etc) will  
require exclusive locks on tables anyhow, so it could interfere with  
applications in a similar way to execute script. What makes execute  
script more disruptive is that it always takes exclusive locks on all  
tables in the table set every time.

Here are some "alternatives" for mitigating execute script's locking  
behavior:

- Switch applications to other nodes while a node is being upgraded.  
Modulate the upgrade timing by stopping and starting the slon  
daemons. (This is what I do)
- Use many small table sets instead of one big one. This means you  
may need multiple execute script statements, but each one will lock  
fewer tables at once.
- Don't use execute script for things that don't directly affect  
replication. For example, adding an index. Note that execute script  
can be very useful for large data changes, however, because changing  
millions of rows can be very painful to replicate row-by-row.

hth,

-Casey
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20071221/388ddc77/attachment.htm