Re: [HACKERS] database replication

Hi Guys,

Now for one of my REALLY rare posts.
Having done a little bit of distributed data systems, I figured I'd
pitch in a couple cents worth.

> 2) The replication system will need to add at least one field to each
> table in each database that needs to be re plicated.  This
> field will be a date/time stamp which identifies the " last
> update" of the record.  This field will be called PGR_TIME
> for la ck of a better name.  Because this field will be used
> from within programs and triggers it can be longer so as to not
> mistake it for a user field.

I just started reading this thread, but I figured I'd throw in a couple
suggestions for distributed data control (a few idioms I've had to
deal with b4):
- Never use time (not reliable from system to system). Use
a version number of some sort that can stay consistent across
all replicas

This way, if a system's time is or goes out of wack, it doesn't
cause your database to disintegrate, and it's easier to track
conflicts (see below. If using time, the algorithm gets
nightmarish)

- On an insert, set to version 1

- On an update, version++

- On a delete, mark deleted, and add a delete stub somewhere for the
replicator process to deal with in sync'ing the databases.

- If two records have the same version but different data, there's
a conflict. A few choices:
1. Pick one as the correct one (yuck!! invisible data loss)
2. Store both copies, pick one as current, and alert
database owner of the conflict, so they can deal with
it "manually."
3. If possible, some conflicts can be merged. If a disjoint
set of fields were changed in each instance, these changes
may both be applied and the record merged. (Problem:
takes a lot more space. Requires a version number for
every field, or persistent storage of some old records.
However, this might help the "which fields changed" issue
you were talking about in #6)

- A unique id across all systems should exist (or something that
effectively simulates a unique id. Maybe a composition of the
originating oid (from the insert) and the originating database
(oid of the database's record?) might do it. Store this as
an extra field in every record.

(Two extra fieldss so far: 'unique id' and 'version')

I do like your approach: triggers and a separate process. (Maintainable!! :)

Anyway, just figured I'd throw in a few suggestions,
Duane