William Yu wrote:
Chris Travers wrote:
I guess I am thinking along different lines than you. I was thinking
that the simplest solution would be to have master/slave replication
for *approved* transactions only and no replication for initial
commits prior to approval. This makes the assumption that a single
transaction will be committed on a single server, and that a single
transaction will not be split over multiple servers. In this way,
you can commit a pending transaction to any single server, and when
it is approved, it gets replicated via the master. See below for more.
This works if you don't care that multiple servers commit transactions
that force a budget or bank account to be exceeded.
Ok. then lets look at possibility B. (Async Multimaster Replication is
out).
Thinking about this.... The big issue is that you only want to
replicate the deltas, not the entire account. I am still thinking
master/slave, but something where the deltas are replicated in the
background or where the user, in checking his account, is actually
querying the home server. This second issue could be done via dblink
or DBI-Link and would simply require that a master table linking the
accounts with home servers be replicated (this should, I think, be
fairly low-overhead).
Except what you know have is your system fails if any server fail or
is inaccessible.
Ok. If you have a system where each location is authoritative for its
customers and the server transparently queries that server where needed
(via a view). then when any server becomes inaccessible then the
customers whose accounts are on that server become inaccessible. This
may not be accessible. But if this is the case, then you could treat
this as a partitioned table, where each partition is authoritative on
one location (see a previous post on how to do this) and then use Slony
to replicate. Again this does not get around your objection above
namely that it is possible to do duplicate transactions at multiple
locations. For this you would need an explicit call to the
authoritative server. I see no other way around that. This might allow
people to view tentative balances from other branches if the home (or
its connection) server is down, but they would not be able to withdraw
funds.
But if you do this, you have a different problem. Namely that
replication will be occuring over your slower than desired links. As
the number of branches grow, so will the bandwidth demands on every
branch. This may not therefore be scalable.
Unfortunately there is no free lunch here. And I think that at some
point you are going to have to choose between:
1) Efficiency of network throughput
2) Tolerance to attempts at repeat transactions before replication
(emptying an account multiple times)
3) Availability of a transaction.
You can pick any two. I think that this is the way it will work with
any other database system as well.
Best Wishes,
Chris Travers
Metatron Technology Consulting
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
