(Please reply-to-all to keep the thread on pgsql-general)
On 26 March 2015 at 18:32, Peter Mogensen <apm@one.com> wrote:
On 2015-03-26 10:14, Craig Ringer wrote:
I see what you're getting at. You want to prevent stale data from being reinsterted into a cache by a read from an asynchronous replica after it's been invalidated and purged by a write on the master. Your application provides cross-node co-ordination and can set a horizon of validity to prevent such caching events.
Ohh... I get why you think of asynchronous replicas. Yes. the system would also prevent that if the txids are global. However that's not the goal. The goal is only to prevent local race conditions wrt. a local node.
If that's the case then BDR shouldn't make any difference.
Earlier you were speaking of (presumably not synchronous) streaming replicas, and writes on the master vs reads from the replica, and a way you avoid caching stale data from the read-replica using the application's cache manager to co-ordinate visibility horizons. It sounded like you were looking for a way to translate that into something that could work with BDR.
Did I misunderstand?
I've seen in production how stale values can end up in the cache due to race condition like:
1) Client tries to read from the cache and gets a cache miss causing it to query the database. The resulting value should now be cached. 2) The value is changed in the database, causing an invalidation event. 3) The invalidation event is applied to the cache (being a NOP since it was not there). 4) Now the client manages to apply it's read value to the cache, but now it's an old stale value.
One way to handle that is to SELECT ... FOR SHARE in your reads, then update the cache and not commit until the cache has been updated.
A concurrent UPDATE can then not proceed until the cache write has been completed.
That could be tricky to apply in practice though, and prone to deadlocks caused by lock-ordering issues.
If it's purely local you can continue to use your current approach without issues. If not, then commit timestamps may offer some assistance when combined with accurate time sync.
