Re: Sync Rep v19

On Fri, Mar 11, 2011 at 5:50 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Mar 10, 2011 at 3:29 PM, Dimitri Fontaine
> <dimitri(at)2ndquadrant(dot)fr> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> they are, but there's no easy way to figure out what that means in
>>> terms of wall-clock time, which I think would be useful.
>>
>> Jan Wieck had a detailed proposal to make that happen at last developper
>> meeting, but then ran out of time to implement it for 9.1 it seems.  The
>> idea was basically to have a ticker in core, an SRF that would associate
>> txid_snapshot with wall clock time.  Lots of good things would come from
>> that.
>>
>>  http://archives.postgresql.org/pgsql-hackers/2010-05/msg01209.php
>>
>> Of course if you think that's important enough for you to implement it
>> between now and beta, that would be great :)
>
> I think that's actually something a little different, and more
> complicated, but I do think it'd be useful.  I was hoping there was a
> simple way to get some kind of time-based information into
> pg_stat_replication, but if there isn't, there isn't.

How about sending the timestamp of last applied transaction
(i.e., this is the return value of pg_last_xact_replay_timestamp)
from the standby to the master, and reporting it in
pg_stat_replication? Then you can see the lag by comparing
it with current_timestamp.

But since the last replay timestamp doesn't advance (but
current timestamp advances) if there is no work on the master,
the calculated lag might be unexpectedly too large. So, to
calculate the exact lag, I'm thinking that we should introduce
new function which returns the timestamp of the last transaction
written in the master.

Thought?

Regards,

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