Re: BDR - triggers on receiving node?

On 26 March 2015 at 15:57, Peter Mogensen <apm(at)one(dot)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.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services