Re: pg_last_xact_insert_timestamp

Thanks for all the feedback guys. Just to throw another monkey wrench in
here - I've been playing with Simon's proposed solution of returning 0 when
the WAL positions match, and I've come to the realizatiion that even if
using pg_last_xact_insert_timestamp, although it would help, we still
wouldn't be able to get a 100% accurate "how far behind?" counter....not
that this is a big deal, but I know my ops team is going to bitch to me
about it :).....take this situation: there's a lull of 30 seconds where
there are no transactions committed on the server....the slave is totally
caught up, WAL positions match, I'm reporting 0, everything is happy. Then
a transaction is committed on the master....before the slave gets it my
query hits it and sees that we're 30 seconds behind (when in reality we're
<1sec behind).....Because of this affect my graph is a little spikey...I
mean it's not a huge deal or anything - I can put some sanity checking in my
number reporting ("if 1 second ago you were 0 seconds behind, you can't be
more than 1 second behind now" sorta thing). But if we wanted to go for
super-ideal solution there would be a way to get the timestamp of
pg_stat_replication.replay_location+1 (the first transaction that the slave
does not have).

On Thu, Sep 8, 2011 at 7:03 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Thu, Sep 8, 2011 at 6:14 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> > 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.
>
> It seems very nice from a usability point of view, but I have to agree
> with Simon's concern about performance. Actually, as of today,
> WALInsertLock is such a gigantic bottleneck that I suspect the
> overhead of this additional bookkeeping would be completely
> unnoticeable. But I'm still reluctant to add more centralized
> spinlocks that everyone has to fight over, having recently put a lot
> of effort into getting rid of some of the ones we've traditionally
> had.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>