In my reading of this, it looks like the practical implication could be that reads happening quickly after writes to the same row(s) might return stale data. The write transaction gets marked as complete before all of the distributed layers of a multi AZ RDS instance have been fully updated, such that immediate reads from the same rows might return nothing (if the row does not exist yet) or older values if the columns have not been fully updated.
Due to the way PostgreSQL does snapshotting, I don't believe this implies such a read might obtain a nonsense value due to only a portion of the bytes in a multi-byte column type having been updated yet.
It seems like a race condition that becomes eventually consistent. Or did anyone read this as if the later transaction(s) of a "long fork" might never complete under normal circumstances?
This isn't just stale data, in the sense of "a point-in-time consistent snapshot which does not reflect some recent transactions". I think what's going on here is that a read-only transaction against a secondary can observe some transaction T, but also miss transactions which must have logically executed before T.
"I think what's going on here is that a read-only transaction against a secondary can observe some transaction T, but also miss transactions which must have logically executed before T."
i was intuitively wondering the same but i'm having trouble reasoning how the post's example with transactions 1, 2, 3, 4 exhibits this behavior. in the example, is transaction 2 the only read-only transaction and therefore the only transaction to read from the read replica? i.e. transactions 1, 3, 4 use the primary and transaction 2 uses the read replica?
The Write-Ahead Logging (WAL) is single-threaded and maintains consistency at a specific point in time in each instance. However, there can be anomalies between two instances. This behavior is expected because the RDS Multi-AZ cluster does not wait for changes to be applied in the shared buffers. It only waits for the WAL to sync.
This is similar to the behavior of PostgreSQL when synchronous_commit is set to on. Nothing unexpected.
ah, so something like... if the primary ordered transaction 3 < transaction 1, but transaction 2 observes only transaction 1 on the read-only secondary potentially because the secondary orders transaction 1 < transaction 3?
To provide a simple (although contrived) example of the type of thing that can happen. Imagine that you have a table with three columns `gps_coordinate`, `postal_code` and `city`. The way these are set is that the new coordinate gets posted to the API and `gps_coordinate` is updated. This then kicks off a background task that uses the new coordinate to lookup and update `postal_code`. Then another background task uses the postal code to look up and set `city`.
Since these happen sequentially, for a single update of `gps_coordinate` you would only expect to be able to observe one of:
1. Nothing updated yet, all columns have the previous value.
2. `gps_coordinate` updated, with `postal_code` and `city` still having the previous values.
3. `gps_coordinate` and `postal_code` updated with `city` still having the previous value.
4. All fields updated.
But the ordering that aphyr proved is possible allows you to see "impossible" states such as
1. `postal_code` updated with `gps_coordinate` and `city` still having the previous values.
2. `city` updated with `gps_coordinate` and `postal_code` still having the previous values.
Basically since these transactions happen in order and depend on one another you would expect that you can only see the "left to right" progression. But actually you can see some subset of the transactions applied even if that isn't a possible logical state of the database.
Due to the way PostgreSQL does snapshotting, I don't believe this implies such a read might obtain a nonsense value due to only a portion of the bytes in a multi-byte column type having been updated yet.
It seems like a race condition that becomes eventually consistent. Or did anyone read this as if the later transaction(s) of a "long fork" might never complete under normal circumstances?