LISTEN/NOTIFY for lightweight replication

От:

Ted Shab <tshab@yahoo.com>

Дата:

12 октября 2004 г. в 23:49:10

Hi,

I'm trying to come up with a relatively simple
multi-master replication solution.  This is for
multiple databases that need to be discreet, and
change relatively infrequently (10-30 updates an
hour), and almost never update each others data (less
than once a day).  

The TCL-based replication project for multi-master is
troublesome to configure and seems to really impact
performance.  It can be assumed that the master-slave
setup will not work for me, nor do we want to purchase
a commercial soluton, nor can we run this all from one
central database.

I'm considering the following, and am requesting
advice and any suggestions:

a.  Use listen/notify to develop a notification when
changes are made.  This could be done between each
node, or not (i.e. it could be a chain instead).

b1.  All of the add events are using sequences so each
node has a unique set of new records. 

b2.  When an add, update or delete is recorded, DDL of
this is passed via the notify.

c.  If no local event happened prior during this event
envelope (i.e. since the last update notification but
before the new event completed), perform the event.

d.  If there is a record level conflict but no field
level one, perform the event.

e.  If there is a field level conflict, raise an
exception (TBD).

There are plenty of things that might not work here,
but I'm particularly interested in:

1.  If this is brain-dead because of performance
issues, I'd like to know upfront.  

2.  Is there a way to get at the system tables that
would contain overall change events? Otherwise, is
this information available in some log event?

3.  Can this be readily translated to DDL?

4.  Does anyone have some extended examples of using
listen/notify, especially in any kind of distributed
transaction capability?

Thanks!




		
__________________________________
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail

Re: LISTEN/NOTIFY for lightweight replication

От:

Ted Shab <tshab@yahoo.com>

Дата:

13 октября 2004 г. в 16:32:19

Richard,

Thanks for the response.

I'll look into both the dblink and iirc.  

Do you know of any extended examples of either?

--Ted
--- Richard Huxton  wrote:

> Ted Shab wrote:
> > Hi,
> > 
> > I'm trying to come up with a relatively simple
> > multi-master replication solution.  This is for
> > multiple databases that need to be discreet, and
> > change relatively infrequently (10-30 updates an
> > hour), and almost never update each others data
> (less
> > than once a day).  
> > 
> > The TCL-based replication project for multi-master
> is
> > troublesome to configure and seems to really
> impact
> > performance.  It can be assumed that the
> master-slave
> > setup will not work for me, nor do we want to
> purchase
> > a commercial soluton, nor can we run this all from
> one
> > central database.
> 
> > e.  If there is a field level conflict, raise an
> > exception (TBD).
> 
> Exception handling and failure recovery are what
> makes for all the work 
> in replication.
> 
> I don't think a pure listen/notify setup will be
> enough because iirc the 
> system doesn't guarantee delivery of multiple
> notifications if >1 are 
> queued.
> 
> Have you looked into the possibility of using dblink
> to handle updates 
> of each others' data? That would mean your problem
> reverting to one of 
> single-master replication.
> 
> -- 
>    Richard Huxton
>    Archonet Ltd
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose
> an index scan if your
>       joining column's datatypes do not match
> 



		
_______________________________
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com

Re: LISTEN/NOTIFY for lightweight replication

От:

Alvaro Herrera <alvherre@dcc.uchile.cl>

Дата:

13 октября 2004 г. в 16:53:19

On Wed, Oct 13, 2004 at 08:32:04AM -0700, Ted Shab wrote:

> Thanks for the response.
> 
> I'll look into both the dblink and iirc.  

That's actually only dblink.  IIRC is an acronym, meaning "if I recall
correctly", IIRC.

-- 
Alvaro Herrera ()
"The West won the world not by the superiority of its ideas or values
or religion but rather by its superiority in applying organized violence.
Westerners often forget this fact, non-Westerners never do."
(Samuel P. Huntington)

Re: LISTEN/NOTIFY for lightweight replication

От:

Ted Shab <tshab@yahoo.com>

Дата:

13 октября 2004 г. в 16:56:36

Thanks.  I was thinking iirc was the transport
protocol :-)

Looks like dblink is the best bet here.

--Ted
--- Richard Huxton  wrote:

> Ted Shab wrote:
> > Richard,
> > 
> > Thanks for the response.
> > 
> > I'll look into both the dblink and iirc.  
> > 
> > Do you know of any extended examples of either?
> 
> dblink is in the contrib/ folder of the source
> distribution and possibly 
> your packaged version if you use such a thing. Never
> needed it myself, 
> but the documentation looks clear enough.
> 
> As for listen/notify possibly dropping duplicate
> notifications... Ah! 
> it's in the "SQL COMMANDS" reference part of the
> manuals
> 
> NOTIFY behaves like Unix signals in one important
> respect: if the same 
> condition name is signaled multiple times in quick
> succession, 
> recipients may get only one notify event for several
> executions of 
> NOTIFY. So it is a bad idea to depend on the number
> of notifies 
> received. Instead, use NOTIFY to wake up
> applications that need to pay 
> attention to something, and use a database object
> (such as a sequence) 
> to keep track of what happened or how many times it
> happened.
> 
> --
>    Richard Huxton
>    Archonet Ltd
> 



		
_______________________________
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com

Re: LISTEN/NOTIFY for lightweight replication

От:

Richard Huxton <dev@archonet.com>

Дата:

13 октября 2004 г. в 09:27:35

Ted Shab wrote:
> Hi,
> 
> I'm trying to come up with a relatively simple
> multi-master replication solution.  This is for
> multiple databases that need to be discreet, and
> change relatively infrequently (10-30 updates an
> hour), and almost never update each others data (less
> than once a day).  
> 
> The TCL-based replication project for multi-master is
> troublesome to configure and seems to really impact
> performance.  It can be assumed that the master-slave
> setup will not work for me, nor do we want to purchase
> a commercial soluton, nor can we run this all from one
> central database.

> e.  If there is a field level conflict, raise an
> exception (TBD).

Exception handling and failure recovery are what makes for all the work 
in replication.

I don't think a pure listen/notify setup will be enough because iirc the 
system doesn't guarantee delivery of multiple notifications if >1 are 
queued.

Have you looked into the possibility of using dblink to handle updates 
of each others' data? That would mean your problem reverting to one of 
single-master replication.

-- 
   Richard Huxton
   Archonet Ltd

Re: LISTEN/NOTIFY for lightweight replication

От:

Richard Huxton <dev@archonet.com>

Дата:

13 октября 2004 г. в 16:53:25

Ted Shab wrote:
> Richard,
> 
> Thanks for the response.
> 
> I'll look into both the dblink and iirc.  
> 
> Do you know of any extended examples of either?

dblink is in the contrib/ folder of the source distribution and possibly 
your packaged version if you use such a thing. Never needed it myself, 
but the documentation looks clear enough.

As for listen/notify possibly dropping duplicate notifications... Ah! 
it's in the "SQL COMMANDS" reference part of the manuals

NOTIFY behaves like Unix signals in one important respect: if the same 
condition name is signaled multiple times in quick succession, 
recipients may get only one notify event for several executions of 
NOTIFY. So it is a bad idea to depend on the number of notifies 
received. Instead, use NOTIFY to wake up applications that need to pay 
attention to something, and use a database object (such as a sequence) 
to keep track of what happened or how many times it happened.

--
   Richard Huxton
   Archonet Ltd