Idea to a replication solution (warning: long, boring, OT? and possibly stupid) - Mailing list pgsql-general

Hi,

I started thinking of a replication solution that would not need too be hard
to code, and would work on a slow connection. What I thought of was a table:

"REPLICATE"

With following fields:

"TableName" "OID_here" "OID_there" "RemovedHere" "IsNew"

That table must be on both the "server" and the "remote" database.

Before I start to replicate I update this table with (locally on both
computers):

UPDATE REPLICATE SET RemovedHere = 1 WHERE TableName = 'myTable' AND
OID_here NOT IN (SELECT OID FROM myTable);

Then I remove removed rows from both computers (looping until all rows are
checked):

bool bRemovedThere = (SELECT OID_there FROM REPLICATE WHERE TableName =
'myTable' AND RemovedHere = 1)?true:false;
    (executed on the "remote")
bool bRemovedHere  = (SELECT OID_there FROM REPLICATE WHERE TableName =
'myTable' AND RemovedHere = 1)?true:false;
    (executed on the "server")

if( bRemovedThere && !bRemovedHere )
{
    [remove here ("server"), because its removed from there ("remote")]
    [remove this row from the local REPLICATE table]
    [remove this row from the "remote" REPLICATE table]
}
if( bRemovedHere && !bRemovedThere )
{
    [remove from the "remote", because its removed from here ("server")]
    [remove this row from the local REPLICATE table]
    [remove this row from the "remote" REPLICATE table]
}

Now all removed rows should be removed on both computers, and only some boolean values were transfered.

DELETE FROM REPLICATE WHERE RemovedHere = 1 AND TableName = 'myTable'
Should not even delete one row (maybe we should check for this?)

Now its time to add all new values to the REPLICATE table, do it something like this:

SELECT OID FROM myTable WHERE OID NOT IN (SELECT OID_here FROM REPLICATE WHERE TableName = 'myTable')
for every row found:
{
    First try to create this row on the "remote" computer and return the newly created OID as OIDthere (new = true).
    On failure (collision) return OIDthere of the existing row (set new = false).
    If no such row, abort replication (permission error?).

    INSERT INTO REPLICATE
        (TableName, OID_here, OID_there, RemovedHere, IsNew)
        VALUES ('myTable', 'OID', 'OIDthere', 0, new )
}

When done on the "server" do the same thing on the "remote". Now we should have all newly created rows on both (with
somecollisions, which we still have a change to solve). We had to send complete rows, but thats unavoidable. 

Then its time to solve UPDATEd rows.

SELECT xtime, OID FROM myTable WHERE OID NOT IN (SELECT OID_here FROM REPLICATE WHERE TableName = 'myTable' AND IsNew =
0)
Before sending this row as an UPDATE .. WHERE OID = OID_there to the "remote", check the remote rows xtime and only
UPDATEif yours are later. And then do it the other way around. Again we had to send complete rows, but thats
unavoidable.

Then we only have som cleaning to do on both sides:
UPDATE REPLICATE SET IsNew = 0 WHERE IsNew = 1 AND TableName = 'myTable'

I think this would work and would be quite effective (and not too error prone). Because if it fails, it will try again
nexttime. *I think* this would be as secure as a transaction (log) replication, but of course a log replication might
befaster. 

And it would be easy to serve several "remote" computers, just ad an id to the REPLICATE table, and your done.

What do you think?

// Jarmo