On 26 March 2015 at 15:57, Peter Mogensen <apm@one.com> wrote:
In standard Postgres one-way replication you can get the txid_snapshot_xmin() of the master on the slave. But with BDR, all nodes have their own txids. So the above scheme doesn't work unless you can get the txid which applied the change to the local node. ... I can see that applying BDR replication consumes transactions, so I assumed this value exists ... and if only there was a way to get it. I you could get it in a trigger, then you could maintain the invalidation event queue locally for each node.
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.
There are a few things you could work with in BDR that may be useful.
First, BDR adds commit timestamps for transactions, giving you wall-clock time commit information. It's used for last-update-wins conflict resolution. Commit timestamp information is accessible from SQL using:
pg_get_transaction_committime(xid)
pg_get_latest_transaction_committime_data()
(Note that these are the BDR definitions. The versions committed to PostgreSQL 9.5 have different names and some interface changes, so applications using these functions may need changes when updated to run on PostgreSQL 9.5+bdr when released, though BDR will probably add backwards-compat wrappers.)
Second, PostgreSQL keeps track of the node-wide log-sequence number (LSN). The replay position of a node is available on the upstream in pg_catalog.pg_stat_replication. The last-replayed LSN of an upstream is kept track of by the BDR extension and recorded as a replication identifier, but the SQL-level interface to replication identifiers, pg_catalog.pg_replication_identifier_progress. This view is only accessible by the superuser at the moment. You can also access the upstream commit's LSN at the C level from the BDR apply worker during transaction replay, though. The LSN provides strict ordering for a node.
I'd start by looking into whether commit timestamps can meet your needs.
