Re: master-side counterpart of pg_last_xact_replay_timestamp?

On Thu, Sep 8, 2011 at 6:43 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> On Thu, Sep 8, 2011 at 7:06 AM, Chris Redekop <chris(at)replicon(dot)com> wrote:
>> Is there anything available to get the last time a transaction
>> occurred?....like say "pg_last_xact_timestamp"?
>
> No.
>
>> In order to accurately
>> calculate how far behind my slave is I need to do something like
>> master::pg_last_xact_timestamp() -
>> slave::pg_last_xact_replay_timestamp()....currently I'm using now() instead
>> of the pg_last_xact_timestamp() call, but then when the master is not busy
>> the slave appears to lag behind.  I'm considering writing a C module to get
>> the last modified file time of the xlog, but I'm hoping there is a better
>> alternative that I haven't found yet....
>
> Your complaint makes sense. I'll implement something like
> pg_last_xact_timestamp() for 9.2. But unfortunately there is
> no way to know such a timestamp on the master, in 9.1..

I see the reason, but would be against that change.

We don't currently generate a timestamp for each WAL record. Doing so
would be a performance drain and a contention hotspot.

I think Chris should change his function to a CASE statement so that
his function returns zero when master and slave have matching WAL
positions, and we only calculate the delay when there is outstanding
WAL.

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