How a Repeatable Read transaction run on the standby could see a transient state?

Question

In the PostgreSQL documentation about transaction isolation levels, we can read the following:

While performing all permanent database writes within Serializable transactions on the master will ensure that all standbys will eventually reach a consistent state, a Repeatable Read transaction run on the standby can sometimes see a transient state which is inconsistent with any serial execution of serializable transactions on the master.

The above text is at the bottom of http://www.postgresql.org/docs/9.4/static/transaction-iso.html

In what circumstances a Repeatable Read transaction ran on a hot standby could see an inconsistent transient state?

Answer 1

The answer from Geir Bostad is correct; I am responding just to provide an example and possible workaround, since people often have trouble getting their heads around this. Besides the Wiki page referenced in the other answer there is a separate Wiki page just for examples which includes a "Deposit Report" example of seeing a batch as closed but not yet being able to see the final detail for the batch.

A hot standby is read-only, so it can only see transient serialization anomalies; the data will eventually settle into a consistent state in some subsequent transaction. This is the same as what can occur with a repeatable read transaction on the primary server, and is not possible with a serializable transaction on the primary server. To prevent confusion, serializable transactions are not allowed on a hot standby, since you cannot (yet) be guaranteed to see truly serializable transaction behavior there. There has been talk of adding information to the WAL stream to identify points where a serializable transaction could be started without risk of seeing anomalies, similar to the DEFERRABLE option of START TRANSACTION; this (or something like it) is likely to be added in some future version.

For read-only transactions using snapshot isolation (including transactions identified as serializable in Oracle or in PostgreSQL versions prior to 9.1) the most common type of anomaly seems to be a SELECT listing or summarizing a batch which appears to the transaction as "closed", without seeing all of the work which should be included with the batch. To see this, the transaction closing the batch must "overlap" (run concurrently with) some transaction modifying batch detail, the transaction closing the batch must commit first, then (after the transaction closing the batch has committed and before the transaction modifying batch detail commits) the read only transaction must start and acquire its snapshot.

To prevent this, the simplest technique is to "promote the conflict" on the primary from a read-write dependency to a write-write conflict. For example, in the "Deposit Report" example referenced above a deposit_total column could be added to the control table which is set to zero by an AFTER trigger when deposit_no is incremented, and an AFTER trigger could be added to the receipt table to update the amount based on the amount of each receipt. That would cause a serialization failure based on the write conflict of the control record, preventing the anomaly from being propagated to the replica.

Answer 2

Looks like this can happen with batch processing and similar applications.

I found some information about the topic in a wiki page about SSI, and some background details from the postgresql-hackers mailing list where they discuss how to handle serialization on hot standbys.

Kevin Grittner writes in his post:

The most common and alarming situation where this occurs, in my opinion, is batch processing. This is extremely common in financial applications, and tends to show up in a lot of other places, too.

[..]

Queries on the standby can, however, see transient anomalies when they run queries which would cause a serialization failure if run on the master at the same point in the transaction stream. This can only occur when, of two concurrent transactions, the one which appears to run second because the other can't read what it wrote, commits first.

[..]

Under SSI, one of these transactions will be canceled to prevent this. Our implementation will always allow the update which closes the batch to complete, and either the insert or the select of the detail will be rolled back with a serialization failure, depending on the timing the actions inside those transactions. If the insert fails, it can be retried, and will land in the new batch -- making the list of the batch which omits it OK. If the listing of the batch details is canceled, it will be because the insert into the old batch committed before it recognized the problem, so an immediate retry of the select will see the complete batch contents.

A hot standby can't really take part in the predicate locking and transaction cancellation on the master.