Re: pg_last_xact_insert_timestamp

On Thu, Sep 8, 2011 at 5:55 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Thu, Sep 8, 2011 at 9:36 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> 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.

Okay. Let me explain another use case of pg_last_xact_insert_timestamp().
The existing functions might be enough for checking whether the standby
has already caught up with the master. But I think that new function would be
very useful to calculate *how much* the standby is behind from the master.

Of course, we can do that by using the existing functions. But a problem is
that they return LSN and the calculated delay is represented as the size of
WAL. For users, it's not easy to handle LSN (e.g., there is no function to do
calculation of LSN), and the delay in WAL size is not intuitive. I've sometimes
received such a complaint.

OTOH, new function enables users to monitor the delay as a timestamp.
For users, a timestamp is obviously easier to handle than LSN, and the delay
as a timestamp is more intuitive. So, I think that it's worth adding
something like pg_last_xact_insert_timestamp into core for improvement
of user-friendness.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center