On 31 Jan 2004 at 10:07, Jonathan Neve wrote:

>>>> Primary keys of replicated tables must be unique not only on
>>>>database level, but in all involved databases.
>>>>
>>>Funny, my replicator has none of these problems! :-) :-) :-)

From the text below I see that your replicator has much worse
problem: broken data consistency.

>> Oh, really? How do you manage the case when two different records
>>were inserted into two different databases with the same value of PK?
>>
>There are two different situations. Either the PK value is calculated
>automatically, as for example, with a generator (in which case something
>needs to be done in order to ensure that the same PK value is never
>used), or else the PK value is typed in directly by the end user (not a
>very good thing, I know), in which case it's up to the end user to make
>sure they don't do so.

The case when PK value is typed by user we may not consider. It is
higly disapproved method for PK generation.

>In the former case, you simply have to tell the replicator how each
>table has to be synchronized. If you use a generator, you need to tell
>the replicator which one you use, and then when it replicates, it will
>fetch the generator value from the server, and update it locally, before
>sending the record, thus avoiding all problems. If the PK value is
>calculated in any other way, you can define a stored procedure (or any
>other SQL statement), that will get executed on the server before
>sending the record, in order to get the correct PK value.

So, newly inserted record, having been replicated to another
database gets new value of PK? You must keep a table for translating
PK values for detail tables and subsequent updates then. Do you?
Otherwise I see a very bad problem with your replicator:

1. Record was inserted to table in DB1 with PK=8
2. It was replicated into DB2 with PK=200, but DB2 already has record
with PK=8
3. The record was updated in DB1
4. Update was replicated to DB2. Oops. Wrong record was updated.

>something they shouldn't do (ie, put twice the same PK value, or update
>the same record simultaneously), there isn't really anything we can do
>about it (how can we know that someone in another database performed a
>simultaneous update on the same record?); the replicator has no way of
>knowing that this situation isn't normal. What do you do in this sort of
>situation?

The same what you do: blame end user and say that table PK values
must be unique for all involved DB. Exactly as described at the top
of the letter.

> Have you found some way of telling that there has been a
>simultaneous update?

Timestamps is the only way I know. Very unreliable one, though.

SY, Dimitry Sibiryakov.