Re: Proposal for CSN based snapshots

Ants Aasma <ants(at)cybertec(dot)at> wrote:

> Serializable transactions
> -------------------------
>
> I won't pretend to be familiar with SSI code, but as far as I can
> tell serializable transactions don't need any modifications to
> work with the CSN based snapshot scheme. There actually already
> is a commit sequence number in the SSI code that could be
> replaced by the actual CSN.

That seems quite likely to work, and may be good for performance.

> IIRC one of the problems with serializable transactions on hot
> standby was that transaction visibility order on the standby is
> different from the master.

Pretty much.  Technically what SSI does is to ensure that every
serializable transaction's view of the data is consistent with some
serial (one-at-a time) exection of serializable transactions.  That
"apparent order of execution" does not always match commit order.
The problem is that without further work a hot standby query could
see a state which would not have been possible for it to see on the
master.  For example, a batch is closed but a transaction has not
yet committed which is part of that batch.  For an example, see:

http://wiki.postgresql.org/wiki/SSI#Read_Only_Transactions

As that example demonstrates, as long as no serializable
transaction *sees* the incomplete batch with knowledge (or at least
potential knowledge) of the batch being closed, the pending
transaction affecting the batch is allowed to commit.  If the
conflicting state is exposed by even a read-only query, the
transaction with the pending change to the batch is canceled.

A hot standby can't cancel the pending transaction -- at least not
without adding additional communications channels and latency.  The
ideas which have been bandied about have had to do with allowing
serializable transactions on a hot standby to use snapshots which
are known to be "safe" -- that is, they cannot expose any such
state.  It might be possible to identify known safe points in the
commit stream on the master and pass that information along in the
WAL stream.  The down side is that the hot standby would need to
either remember the last such safe snapshot or wait for the next
one, and while these usually come up fairly quickly in most
workloads, there is no actual bounding on how long that could take.

A nicer solution, if we can manage it, is to allow snapshots on the
hot standby which are not based exclusively on the commit order,
but use the apparent order of execution from the master.  It seems
non-trivial to get that right.

> If we use CSNs for visibility on the slave then we can actually
> provide identical visibility order.

As the above indicates, that's not really true without using
"apparent order of execution" instead of "commit order".  In the
absence of serializable transactions those are always the same (I
think), but to provide a way to allow serializable transactions on
the hot standby the order would need to be subject to rearrangement
based on read-write conflicts among transactions on the master, or
snapshots which could expose serialization anomalies would need to
be prohibited.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company