Re: pg_last_xact_insert_timestamp

On Thu, Sep 8, 2011 at 9:36 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"?  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....
>
> The above has been posted in pgsql-general. The reason why Chris thinks
> a counterpart of pg_last_xact_replay_timestamp() is required sounds
> reasonable to me. So I'd like to propose new function
> "pg_last_xact_insert_timestamp" as the counterpart, which returns the
> timestamp of the last inserted commit or abort WAL record. I'll add the
> attached patch into the next CF.

For reasons stated on the original thread, I don't think we need this.

The OP can calculate what he wants without this.

The code already exists in recovery and has some purpose there. Adding
similar code to the mainline just so somebody can run this function
occasionally is not good. Based on this I will be looking to see if we
can optimise the recovery path some more.

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