Re: SSI and Hot Standby

On Fri, 2011-01-21 at 18:52 -0600, Kevin Grittner wrote:
> My assumption is that when we have a safe snapshot (which should be
> pretty close to all the time), we immediately provide it to any
> serializable transaction requesting a snapshot, except it seems to
> make sense to use the new DEFERRABLE mode to mean that you want to
> use the *next* one to arrive.

How would it handle this situation:
1. Standby has safe snapshot S1
2. Primary does a VACUUM which removes some stuff visible in S1
3. Standby can't replay the VACUUM because it still has S1, but also
can't get a new S2 because the WAL needed for that is behind the VACUUM

So, S1 needs to be discarded. What do we do on the standby while there
is no safe snapshot? I suppose throw errors -- I can't think of anything
else.

> This would effectively cause the point in time which was visible to
> serializable transactions to lag behind what is visible to other
> transactions by a variable amount, but would ensure that a
> serializable transaction couldn't see any serialization anomalies.
> It would also be immune to serialization failures from SSI logic;
> but obviously, standby-related cancellations would be in play. I
> don't know whether the older snapshots would tend to increase the
> standby-related cancellations, but it wouldn't surprise me.

I'm also a little concerned about the user-understandability here. Is it
possible to make the following guarantees in this approach:

1. If transactions are completing on the primary, new snapshots will be
taken on the standby; and
2. If no write transactions are in progress on the primary, then the
standby will get a snapshot that represents the exact same data as on
the primary?

That would be fairly easy to explain to users. If there is a visibility
lag, then we just say "finish the write transactions, and progress will
be made". And if the system is idle, they should see identical data.

Regards,
Jeff Davis