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 <robertmh...@gmail.com> wrote: > On Thu, Sep 8, 2011 at 6:14 AM, Fujii Masao <masao.fu...@gmail.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 >