Thread: master-side counterpart of pg_last_xact_replay_timestamp?
Is there anything available to get the last time a transaction occurred?....like say "pg_last_xact_timestamp"? 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....
On Thu, Sep 8, 2011 at 7:06 AM, Chris Redekop <chris@replicon.com> 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.. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
On Thu, Sep 8, 2011 at 6:43 AM, Fujii Masao <masao.fujii@gmail.com> wrote: > On Thu, Sep 8, 2011 at 7:06 AM, Chris Redekop <chris@replicon.com> 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
On Thu, Sep 8, 2011 at 3:19 PM, Simon Riggs <simon@2ndquadrant.com> wrote: > On Thu, Sep 8, 2011 at 6:43 AM, Fujii Masao <masao.fujii@gmail.com> wrote: >> 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. Each commit/abort record already has a timestamp. So I'm thinking to implement pg_last_xact_insert_timestamp() so that it returns the timestamp of the last inserted commit/abort record. Since we don't need to generate a timestamp newly, I guess that what I'm thinking to implement would not degrade a performance. pg_last_xact_replay_timestamp() also returns the timestamp of the commit/abort record replayed. So pg_last_xact_insert_timestamp() doesn't need to return the timestamp other than that of commit/abort record, to compare them to calculate the replication delay. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center