🚀 go-pugleaf

Daniel Alvarez wrote:
[snip]
> My question is if there is a solution or workaround to solve this with IB
> Replicator, and I wanted to know if SQL Server and Oracle replication works
> the same as Interbase one.
> 
> Are they smart enough to get rid of this problem?

The built-in oracle replication sure is able to do it, I'm making a 
manual one (so don't need to build oracle enterprise to do it) and I 
solved it in the following way:

1) first, I made all constraints deferrable, so with a single system 
switch I make them only enforceable at commit time
2) when copying, always replicate to the current time, no matter how 
many log entries and what entries you have, unless you have a way to do 
that (the only way I know is copying all the data to another table -> db 
slowdown). First, change session transaction level to serializable, so 
you don't get other users changes in midle way (consistency), and them 
get all the records affected looking at the log. If the record was 
deleted, I get only the key in the log. When applying, first delete, 
then insert/update. I always test if the record exists, update, 
otherwise, insert.

Solerman

It seems to me that you are doing this in the wrong order. I would insert into table 2 first, and then insert into table 1 with the appropriate FK value - it shouldn't allow you insert with a null PK value and then patch it up later.

If replication tries to add the table 1 entry first it will fail, however it will succeed with the table 2 entry. On the next replication the table 1 entry will replicate.

HTH

Mark





"Daniel Alvarez" <dalvarez1973@yahoo.com.ar> wrote:
>Question about replication issue
>
>
>
>Actually I'm using replication with Interbase/Firebird, I have a problem and
>I wanted to know if someone knows how to solve this, and if people from SQL
>Server and Oracle replication knows if this happens too with that engines (I
>hope not)
>
>
>
>I start describing a the tables for the example:
>
>
>
>/* Table: TABLE1 */
>
>
>
>CREATE TABLE TABLE1 (
>
>    ID INTEGER NOT NULL,
>
>    NAME VARCHAR (40) CHARACTER SET NONE COLLATE NONE,
>
>    FK_TABLE2 INTEGER);
>
>
>
>ALTER TABLE TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY (ID);
>
>
>
>ALTER TABLE TABLE1 ADD CONSTRAINT R_T2_T1 FOREIGN KEY (ID) REFERENCES TABLE2
>(ID);
>
>
>
>/* Table: TABLE2 */
>
>
>
>CREATE TABLE TABLE2 (
>
>    ID INTEGER NOT NULL,
>
>    DESCRIPTION VARCHAR (40) CHARACTER SET NONE COLLATE NONE);
>
>
>
>ALTER TABLE TABLE2 ADD CONSTRAINT PK_TABLE2 PRIMARY KEY (ID);
>
>
>
>That gives me two tables, one with a relation to the other with a foreign
>key.
>
>I'm replicating with a one-way schema, and the replication manager creates
>the needed triggers and tables in the master database. In one of that
>tables, the trigger saves information about the insert, update or delete
>occurred. The problem is that in the table (REPL_LOG), only stays a
>reference to the record inserted (for example), not the real data inserted.
>I give an example:
>
>
>
>I insert a record into TABLE1:
>
>
>
>ID = 1
>
>NAME = 'My Name'
>
>FK_TABLE2 = Null
>
>
>
>The trigger in TABLE1 saves in REPL_LOG = 'I' (Insert), 1 (the PK of the
>Table), TABLE1 (an internal ID of the TABLE1 inside the internal dictionary
>of the replication schema)
>
>
>
>Now, I insert a record into TABLE2:
>
>
>
>ID = 1
>
>DESCRIPTION = 'My Description'
>
>
>
>The trigger in TABLE2 save in REPL_LOG = 'I', 1, TABLE2
>
>
>
>Now, comes the problematic part. I update TABLE1, setting the FK to TABLE2.
>
>
>
>FK_TABLE2 = 1 (the PK value of the inserted record in TABLE2)
>
>
>
>This creates a record in REPL_LOG like this: 'U' (Update), 1, TABLE1
>
>
>
>Up to this, It's all OK with the master database. But the problem comes when
>It's time to replicate. Supposing that the replication does not occur
>between the prior changes to the database, and all of that comes after
>modifying the master database:
>
>
>
>The replication fires:
>
>
>
>The server finds all records of REPL_LOG in master database, so It first
>tries to insert a record in TABLE1, but, here's the problem: the replicator
>doesn't know what fields the real insert filled, and it tries to insert ALL
>fields (FK_TABLE2 too). So, the replicator server tries to insert a record
>like this:
>
>
>
>ID = 1 (OK)
>
>NAME = 'My Name' (OK)
>
>FK_TABLE2 = 1 (Ohhh, problem, FOREIGN KEY CONSTRAINT ERROR, It doesn't exist
>a record in TABLE2 with PK = 1), (Yet)
>
>
>
>So the replication fails...
>
>
>
>I can think that in a following step, I can be solved, but I find this
>behavior a BIG problem, because this can happen in a lot of situations.
>
>
>
>My question is if there is a solution or workaround to solve this with IB
>Replicator, and I wanted to know if SQL Server and Oracle replication works
>the same as Interbase one.
>
>Are they smart enough to get rid of this problem?
>
>
>
>Thank you very much.
>
>
>
>Daniel Alvarez
>
>IMS Soluciones Tecnol�gicas S.A.
>
>Buenos Aires - Argentina
>
>
>
>
>---
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.729 / Virus Database: 484 - Release Date: 27/07/2004
>
>