duplicated rows in sqlremote

[Baron]

We are using SQLRemote for replicating several remote DBs against one cons. DB (Sql Anywhere 10). I am looking for a solution for a specific situation where more than one remote inserts the same line locally and then all those lines are replicated to Cons. and later back to all other remotes. In other words, I want to simulate the effect of (ON EXISTING UPDATE) for the lines inserted over DBRemote. I tried to apply a (before insert trigger), but this trigger affects only the locally inserted rows, and does not affect the rows inserted over DBremote! Thanks for any help in advance.

[VolkerBarth]

more than one remote inserts the same line locally and then all those lines are replicated to Cons

What does that mean technically? More than one remote does insert rows with the same PK, leading to a duplicate PK violation in the cons?

[VolkerBarth]

And please show us how the according statements look like done locally and when applied at the cons (via dbremote -v output).

[Baron]

Yes, several remotes insert rows with the same PK

[Baron]

I tried to add the ON EXISTING UPDATE clause in the local statement, but this clause is not included in transaction log file (and consequently not in dbremote).

Main statement in each DB locally: insert into table T1(order_id, client_name) ON EXISTING UPDATE values (1001, 'Client1')

Replicated Statement (-v output of dbremote): insert into table T1(order_id, client_name) values (1001, 'Client1')

[regdomaratzki]

That is expected behaviour.

[VolkerBarth]

Would this also be expected when the row is already existing in the local database? (I have never used ON EXISTING with replicated databases...)

[VolkerBarth]

And is that expected behaviour or do you try to fix a PK design error?

FWIW: We have had a similar situation with child tables in a 1:1 relationship to their parent tables: The child tables therefore use the parent table's PK as their own PK. In that design with the possibility that different remotes might insert a child row to a "currently child-less parent" resulting in several INSERTs for the same row reaching the cons. Here, we have used before insert triggers to delete the row if already existing, and additionally making sure that the "last insert" is bounced back to the remote it came from.

[VolkerBarth]

Just to add: The parents tables's PKs are defined as GLOBAL AUTOINCREMENT, so the particular handling described above is only effective in very rare cases...

[regdomaratzki]

When you use the ON EXISTING UPDATE clause of the INSERT statement, INSERTS are logged as INSERTS, and UPDATES are logged as UPDATES.

The following SQL :

create table t2 (pk integer primary key, c1 integer);
insert into t2 on existing update values (1,1);
insert into t2 on existing update values (1,2);
commit;

Results in the following entries in the transaction log :

--SQL-1014-0001067059
create table "DBA"."t2"(
  "pk" integer not null,
  "c1" integer null,
  primary key("pk"),
  )
go
--COMMIT-1014-0001067166-2019-01-07 09:58:08.787
COMMIT WORK
go
--INSERT-1014-0001067246
INSERT INTO DBA.t2(pk,c1)
VALUES (1,1)
go
--UPDATE-1014-0001067262
UPDATE DBA.t2
   SET c1=2
 WHERE pk=1
go
--COMMIT-1014-0001067279-2019-01-07 09:58:25.569
COMMIT WORK
go

Reg

[VolkerBarth]

Ah, thanks, I'm sure the same happens for MERGE statements (possibly also including DELETEs in the log)...

[regdomaratzki]

If your problem is separate remotes using the same primary key, there are a number of options to ensure primary key uniqueness across your replicating system. The two simplest options are using the DEFAULT GLOBAL AUTOINCREMENT default on your primary key column, or defining your primary keys as GUIDs. See http://dcx.sybase.com/index.html#sqla170/en/html/95f549cb6ea110148bccd9e49ca2a26a.html for more ideas if the simplest solutions will not work for you.

If the problem is about two remotes updating the values in the same row, the conflict can be resolved at the consolidated using a RESOLVE UPDATE trigger.

I tried to apply a (before insert trigger), but this trigger affects only the locally inserted rows, and does not affect the rows inserted over DBremote!

I don't understand this point. A before insert trigger will fire whether the row is inserted from your application or from dbremote. Also, a before insert trigger cannot change an insert into an update. It's possible to prevent the insert from happening by raising an error, but that will force a rollback of the entire transaction that includes the inserts, and not just the individual insert. Using triggers at either the remote or consolidated to maintain primary key uniqueness in any distributed system is a poor idea IMHO.

Reg

[Baron]

The problem is that separate remotes are using the same primary key. I tested a demo 'before insert trigger' that could change INSERT into UPDATE in that I delete the already existing row from within the trigger, and later the insert statement will insert the new row without PK conflict. The problem was that the trigger works only for local statements and not for statements over dbremote.

[regdomaratzki]

I don't understand what you mean when you say "the trigger works only for local statements and not for statements over dbremote".

If I create an incredibly simple trigger, it will fire regardless of whether dbremote is executing the insert or whether another application is executing the insert.

create trigger bi_admin before insert on admin
referencing new as nr
for each row
begin
  message nr.a_pkey;
end;

Do you mean that the trigger actions don't replicate? That is expected behaviour, unless you run with the -t switch on dbremote.

Reg