Re: [HACKERS] database replication - Mailing list pgsql-hackers

Ryan Kirkpatrick wrote:
> 
> On Sun, 26 Dec 1999, Damond Walker wrote:
> 
> > >     How about a single, seperate table with the fields of 'database',
> > >'tablename', 'oid', 'last_changed', that would store the same data as your
> > >PGR_TIME field. It would be seperated from the actually data tables, and
> ...
> >     The problem with OID's is that they are unique at the local level but if
> > you try and use them between servers you can run into overlap.

The same is unfortunately true of any local primary key in a replicated
system.

Mariposa solved this by making the oid 8-byte, of which first four are the
site id 
and remaining four are the oid as we have it now.

This has the added benefit of being able to determine which site created the
record.

> >     Oops...how about defining a trigger for this?  With deletion I guess we
> > would have to move a flag into another table saying we deleted record 'X'
> > with this primary key from this table.
> 
>         Or, according to my logic below, if a row is missing on one side
> or the other, then just compare the remaining row's timestamp to the last
> synchronization time (stored in a seperate table/db elsewhere). The
> results of the comparsion and the state of row existences tell one if the
> row was inserted or deleted since the last sync, and what should be done
> to perform the sync.

It's very difficult to find a _missing_ row quickly. It will allways be
somewhat 
expensive.

Perhaps the easiest way would be to re-introduce time-travel. then a deleted
row
would just be an ordinary row with its valid_to timestamp set to past.

probably a set of (valid_from,valid_to,site_id,local_row_id) would be
sufficient to 
pinpoint the record both in time and space.

Being able to do it would require some improvement in postgres inheritance.
At least rules, triggers, indexes, constraints and defaults should be
inheriteable.

>         No, provided that we don't do the identification number idea
> (which the more I think about it, probably will not work). As for what
> version support plpgsql, I don't know, one of the more hard-core pgsql
> hackers can probably tell us that.

Ask Jan Wiek, he did it :)

> >     The only thing we'd need for Python is the Python extensions for
> > PostgreSQL...which in turn requires libpq and that's about it.  So, it
> > should be able to run on any platform supported by Python and libpq.
> 
>         Of course. If it ran on NT as well as Linux/Unix, that would be
> even better. :)

NT kas both Python and libpq but unfortunately no PyGreSQL. If someone takes 
the time to make it compile it would be appreciated :)

If you feel you like pure python protocol hacking, I can dig up my python 
module that was able to do simple queries for version 6.2
------------------
Hannu