Re: tracking commit timestamps

On 25 November 2014 at 13:35, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:

> Can I check my understanding? Probably we cannot use this feature to calculate
> the actual replication lag by, for example, comparing the result of
> pg_last_committed_xact() in the master and that of
> pg_last_xact_replay_timestamp()
> in the standby. Because pg_last_xact_replay_timestamp() can return even
> the timestamp of aborted transaction, but pg_last_committed_xact()
> cannot. Right?

It was intended for that, but I forgot that
pg_last_xact_replay_timestamp() includes abort as well.

I suggest we add a function that returns both the xid and timestamp on
the standby:
* pg_last_commit_replay_info() - which returns both the xid and
timestamp of the last commit replayed on standby
* then we use the xid from the standby to lookup the commit timestamp
on the master.
We then have two timestamps that refer to the same xid and can
subtract to give us an exact replication lag.

That can be done manually by user if requested.

We can also do that by sending the replay info back as a feedback
message from standby to master, so the information can be calculated
by pg_stat_replication when requested.

I'll work on that once we have this current patch committed.

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