Dimitry Sibiryakov wrote:

>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.
>
>

I know! :-)
But since I had already developped an application with a database
designed that way, it was too late to change.
Still, I agree with you on this point! I no longer allow this in my new
projects.

>>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?
>

That's right (sometimes). But I then immediately update the initial
database, so what's the problem?

>You must keep a table for translating
>PK values for detail tables and subsequent updates then. Do you?
>

Nope.

> 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
>

Yes.

>4. Update was replicated to DB2. Oops. Wrong record was updated.
>
>

No. This local update is done by the replicator, with the appropriate
user, so this update is never replicated.

Besides, if DB2 has a record with PK=8, then there is the risk that this
record will get replicated to DB1 before the other record with PK=8 in
DB1. This would mean a PK violation. Therefore, it's not a good idea to
have duplicate PK values at any time in any of the databases. So it's up
to the developper to see to it that this never happens.

So the simplest solution would be to use GUIDs. Since I didn't know
about this possibility, I simply use the local generator value, which I
then multiply by -1. In other tables, where the PK is not an integer
:-(, I use something like "LOCAL_NNNN", where NNNN is a generator value.

The problem you mention only happens in certain very particular
circumstances, which have to be avoided by a simple design decision
(using GUIDs, or some other such scheme), so as to never have the same
PK value in both databases.

In any case, if you use GUIDs (which seems to me to be the ideal
solution for a replication system), you don't even need to use the
feature I descibed above. Since the PK values will always be unique, you
don't need to try to synchronize them. However, this strategy can still
be useful for synchronzing other fields (even if they aren't a PK),
since the end user will often want to see something other than a GUID
for things such as, for example, invoices! :-)

>>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.
>

Quite.

>>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.
>

Indeed.

BTW, I didn't mean to criticise IBPReplicator (by your tone, you seem to
be taking it this way). I guess I was a bit provocative at first. Sorry
about that. I didn't mean to get into all this. My replicator was
tailored for my needs. Since I had a rather complex database, I had to
develop a few features that usually wouldn't be necessary. As a result,
I can now replicate the database I was working on, whereas I don't think
IBPReplicator could have done it. To be fair, I only ever tried a
prerelease version of the product, so I might be wrong, but I don't
think so. In any case, my customer wouldn't have been prepared to deal
with the licencing of IBReplicator (unless IBPReplicator is different, I
would be interrested to know).

Thanks for your reply,

Jonathan Neve.


[Non-text portions of this message have been removed]