On Mon, Oct 3, 2011 at 6:31 PM, Fujii Masao <[email protected]> wrote:
>> Also, in pg_last_xact_insert_timestamp, the errhint() seems a little
>> strange - this is not exactly a WAL *control* function, is it?
>
> Not only "control" but also "WAL" might be confusing. What about
> "transaction information functions"?
Attached is the updated version of the patch. In the patch, I used the
function name itself in the HINT message, i.e., the HINT message is
the following.
pg_last_xact_insert_timestamp() cannot be executed during recovery.
>> In the documentation, for the short description of
>> pg_last_xact_insert_timestamp(), how about something like "returns the
>> time at which a transaction commit or transaction about record was
>> last inserted into the transaction log"? Or maybe that's too long.
>> But the current description doesn't seem to do much other than
>> recapitulate the function name, so I'm wondering if we can do any
>> better than that.
>
> Agreed. I will change the description per your suggestion.
Done.
>> I think that instead of hacking up the backend-status copying code to
>> have a mode where it copies everything, you should just have a
>> special-purpose function that computes the value you need directly off
>> the backend status entries themselves. This approach seems like it
>> both clutters the code and adds lots of extra data copying.
>
> Agreed. Will change.
Done.
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,14067 ----
</row>
<row>
<entry>
+ <literal><function>pg_last_xact_insert_timestamp()</function></literal>
+ </entry>
+ <entry><type>timestamp with time zone</type></entry>
+ <entry>
+ Get the time at which a transaction commit or transaction abort record
+ was last inserted into the transaction log
+ </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());
--- 14188,14200 ----
</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.
+ If there has been no transaction committed or aborted yet since the server has started,
+ this function returns NULL.
+ </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
***************
*** 1066,1071 **** RecordTransactionCommit(void)
--- 1066,1074 ----
(void) XLogInsert(RM_XACT_ID, XLOG_XACT_COMMIT_COMPACT, rdata);
}
+
+ /* Save timestamp of latest transaction commit record */
+ pgstat_report_xact_end_timestamp(xactStopTimestamp);
}
/*
***************
*** 1434,1439 **** RecordTransactionAbort(bool isSubXact)
--- 1437,1445 ----
(void) XLogInsert(RM_XACT_ID, XLOG_XACT_ABORT, rdata);
+ /* Save timestamp of latest transaction abort record */
+ pgstat_report_xact_end_timestamp(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
***************
*** 4968,4970 **** xact_desc(StringInfo buf, uint8 xl_info, char *rec)
--- 4974,5000 ----
else
appendStringInfo(buf, "UNKNOWN");
}
+
+ /*
+ * Returns timestamp of latest inserted commit/abort record.
+ *
+ * If there has been no transaction committed or aborted yet since
+ * the server has started, this function returns NULL.
+ */
+ 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("pg_last_xact_insert_timestamp() cannot be executed during recovery.")));
+
+ xtime = pgstat_get_last_xact_end_timestamp();
+ if (xtime == 0)
+ PG_RETURN_NULL();
+
+ PG_RETURN_TIMESTAMPTZ(xtime);
+ }
*** a/src/backend/postmaster/pgstat.c
--- b/src/backend/postmaster/pgstat.c
***************
*** 2201,2206 **** pgstat_fetch_stat_numbackends(void)
--- 2201,2207 ----
return localNumBackends;
}
+
/*
* ---------
* pgstat_fetch_global() -
***************
*** 2418,2423 **** pgstat_bestart(void)
--- 2419,2429 ----
beentry->st_appname[NAMEDATALEN - 1] = '\0';
beentry->st_activity[pgstat_track_activity_query_size - 1] = '\0';
+ /*
+ * Don't reset st_xact_end_timestamp because the previous value can still
+ * be referenced to calculate the latest transaction insert timestamp.
+ */
+
beentry->st_changecount++;
Assert((beentry->st_changecount & 1) == 0);
***************
*** 2564,2569 **** pgstat_report_xact_timestamp(TimestampTz tstamp)
--- 2570,2598 ----
Assert((beentry->st_changecount & 1) == 0);
}
+ /*
+ * Report last transaction end timestamp as the specified value.
+ * Zero means there is no finished transaction.
+ */
+ void
+ pgstat_report_xact_end_timestamp(TimestampTz tstamp)
+ {
+ volatile PgBackendStatus *beentry = MyBEEntry;
+
+ if (!beentry)
+ return;
+
+ /*
+ * Update my status entry, following the protocol of bumping
+ * st_changecount before and after. We use a volatile pointer here to
+ * ensure the compiler doesn't try to get cute.
+ */
+ beentry->st_changecount++;
+ beentry->st_xact_end_timestamp = tstamp;
+ beentry->st_changecount++;
+ Assert((beentry->st_changecount & 1) == 0);
+ }
+
/* ----------
* pgstat_report_waiting() -
*
***************
*** 2752,2757 **** pgstat_get_backend_current_activity(int pid, bool checkUser)
--- 2781,2840 ----
}
+ /* ----------
+ * pgstat_get_last_xact_end_timestamp() -
+ *
+ * Returns timestamp of latest inserted commit/abort record.
+ *
+ * If there has been no transaction committed or aborted yet since
+ * the server has started, this function returns zero.
+ * ----------
+ */
+ TimestampTz
+ pgstat_get_last_xact_end_timestamp(void)
+ {
+ volatile PgBackendStatus *beentry;
+ TimestampTz result = 0;
+ int i;
+
+ Assert(!pgStatRunningInCollector);
+
+ beentry = BackendStatusArray;
+ for (i = 1; i <= MaxBackends; i++)
+ {
+ TimestampTz xtime = 0;
+
+ /*
+ * Follow the protocol of retrying if st_changecount changes while we
+ * copy the entry, or if it's odd. (The check for odd is needed to
+ * cover the case where we are able to completely copy the entry while
+ * the source backend is between increment steps.) We use a volatile
+ * pointer here to ensure the compiler doesn't try to get cute.
+ */
+ for (;;)
+ {
+ int save_changecount = beentry->st_changecount;
+
+ xtime = beentry->st_xact_end_timestamp;
+
+ if (save_changecount == beentry->st_changecount &&
+ (save_changecount & 1) == 0)
+ break;
+
+ /* Make sure we can break out of loop if stuck... */
+ CHECK_FOR_INTERRUPTS();
+ }
+
+ if (result < xtime)
+ result = xtime;
+
+ beentry++;
+ }
+
+ return result;
+ }
+
+
/* ------------------------------------------------------------
* Local support functions follow
* ------------------------------------------------------------
*** a/src/include/access/xlog_internal.h
--- b/src/include/access/xlog_internal.h
***************
*** 272,277 **** extern Datum pg_current_xlog_location(PG_FUNCTION_ARGS);
--- 272,278 ----
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");
*** a/src/include/pgstat.h
--- b/src/include/pgstat.h
***************
*** 623,628 **** typedef struct PgBackendStatus
--- 623,631 ----
TimestampTz st_xact_start_timestamp;
TimestampTz st_activity_start_timestamp;
+ /* Time when last transaction ended */
+ TimestampTz st_xact_end_timestamp;
+
/* Database OID, owning user's OID, connection client address */
Oid st_databaseid;
Oid st_userid;
***************
*** 718,725 **** extern void pgstat_bestart(void);
--- 721,730 ----
extern void pgstat_report_activity(const char *cmd_str);
extern void pgstat_report_appname(const char *appname);
extern void pgstat_report_xact_timestamp(TimestampTz tstamp);
+ extern void pgstat_report_xact_end_timestamp(TimestampTz tstamp);
extern void pgstat_report_waiting(bool waiting);
extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser);
+ extern TimestampTz pgstat_get_last_xact_end_timestamp(void);
extern PgStat_TableStatus *find_tabstat_entry(Oid rel_id);
extern PgStat_BackendFunctionEntry *find_funcstat_entry(Oid func_id);
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
