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