On Thu, Sep 8, 2011 at 6:43 AM, Fujii Masao <masao.fujii@xxxxxxxxx> wrote: > On Thu, Sep 8, 2011 at 7:06 AM, Chris Redekop <chris@xxxxxxxxxxxx> wrote: >> Is there anything available to get the last time a transaction >> occurred?....like say "pg_last_xact_timestamp"? > > No. > >> 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.... > > Your complaint makes sense. I'll implement something like > pg_last_xact_timestamp() for 9.2. But unfortunately there is > no way to know such a timestamp on the master, in 9.1.. I see the reason, but would be against that change. We don't currently generate a timestamp for each WAL record. Doing so would be a performance drain and a contention hotspot. I think Chris should change his function to a CASE statement so that his function returns zero when master and slave have matching WAL positions, and we only calculate the delay when there is outstanding WAL. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
