On 27 February 2016 at 17:54, Kevin Grittner <kgrittn@gmail.com> wrote:
On a single database SSI can see whether a read has caused such a problem. If you replicate the transactions to somewhere else and read them SSI cannot tell whether there is an anomaly
OK, I thought you were saying something else. What you're saying is that SSI doesn't work on replicas, yet, whether that is physical or logical.
Row level locking (S2PL) can be used on logical standbys, so its actually a better situation.
(at least, not without exchanging a lot of information that isn't currently happening), so some other mechanism would probably need to be used. One possibility is to pass along information about when things are in a state on the source that is known to be free of anomalies if read; another would be to reorder the application of transactions to match the apparent order of execution. The latter would not work for "physical" replication, but should be fine for logical replication. An implementation might create a list in commit order, but not release the front of the list for processing if it is a SERIALIZABLE transaction which has written data until all overlapping SERIALIZABLE transactions complete, so it can move any subsequently-committed SERIALIZABLE transaction which read the "old" version of the data ahead of it.
The best way would be to pass across "anomaly barriers", since they can easily be inserted into the WAL stream. The main issue seems to be how and when to detect them.
For logical replay, applying in batches is actually a good thing since it allows parallelism. We can remove them all from the target's procarray all at once to avoid intermediate states becoming visible. So that would be the preferred mechanism.
Collecting a list of transactions that must be applied before the current one could be accumulated during SSI processing and added to the commit record. But reordering the transaction apply is something we'd need to get some real clear theory on before we considered it.
Anyway, next release.
--
Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services