Re: Transaction Snapshots and Hot Standby

On Thu, 2008-09-11 at 16:19 +0300, Heikki Linnakangas wrote:
> Well, yes, but you can fall behind indefinitely that way. Imagine that
> each transaction on the slave lasts, say 10 minutes, with a new
> transaction starting every 5 minutes. On the master, there's a table
> that's being vacuumed (or HOT-updated) frequently, say after each
> transaction for simplicity. What can happen is that every transaction
> that finishes on the slave will only let the WAL replay advance by one
> XID before blocking on the snapshot of the next slave transaction. The
> WAL replay will advance at a rate of 0.2 TPM, while the master is
> generating 1.0 TPM.

Aha, now I see where I was mistaken... I thought in terms of time and
not transaction IDs. So the time distance between the slave transactions
does not matter at all, only the distance in recovered XIDs matter for
the "blocking horizon"... and if the WAL recovery is blocked, the
"blocking horizon" is stalled as well, so the next transaction on the
slave will in fact require the same "blocking horizon" as all currently
running ones. Now I got it... and that means in fact that if you have
continuously overlapping small transactions, the "blocking horizon"
could be even blocked forever, as there'll always be a query running,
and the new queries will always have the snapshot of the currently
running ones because WAL recovery is stalled... or at least that's what
I understand from the whole thing...

Cheers,
Csaba.