[Modeling-users] Future directions: replication and auto-increments

[Ernesto Revilla]

Hi,

when talking with some Python people, I heard about future requirements
about multi-master replication. This is what you need if you have data
split over several offices and you have no 100% uptime links.
Multi-master replication is available in mysql, but Postgresql still is
lacking this feature. I think Oracle as it also.

The problems you can have with multi-master replication are the update
collisions. But these may not be very usual in real scenarios, depending
how data is organized.

The first problem of all is that we can't use auto-increment (serial)
fields. So we have to use our own function to produce this. For this to
work, the original autoincremen field is defined as large integer,
something like 64 bits, where a part is reserved as a local counter and
the other part as station identifier which created the object, e.g. 16
bit station identifier + 48 bit counter. There are other, more
complicated, algorithms, like those which create a global unique id
(GUID) bases on the Ethernet address and time, and distributed
scenarios, where there is a process which gives new hi-numbers to
stations (which in turn can fragment the bits used, say 16 into 8+8 and
give their own low part hi number to other stations, producing a
hierarchical system).

Concluding, IDs should not be created by database systems, but by the
middleware in a nuclear operation.

With best regards, Erny

[Sebastien Bigaret]

Hi Erny,

This particular point can be easily solevd if you could plug-in your
own algorithm to generate PK values --then they would be generated the
appropriate way. Are you going this way and would you test it if it is
implemented?

-- Sébastien.




PS: Oh, BTW, TemporaryGlobalIDs are identified by host, ip and time :)

Post by Ernesto Revilla
Hi,
when talking with some Python people, I heard about future requirements
about multi-master replication. This is what you need if you have data split
over several offices and you have no 100% uptime links. Multi-master
replication is available in mysql, but Postgresql still is lacking this
feature. I think Oracle as it also.
The problems you can have with multi-master replication are the update
collisions. But these may not be very usual in real scenarios, depending how
data is organized.
The first problem of all is that we can't use auto-increment (serial)
fields. So we have to use our own function to produce this. For this to
work, the original autoincremen field is defined as large integer, something
like 64 bits, where a part is reserved as a local counter and the other part
as station identifier which created the object, e.g. 16 bit station
identifier + 48 bit counter. There are other, more complicated, algorithms,
like those which create a global unique id (GUID) bases on the Ethernet
address and time, and distributed scenarios, where there is a process which
gives new hi-numbers to stations (which in turn can fragment the bits used,
say 16 into 8+8 and give their own low part hi number to other stations,
producing a hierarchical system).
Concluding, IDs should not be created by database systems, but by the
middleware in a nuclear operation.
With best regards, Erny