The pg_xlog_location_diff within your query may return more than 0.
There are three writing timing of WAL data to WAL segment file: 1) When a transaction is committed, 2) When WAL buffer is filled up, 3) When WAL writer process writes periodically (default is 200 milliseconds).
And basic sequence of streaming replication is shown below:
- (master) Write WAL data.
- (master) Send WAL data to a slave.
- (slave) Receive WAL data and replay it.
- (slave) Return some information (e.g. replay location, receive location, flushed location.)
I show an extreme example to clarify the problem. If you run a long transaction to insert huge data, master continues to send WAL data till the transaction is committed. When you issue your query during the period from 2 to 3,
pg_current_xlog_location() returns the current location whereas
replay_location returns the previous replay location,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location) therefore returns more than 0.
Other case is the following: The configuration parameter
max_standby_*_delay is enable and a conflict occurs, your standby suspends replaying WAL data, therefore
pg_xlog_location_diff() returns more than 0.
(Both of examples are logical extreme cases.)
In any case, I think your problem does not depend on whether replication mode is sync or async. The difference between sync and async is that the master confirms a synchronous slave commits transactions or not.
Your problem is related to general sequence of WAL data sending and replaying, but is not depend on the synchronous mode.
(Consider how a long transaction is committed in synchronous mode. I discussed what happens when your query is issued during the period from 2 to 3 (it's very short period) in the streaming replication sequence I showed. I did not discussed when transaction is committed because it is not essential of the answer, i think.)