Re: Postgresql replication - Mailing list pgsql-general

chris@verkiel.metatrontech.com (Chris Travers) writes:
> William Yu wrote:
>> This system sounds ok for documents and general data that can
>> always be revived via version control/history. But I can't see how
>> this would work for financial transactions where you're dealing
>> with money and bank accounts. Suppose I have $100 in my account. I
>> decided to login to multiple servers and wire transfer $100 to
>> another account on every server. And I hit submit exactly at the
>> same time for every server so check. Sure they can resolve the
>> conflict afterwards in terms of saying in terms of which transfer
>> to kill off. But the fact is that my other account has that N x
>> $100 already and I've just fleeced the bank.
>>
> Hmmm.......
>
> I think you should realize by now that no multimaster async
> replication solution is going to adequately work for something like
> these financial transactions.  You need either synchronous or
> single-master simply because you need to have a concept that there
> is a *single* authoritative source of the current status and balance
> on the account.  You can't really expect async-replicated servers to
> know about transactions that haven't been replicated yet can you?

Actually, I disagree.  There _is_ a way to cope with that issue.

The methodology involves not having a Balance Column anywhere in the
database, where activity *always* involves recording the "deltas."

If the DEBIT and the CREDIT for each financial transaction are always
committed, in any given place, as part of a single transaction, then
there is no reason for the system to fall out of balance.

This doesn't prevent the scenario of someone logging into many servers
simultaneously and emptying their account multiple times.  But there
are other ways of addressing that problem.

This sort of scenario is almost certainly the case for the
stupendously enormous quantities of billing transactions that result
from long distance and cellular activity in the telco industry.

I'm pretty sure that they _don't_ track balance updates for each
transaction that applies to a customer's account.  You could, via one
form of trickery or another, "overdraw" your account by a fairly hefty
amount, and they probably won't notice for a day or even three.  But
once they notice/estimate that the Gentle Caller has built up some
dangerously high balance, they'll warn of impending discontinuation of
service if some sum isn't paid by some date.

This sort of analysis does not require that any location is considered
"authoritative" for the balance of the account.  It suffices to have
some sort of "transaction cutoff," that the analysis is sure to
include all transactions up to *yesterday* at midnight UTC.

Some "bad things" might take place during the up-to-24h lag; the
assumption is that that won't be material, or that you can do other
sorts of traffic analysis to warn of impending problem accounts...
--
output = ("cbbrowne" "@" "ntlug.org")
http://www.ntlug.org/~cbbrowne/sap.html
"... The book [CLtL1] is about  400 pages of 8.5" by 11" Dover output.
Apparently  the publisher and  typesetter decided  that this  made the
lines too wide for  easy reading, so they will use a  6" by 9" format.
This  will make  the shape  of the  book approximately  cubical.  Now,
there are  26 chapters counting the  index, and a Rubik's  cube has 26
exterior cubies.  I'll let  you individually extrapolate and fantasize
from there."  -- GLS