I'd like to cache parts of my database locally on each client. To keep
those caches in sync I'd like to implement an invalidation queue.
A naïve approach would be to simply create a table of (txn_id,
invalidated_object_ids), then have the clients query this table for
txn_ids > last_queried_txn_id. But I suspect this could result in
invalidations being missed for long running transactions due to MVCC
visibility issues.
How might I go about implementing such a queue safely in Postgres?
Laurence