"Marco Canderle" <marcocanderle at gmail.com> writes:
> Hi, I'm new here so hello everyone. I've been experimenting with
> Slony-I to deploy a replication system for the database at my work.

> My objective is to have a master server in headquarter office,
> replicating to every slave server at the satellite offices. The idea
> is that users located in headquarters read from and write to master
> server. The users located in satellite offices read from the local
> slave server and write directly to the master server (which then
> replicates to all slaves databases, including the one who originate
> the write).

> This has to be done this way mainly for two reasons:

> ?? -If the master gets offline, the remote users can continue
> accessing information because they read from the local slave server.

> ?? -An increase in the response time: The system is web-based
> (implemented in PHP). For the remote users, in the other end of the
> country, the response time is much more slower than for the users
> near the master server, due to the Internet connection. And the
> majority of the database access are reads not writes.

> As I said, Reads are solved locally by every local slave server, and
> writes are routed directly to the master server, which then
> replicates the changes made.

> Here is where my problem arise. If a user is connected to a slave
> server and needs to make changes to de database, the writes will be
> passed to the master server. The master will make the corresponding
> changes and Slony-I will replicate this changes back to the slave
> who passed the writes (and all other slaves). All this steps adds a
> delay until the remote user can see the changes he/she made to the
> database reflected in his screen. For example if the user adds a new
> product to the database, he won't see it in the products list until?
> the replication takes place.  This kind of situations can make the
> system show wrong information, which is dangerous in some scenarios
> of my system.

> So I ask: Are there any solutions that may help me reduce/avoid this
> delay or at least prevent the slave server to respond to the user
> until the replication have propagated to all the slaves (or at least
> the one which originates the writes)? Maybe some kind of combination
> between asynchronous replication (provided by Slony-I) and
> synchronous replication?

> I hope I've explained this clear enough. Thanks for your time!

I think you have explained it clearly enough...

It doesn't seem to me that there is any possible resolution to your
problem.

1.  As an asynchronous system, Slony-I inherently separates updates on
the master system from when they are applied downstream.

You can't use the "slave" for reads and be certain that there aren't
updates pending.

In effect, for the problem you outline at the end, Slony-I isn't an
answer.

2.  You can't have the synchronization you require without going back
to reference "master" data on the "master" node.

This points towards the thought that what you want is quite likely to
be *unachievable*.

That may be unfortunate, but it's kind of useful to know, ahead of
time, what sorts of things are actually impossible.  It means you
don't need to waste time trying to solve them, only to be more or less
assured of failure...

It sounds as though your application really and truly needs to
reference "master" data, which means that if the master is offline,
your applications cannot get trustworthy results.

Let me point out the *troublesome* case...

- Suppose the connection goes down.

- Then, suppose someone at head office updates their system, promising
  inventory to someone, or removing a product from the list.  

  Update A.

- Suppose, during the network outage, someone at the satellite site
  promises that same inventory to someone, or promises that product that
  head office just removed.

  Update B.  Which is in conflict with Update A.

The only way to prevent these dueling updates from occuring is via
"sychronicity," where both are applied directly to the "head office"
system.  Unfortunately, that won't work here either, because the
connection to head office is down.

An asynchronous system is inherently vulnerable to these conflicting
updates, well, conflicting.

A synchronous system is inherently vulnerable to the connection going
down and preventing the remote site from doing business.

There isn't a third answer to give :-(.
-- 
output = reverse("ofni.sailifa.ac" "@" "enworbbc")
<http://dba2.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)