Re: Replication Using Triggers

Hello,

On Fri, 18 Jan 2008 17:37:07 +0000 (GMT) gordan(at)bobich(dot)net wrote:

> >>>> This is what I have in mind:
> >>>>
> >>>> Have a plperl function that creates connections to all servers in the
> >>>> cluster (replication partners), and issues the supplied write query to
> >>>> them, possibly with a tag of some sort to indicated it is a replicated
> >>>> query (to prevent circular replication).
> >>>>
> >>>> Have a post execute trigger that calls the above replication function if
> >>>> the query was issued directly (as opposed to replicated), and passes it
> >>>> the query it just executed if it was successful.
> >
> > Not sure here if you mean literally the SQL query that was executed - in
> > which case you have all sorts of problems with sequences and functions
> > returning different values.
>
> Indeed, but sequences at least can be worked around. Post-execute,
> sequence number used should be available already, and the sequence offset
> and increment can be set so as to ensure they do not clash. That's what
> MySQL does (and I must apologize for making the comparison all the time).

Sequences are only one (small) problem. What about functions returning
different results (volatile) for each call? Just imagine random() or
now(). What about inserts or updates selecting parts of table data? You
can't be sure to get exactly the same results on the slave.

> > But there are plenty of solutions that do a lot better than this. Slony-I is
> > the most polular. My favourite is a spin on the old db_mirror that used to be
> > part of the Postgres distribution.
>
> How would Slony be better? It doesn't seem to support master-master
> replication for one.

But it work's. At least master-slave.

> The problem with this is that the ordering becomes inconsistent with
> multiple masters. That would be a bit too inconsistent for my liking. As
> far as I can tell, that is also why MySQL's current replication method is
> unsuitable for more than ring-replication. Having said that, ring suffers
> from similar race conditions, it's more of a hack than a solution.

A lot books are written about this topic, many details researched and
still master-master replication i a very complicated topic. You should
start reading about vector and Lamport clocks. This are the (very)
basic (among others) for your sync problem.

> Now that I think about it, I'm not actually sure that waiting for global
> success before final commit would make update/delete without race
> condition as they won't fail, but can still yield inconsistencies due to
> race conditions. Still, I think it's worth having despite this issue.

Sure, you are right. You now are about to find out what other ppl are
researching the last *uhm* 20 years ;-)

> > You seem to be re-inventing the wheel, and the re-invention is not quite as
> > round as the existing wheel :-)
>
> Not quite - I think multi-master capability is important.

Yes. But master-master or even multi-master is a very complicated topic.
So let's start with something more easy, like master-slave. This works,
you don't have race conditions and that's a good starting point for
more research.

> I presume you mean that you cannot attach triggers to schema changes. Yes,
> I had thought of that a minute ago. I don't suppose this could be deemed a
> feature request for CREATE/ALTER/DROP schema level triggers? ;)

More triggers, not only for ddl changes, would be nice anyway.
I see fields of application for some of my own projects ;-)

Kind regards

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group