Re: tracking commit timestamps

On 25/11/14 17:16, Simon Riggs wrote:
> 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.
>

Won't the pg_last_committed_xact() on slave + pg_xact_commit_timestamp()
on master with the xid returned by slave accomplish the same thing?

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