Ensuring Postgresql BDR replication

Question

My understanding, reading between the lines, is that BDR replicates row by row, starting after the COMMIT. I have a requirement to be able to then wait until some nodes have actually replicated my latest commit. If not, I will report the problem upstream. And maybe mark the transaction as dubious on the local db, which will hopefully also get replicated.

bdr.bdr_node_slots seems to tell me a pg_lsn of the latest replication of each node. But I need to know the pg_lsn of my last transaction. Knowing the current state of the db now that transaction committed would be close enough.

I could only find pg_current_xlog_insert_location(), but it looks different.

Answer 1

My understanding, reading between the lines, is that BDR replicates row by row, starting after the COMMIT.

Right - but it replicates transactions with the same transaction boundaries as on the original node. So they only become visible at commit time on the downstream.

e.g.

BEGIN;
INSERT INTO x (a) VALUES (1), (2), (3);
INSERT INTO x (a) VALUES (4);
COMMIT;
BEGIN;
INSERT INTO x (a) VALUES (42);
COMMIT;

replicates as

BEGIN;
INSERT INTO x (a) VALUES (1);
INSERT INTO x (a) VALUES (2);
INSERT INTO x (a) VALUES (3);
INSERT INTO x (a) VALUES (4);
COMMIT;
BEGIN;
INSERT INTO x (a) VALUES (42);
COMMIT;

Note, however, that conflict resolution is row-by-row. So if your downstream already had a row with a = 4, and a was the PRIMARY KEY, that INSERT would get ignored but the rest would proceed normally, and the whole xact would commit as an insert of 1, 2, and 4.

But I need to know the pg_lsn of my last transaction.

Unfortunately, PostgreSQL does not report commit LSNs. I've tried to get optional support for reporting them at commit time, but without success so far. Doing it "right" requires extending the wire protocol, and a few people objected to the backward-compatible way of doing so that I proposed.

The best you can do right now is wait until replay has passed your commit, by doing:

BEGIN;
... do stuff ...;
COMMIT;
SELECT pg_current_xlog_insert_location();

and waiting until replication passes that point. But since this may contain other unimportant work after your commit by the time you call it, and the walsender doesn't rush to replicate unimportant bookkeeping to peers, sometimes it can take a while to pass that LSN. Not usually a problem in production, but can cause stalls in tests and small scale setups.

There's not really an alternative right now though. You can perform a dummy xact after the real one you're interested in to make sure you replicate immediately, at the cost of burning xids, but it must be a real write xact otherwise it'll be silently discarded.

• Tuntable: Thanks for that. pg_current_xlog_insert_location(); should do.
• Tuntable: I do not think replication respects transaction boundaries, other than to start after the initial transaction commits. See sdf.org/~riley/blog/2016/01/04/…
• Craig Ringer: @Tuntable Conflict resolution logic applies row-by-row, but changes are indeed applied transactionally, i.e. rows from an upstream xact become visible on the downstream only after downstream commit, not piecemeal. The reason conflict resolution is row-level is that we can't abort the already-committed xact if there's a conflict, so we have to resolve it and proceed.