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.