Re: Proposal: Commit timestamp - Mailing list pgsql-hackers

On 2/7/2007 9:27 PM, Markus Schiltknecht wrote:
> Hi,
> 
> Jan Wieck wrote:
>> Then let me give you a little puzzle just for the fun of it.
>> 
>> A database containing customer contact information (among other things) 
>> is a two node multimaster system. One is serving the customer web 
>> portal, the other is used by the company staff including the call 
>> center. At 13:45 the two servers lose connectivity to each other, yet 
>> the internal staff can access the internal server while the web portal 
>> is accessible from the outside. At 13:50 customer A updates their credit 
>> card information through the web portal, while customer B does the same 
>> through the call center. At 13:55 both customers change their mind to 
>> use yet another credit card, now customer A phones the call center while 
>> customer B does it via the internet.
> 
> Phew, a mind twister... one customer would already be enough to trigger 
> that sort of conflict...
> 
>> At 14:00 the two servers reconnect and go through the conflict 
>> resolution. How do you intend to solve both conflicts without using any 
>> "clock", because that seems to be a stopword causing instant rejection 
>> of whatever you propose. Needless to say, both customers will be 
>> dissatisfied if you charge the "wrong" credit card during your next 
>> billing cycle.
> 
> Correct. But do these cases satisfy storing timestamps to each and every 
> transaction you do? That's what I doubt, not the usefulness of time 
> based conflict resolution for certain cases.
> 
> You can always add a time based conflict resolution, by adding a 
> timestamp column and decide upon that one. I'd guess that the overall 
> costs are lower that way.

Yes, yes, and yes ... but aside from the problem that you use the very 
ambiguous word "timestamp" (which somehow suggests using a "clock" of 
some sort), isn't the "begin" timestamp of a long running transaction 
worse than the "commit" timestamp, when all its work got visible to the 
outside world instantaneously?

> 
> But you've withdrawn that proposal already, so...
> 
>> Which is a good discussion because one of the reasons why I stopped 
>> looking into Postgres-R is the fact that is based on the idea to push 
>> all the replication information through a system that generates a global 
>> serialized message queue. That by itself isn't the problem, but the fact 
>> that implementing a global serialized message queue has serious 
>> throughput issues that are (among other details) linked to the speed of 
>> light.
> 
> Agreed. Nevertheless, there are use cases for such systems, because they 
> put less limitations to the application. One could even argue, that your 
> above example would be one ;-)

Now we're in sync :-)

> 
>> I am trying to start with a system, that doesn't rely on such a 
>> mechanism for everything. I do intend to add an option later, that 
>> allows to declare a UNIQUE NOT NULL constraint to be synchronous. What 
>> that means is, that any INSERT, UPDATE, DELETE and SELECT FOR UPDATE 
>> will require the node to currently be a member of the (quorum or 
>> priority defined) majority of the cluster.
> 
> Sounds reasonable.
> 
>> An advisory lock system, 
>> based on a total order group communication, will grant the lock to the 
>> unique key values on a first come, first serve base. Every node in the 
>> cluster will keep those keys as "locked" until the asynchronous 
>> replication stream reports the locking transaction as ended. If another 
>> remote transaction in the meantime requires updating such key, the 
>> incoming stream from that node will be on hold until the lock is 
>> cleared. This is to protect agains node B replicating a transaction from 
>> node A and a later update on node B arrives on C before C got the first 
>> event from A. A node that got disconnected from the cluster must rebuild 
>> the current advisory lock list upon reconnecting to the cluster.
> 
> Yeah, this is a convenient way to replicate sequences via a GCS.
> 
>> I think that this will be a way to overcome Postgres-R's communication 
>> bottleneck, as well as allowing limited update activity even during a 
>> completely disconnected state of a node. Synchronous or group 
>> communication messages are reduced to the cases, where the application 
>> cannot be implemented in a conflict free way, like allocating a natural 
>> primary key. There is absolutely no need to synchronize for example 
>> creating a sales order. 
> 
> Agreed, such cases can easily be optimized. But you have to be aware of 
> he limitations these optimizations cause. Postgres-R is much more 
> targeted at very general use cases.

I am, if for no other reason than that I am familiar with the concepts 
underneath Postgres-R for more than 3 years. What I realized is that the  "general use" case (for arbitrary complex
applications)is very likely 
 
to be in conflict with any king of "good default performance" case.

> 
>> An application can use global unique ID's for 
>> the order number. And everything possibly referenced by an order (items, 
>> customers, ...) is stored in a way that the references are never 
>> updated. Deletes to those possibly referenced objects are implemented in 
>> a two step process, where they are first marked obsolete, and later on 
>> things that have been marked obsolete for X long are deleted. A REPLICA 
>> TRIGGER on inserting an order will simply reset the obsolete flag of 
>> referenced objects. If a node is disconnected longer than X, you have a 
>> problem - hunt down the guy who defined X.
> 
> Yeah, that's another very nice optimization. Again, as long as you know 
> the limitations, that's all well and fine.
> 
>> Merging certain ideas to come up with an async/sync hybrid? Seems to me 
>> we have similar enough ideas to need conflict resolution, because we had 
>> them simultaneously but communicate them asynchronously.
> 
> Huh? Sorry, I didn't get what you're trying to say here.

Out of sync again ... we'll get there tomorrow ... unless your clock is 
way back and tomorrow will never come.


Jan


-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #