Re: cheaper snapshots

On Thu, Jul 28, 2011 at 11:54 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> (4)  We communicate acceptable snapshots to the replica to make the
> order of visibility visibility match the master even when that
> doesn't match the order that transactions returned from commit.

I wonder if some interpretation of 2 phase commit could make Robert's
original suggestion implement this.

On the master the commit sequence would look something like:
1. Insert commit record to the WAL
2. Wait for replication
3. Get a commit seq nr and mark XIDs visible
4. WAL log the seq nr
5. Return success to client

When replaying:
* When replaying commit record, do everything but make
the tx visible.
* When replaying the commit sequence number
if there is a gap between last visible commit and current:
insert the commit sequence nr. to list of waiting commits.
else:
mark current and all directly following waiting tx's visible

This would give consistent visibility order on master and slave. Robert
is right that this would undesirably increase WAL traffic. Delaying this
traffic would undesirably increase replay lag between master and slave.
But it seems to me that this could be an optional WAL level on top of
hot_standby that would only be enabled if consistent visibility on
slaves is desired.

--
Ants Aasma