Re: Batch replication ordering (was Re: [GENERAL] 32/64-bit

On Fri, 11 Apr 2003, Ed L. wrote:

> For those of you who do see the validity in batching multiple transactions,
> my question is restated here:
>
> My question: Is there an ordering algorithm that would make a consistent
> but limited batchsize replication possible? I propose one below.
>
> Suppose you have a replication queue on the master, with dbmirror-like
> trigger-based insertions, that looks something like this:
>
> create table replication_queue (
> xid integer,
> seqid serial primary key,
> data...
> );
>
> Here, 'xid' is the transaction ID, 'seqid' is the queue insertion order, and
> 'data' has all the info needed to replicate the update. Every row
> update/delete/inserte results in a row in this queue, and a transaction may
> consist of one to many rows in the queue.

> The algorithm I'm considering right now is the following:
>
> select xid, max(seqid) as "max_seqid"
> into temp replication_order
> from replication_queue
> group by xid
> order by max(seqid)
> limit N;
>
> Then, to get the actual queue replication order,
>
> select q.xid, q.seqid, q.data
> from replication_queue q, replication_order o
> where q.xid = o.xid
> order by o.max_seqid, q.seqid;
>
> [This is a batched variation of dbmirror's original algorithm.]
>
> So, replication is done by transaction groupings, in ascending order
> according to the maximum seqid in each transaction. I'm hoping someone can
> poke holes in this algorithm if they exist.

Does it matter if transactions that do not affect each other are committed
on the slave in a different order than they were on the master? I don't
think that's guaranteed by the above (unless the inserts into
replication_queue were deferred to transaction end), but I'm also
uncertain if it's a constraint you're concerned with.