Re: Batch replication ordering (was Re: [GENERAL] 32/64-bit transaction IDs?)

If the items going to the slave are ordered and complete transactions,
does it matter whether or not they are applied as complete transactions
on the slave in groups or one at a time?

It is clear that incomplete or partial transactions should not be queued.

What I'm not sure about is the overlapping transactions where the
second completes before the first.

begin (xact 1)
select ... for update ...
begin (xact 2)
update ...
end (xact 2)

end (xact 1)

In this case, on the slave, xact2 will be commited before xact 1 is begun.
This seems OK to me, but I'd like some confirmation to understand this.
Or is this the wrong question?

Thanks,
elein(at)varlena(dot)com

On Thursday 10 April 2003 19:03, Ed L. wrote:
> On Thursday April 10 2003 5:26, Tom Lane wrote:
> > "Ed L." <pgsql(at)bluepolka(dot)net> writes:
> > > I don't think so. Can you imagine a replication queue big enough to
> > > that someone might not want to process it entirely in one transaction?
> >
> > No, I can't. The bigger the queue is, the further behind you are, and
> > the more you need to catch up; twiddling your thumbs for awhile gets
> > progressively less attractive.
>
> Well, if an arbitrarily large queue of transactions doesn't persuade you,
> you are not going to be persuaded. If (a) the catch-up rate is fast
> enough, (b) the master load periodically ebbs enough, as it does at various
> predictably slow times, (c) you care about lessening traffic between master
> and slave, and/or (d) you care about lessening constancy of the replication
> load on the master, having the replicator periodically back-off is very
> attractive.
>
> > Also, AFAIR from prior discussion, the *slave* side doesn't need to
> > commit the whole batch in one transaction. I can't recall if this
> > could expose transaction intermediate states on the slave, but if
> > you're that far behind you'd best not be having any live clients
> > querying the slave anyway.
>
> Exposing intermediate transaction states is precisely the issue and the
> reason for my original question. Your apparent presumption of the lack of
> value of querying a slave that's running significantly behind is a false
> blanket assumption. Of course it depends on the situation and the nature
> of the data. I can think of a number of past instances where some
> considerable lagtime in the data propagation was just fine, but
> inconsistency was not. If you aren't replicating to the slave and
> committing in one big all-inclusive batch, then there needs to be some care
> to commit in transaction units if you don't want to offer room for
> inconsistent views to slave clients. We left the conversation a while back
> thinking there was no need for anything other than a serial replay by
> insertion order, but a batching requirement seems to change that, which is
> why I posed the original question.
>
> > In any case you can throttle the load by sleeping between selects while
> > holding the transaction open. I think your concern is predicated on
> > Oracle-ish assumptions about the cost of holding open a transaction.
> > The only such cost in PG is that it interferes with VACUUM cleaning
> > out old tuples --- which I'm not sure VACUUM could do anyway for stuff
> > that still hasn't propagated to a slave.
>
> I'd have thought there might be other problems resulting from holding a
> transaction open for hours, like the progress you lose if the master or
> slave or the connection between goes down before the mondo commit. Maybe
> that kind of thing just never happens.
>
> Ed
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
----------------------------------------------------------------------------------------
elein(at)varlena(dot)com Database Consulting www.varlena.com
I have always depended on the [QA] of strangers.