On Thu, Sep 8, 2011 at 7:06 AM, Chris Redekop <ch...@replicon.com> wrote: > 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....
The above has been posted in pgsql-general. The reason why Chris thinks a counterpart of pg_last_xact_replay_timestamp() is required sounds reasonable to me. So I'd like to propose new function "pg_last_xact_insert_timestamp" as the counterpart, which returns the timestamp of the last inserted commit or abort WAL record. I'll add the attached patch into the next CF. Comments? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center
*** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *************** *** 13996,14001 **** SELECT set_config('log_statement_stats', 'off', false); --- 13996,14004 ---- <primary>pg_current_xlog_location</primary> </indexterm> <indexterm> + <primary>pg_last_xact_insert_timestamp</primary> + </indexterm> + <indexterm> <primary>pg_start_backup</primary> </indexterm> <indexterm> *************** *** 14049,14054 **** SELECT set_config('log_statement_stats', 'off', false); --- 14052,14064 ---- </row> <row> <entry> + <literal><function>pg_last_xact_insert_timestamp()</function></literal> + </entry> + <entry><type>timestamp with time zone</type></entry> + <entry>Get current transaction log insert time stamp</entry> + </row> + <row> + <entry> <literal><function>pg_start_backup(<parameter>label</> <type>text</> <optional>, <parameter>fast</> <type>boolean</> </optional>)</function></literal> </entry> <entry><type>text</type></entry> *************** *** 14175,14180 **** postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); --- 14185,14199 ---- </para> <para> + <function>pg_last_xact_insert_timestamp</> displays the time stamp of last inserted + transaction. This is the time at which the commit or abort WAL record for that transaction. + This is initialized with the time stamp of last transaction replayed during recovery (i.e., + the return value of <function>pg_last_xact_replay_timestamp</>). If no transactions + have been replayed during recovery, <function>pg_last_xact_insert_timestamp</> + returns NULL until at least one commit or abort WAL record has been inserted. + </para> + + <para> For details about proper usage of these functions, see <xref linkend="continuous-archiving">. </para> *** a/doc/src/sgml/high-availability.sgml --- b/doc/src/sgml/high-availability.sgml *************** *** 867,872 **** primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass' --- 867,881 ---- <command>ps</> command (see <xref linkend="monitoring-ps"> for details). </para> <para> + You can also calculate the lag in time stamp by comparing the last + WAL insert time stamp on the primary with the last WAL replay + time stamp on the standby. They can be retrieved using + <function>pg_last_xact_insert_timestamp</> on the primary and + the <function>pg_last_xact_replay_timestamp</> on the standby, + respectively (see <xref linkend="functions-admin-backup-table"> and + <xref linkend="functions-recovery-info-table"> for details). + </para> + <para> You can retrieve a list of WAL sender processes via the <link linkend="monitoring-stats-views-table"> <literal>pg_stat_replication</></link> view. Large differences between *** a/src/backend/access/transam/xact.c --- b/src/backend/access/transam/xact.c *************** *** 1041,1046 **** RecordTransactionCommit(void) --- 1041,1049 ---- rdata[lastrdata].next = NULL; (void) XLogInsert(RM_XACT_ID, XLOG_XACT_COMMIT, rdata); + + /* Save timestamp of latest transaction commit record */ + SetLastInsertXTime(xlrec.xact_time); } else { *************** *** 1064,1069 **** RecordTransactionCommit(void) --- 1067,1075 ---- rdata[lastrdata].next = NULL; (void) XLogInsert(RM_XACT_ID, XLOG_XACT_COMMIT_COMPACT, rdata); + + /* Save timestamp of latest transaction commit record */ + SetLastInsertXTime(xlrec.xact_time); } } *************** *** 1433,1438 **** RecordTransactionAbort(bool isSubXact) --- 1439,1447 ---- (void) XLogInsert(RM_XACT_ID, XLOG_XACT_ABORT, rdata); + /* Save timestamp of latest transaction abort record */ + SetLastInsertXTime(xlrec.xact_time); + /* * Report the latest async abort LSN, so that the WAL writer knows to * flush this abort. There's nothing to be gained by delaying this, since *** a/src/backend/access/transam/xlog.c --- b/src/backend/access/transam/xlog.c *************** *** 391,396 **** typedef struct XLogCtlData --- 391,397 ---- XLogRecPtr asyncXactLSN; /* LSN of newest async commit/abort */ uint32 lastRemovedLog; /* latest removed/recycled XLOG segment */ uint32 lastRemovedSeg; + TimestampTz lastInsertXTime; /* timestamp of last COMMIT/ABORT record inserted */ /* Protected by WALWriteLock: */ XLogCtlWrite Write; *************** *** 608,613 **** static bool recoveryStopsHere(XLogRecord *record, bool *includeThis); --- 609,615 ---- static void recoveryPausesHere(void); static bool RecoveryIsPaused(void); static void SetRecoveryPause(bool recoveryPause); + static TimestampTz GetLastInsertXTime(void); static void SetLatestXTime(TimestampTz xtime); static TimestampTz GetLatestXTime(void); static void CheckRequiredParameterValues(void); *************** *** 5866,5871 **** pg_is_xlog_replay_paused(PG_FUNCTION_ARGS) --- 5868,5928 ---- } /* + * Save timestamp of latest inserted commit/abort record. + */ + void + SetLastInsertXTime(TimestampTz xtime) + { + /* use volatile pointer to prevent code rearrangement */ + volatile XLogCtlData *xlogctl = XLogCtl; + + SpinLockAcquire(&xlogctl->info_lck); + xlogctl->lastInsertXTime = xtime; + SpinLockRelease(&xlogctl->info_lck); + } + + /* + * Fetch timestamp of latest inserted commit/abort record. + */ + static TimestampTz + GetLastInsertXTime(void) + { + /* use volatile pointer to prevent code rearrangement */ + volatile XLogCtlData *xlogctl = XLogCtl; + TimestampTz xtime; + + SpinLockAcquire(&xlogctl->info_lck); + xtime = xlogctl->lastInsertXTime; + SpinLockRelease(&xlogctl->info_lck); + + return xtime; + } + + /* + * Returns timestamp of latest inserted commit/abort record. + * + * When the server has been started normally, this function returns + * NULL until at least one commit/abort record has been inserted. + */ + Datum + pg_last_xact_insert_timestamp(PG_FUNCTION_ARGS) + { + TimestampTz xtime; + + if (RecoveryInProgress()) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("recovery is in progress"), + errhint("WAL control functions cannot be executed during recovery."))); + + xtime = GetLastInsertXTime(); + if (xtime == 0) + PG_RETURN_NULL(); + + PG_RETURN_TIMESTAMPTZ(xtime); + } + + /* * Save timestamp of latest processed commit/abort record. * * We keep this in XLogCtl, not a simple static variable, so that it can be *************** *** 6944,6949 **** StartupXLOG(void) --- 7001,7012 ---- } /* + * Initialize the timestamp of the latest inserted COMMIT/ABORT record + * with the latest replayed one. + */ + SetLastInsertXTime(GetLatestXTime()); + + /* * If any of the critical GUCs have changed, log them before we allow * backends to write WAL. */ *** a/src/include/access/xlog.h --- b/src/include/access/xlog.h *************** *** 306,311 **** extern void CreateCheckPoint(int flags); --- 306,312 ---- extern bool CreateRestartPoint(int flags); extern void XLogPutNextOid(Oid nextOid); extern XLogRecPtr XLogRestorePoint(const char *rpName); + extern void SetLastInsertXTime(TimestampTz xtime); extern XLogRecPtr GetRedoRecPtr(void); extern XLogRecPtr GetInsertRecPtr(void); extern XLogRecPtr GetFlushRecPtr(void); *** a/src/include/access/xlog_internal.h --- b/src/include/access/xlog_internal.h *************** *** 270,275 **** extern Datum pg_current_xlog_location(PG_FUNCTION_ARGS); --- 270,276 ---- extern Datum pg_current_xlog_insert_location(PG_FUNCTION_ARGS); extern Datum pg_last_xlog_receive_location(PG_FUNCTION_ARGS); extern Datum pg_last_xlog_replay_location(PG_FUNCTION_ARGS); + extern Datum pg_last_xact_insert_timestamp(PG_FUNCTION_ARGS); extern Datum pg_last_xact_replay_timestamp(PG_FUNCTION_ARGS); extern Datum pg_xlogfile_name_offset(PG_FUNCTION_ARGS); extern Datum pg_xlogfile_name(PG_FUNCTION_ARGS); *** a/src/include/catalog/pg_proc.h --- b/src/include/catalog/pg_proc.h *************** *** 2869,2874 **** DATA(insert OID = 2850 ( pg_xlogfile_name_offset PGNSP PGUID 12 1 0 0 0 f f f t --- 2869,2876 ---- DESCR("xlog filename and byte offset, given an xlog location"); DATA(insert OID = 2851 ( pg_xlogfile_name PGNSP PGUID 12 1 0 0 0 f f f t f i 1 0 25 "25" _null_ _null_ _null_ _null_ pg_xlogfile_name _null_ _null_ _null_ )); DESCR("xlog filename, given an xlog location"); + DATA(insert OID = 3831 ( pg_last_xact_insert_timestamp PGNSP PGUID 12 1 0 0 0 f f f t f v 0 0 1184 "" _null_ _null_ _null_ _null_ pg_last_xact_insert_timestamp _null_ _null_ _null_ )); + DESCR("timestamp of last insert xact"); DATA(insert OID = 3810 ( pg_is_in_recovery PGNSP PGUID 12 1 0 0 0 f f f t f v 0 0 16 "" _null_ _null_ _null_ _null_ pg_is_in_recovery _null_ _null_ _null_ )); DESCR("true if server is in recovery");
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers