On Thu, Sep 8, 2011 at 7:06 AM, Chris Redekop <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
