On Mon, Aug 11, 2014 at 8:27 PM, Fujii Masao <masao.fu...@gmail.com> wrote: > On Mon, Aug 11, 2014 at 4:46 PM, Andres Freund <and...@2ndquadrant.com> wrote: >> Hi, >> >> On 2011-10-04 20:52:59 +0900, Fujii Masao wrote: >>> *** 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); >>> } >>> >> >> Perhaps that pgstat_report() should instead be combined with the >> pgstat_report_xact_timestamp(0) in CommitTransaction()? Then the number >> of changecount increases and cacheline references would stay the >> same. The only thing that'd change would be a single additional >> assignment. > > Sounds good suggestion.
I attached the updated version of the patch. I changed pgstat_report_xx functions like Andres suggested. > While reading the patch again, I found it didn't handle the COMMIT/ABORT > PREPARED case properly. According to the commit e74e090, now > pg_last_xact_replay_timestamp() returns the timestamp of COMMIT/ABORT > PREPARED. > pg_last_xact_insert_timestamp() is mainly expected to be used to calculate > the replication delay, so it also needs to return that timestam. But the patch > didn't change 2PC code at all. We need to add > pgstat_report_xact_end_timestamp() > into FinishPreparedTransaction(), RecordTransactionCommitPrepared() or > RecordTransactionAbortPrepared(). Done. Regards, -- Fujii Masao
*** a/doc/src/sgml/func.sgml --- b/doc/src/sgml/func.sgml *************** *** 16116,16121 **** SELECT set_config('log_statement_stats', 'off', false); --- 16116,16124 ---- <primary>pg_current_xlog_location</primary> </indexterm> <indexterm> + <primary>pg_last_xact_insert_timestamp</primary> + </indexterm> + <indexterm> <primary>pg_start_backup</primary> </indexterm> <indexterm> *************** *** 16180,16185 **** SELECT set_config('log_statement_stats', 'off', false); --- 16183,16195 ---- </row> <row> <entry> + <literal><function>pg_last_xact_insert_timestamp()</function></literal> + </entry> + <entry><type>timestamp with time zone</type></entry> + <entry>Get last 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>pg_lsn</type></entry> *************** *** 16334,16339 **** postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); --- 16344,16356 ---- </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 *************** *** 862,867 **** primary_conninfo = 'host=192.168.1.50 port=5432 user=foo password=foopass' --- 862,876 ---- <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/twophase.c --- b/src/backend/access/transam/twophase.c *************** *** 156,167 **** static void RecordTransactionCommitPrepared(TransactionId xid, RelFileNode *rels, int ninvalmsgs, SharedInvalidationMessage *invalmsgs, ! bool initfileinval); static void RecordTransactionAbortPrepared(TransactionId xid, int nchildren, TransactionId *children, int nrels, ! RelFileNode *rels); static void ProcessRecords(char *bufptr, TransactionId xid, const TwoPhaseCallback callbacks[]); static void RemoveGXact(GlobalTransaction gxact); --- 156,169 ---- RelFileNode *rels, int ninvalmsgs, SharedInvalidationMessage *invalmsgs, ! bool initfileinval, ! TimestampTz *lastXactTime); static void RecordTransactionAbortPrepared(TransactionId xid, int nchildren, TransactionId *children, int nrels, ! RelFileNode *rels, ! TimestampTz *lastXactTime); static void ProcessRecords(char *bufptr, TransactionId xid, const TwoPhaseCallback callbacks[]); static void RemoveGXact(GlobalTransaction gxact); *************** *** 1353,1358 **** FinishPreparedTransaction(const char *gid, bool isCommit) --- 1355,1361 ---- int ndelrels; SharedInvalidationMessage *invalmsgs; int i; + TimestampTz lastXactTime = -1; /* * Validate the GID, and lock the GXACT to ensure that two backends do not *************** *** 1404,1414 **** FinishPreparedTransaction(const char *gid, bool isCommit) hdr->nsubxacts, children, hdr->ncommitrels, commitrels, hdr->ninvalmsgs, invalmsgs, ! hdr->initfileinval); else RecordTransactionAbortPrepared(xid, hdr->nsubxacts, children, ! hdr->nabortrels, abortrels); ProcArrayRemove(proc, latestXid); --- 1407,1419 ---- hdr->nsubxacts, children, hdr->ncommitrels, commitrels, hdr->ninvalmsgs, invalmsgs, ! hdr->initfileinval, ! &lastXactTime); else RecordTransactionAbortPrepared(xid, hdr->nsubxacts, children, ! hdr->nabortrels, abortrels, ! &lastXactTime); ProcArrayRemove(proc, latestXid); *************** *** 1470,1475 **** FinishPreparedTransaction(const char *gid, bool isCommit) --- 1475,1482 ---- /* Count the prepared xact as committed or aborted */ AtEOXact_PgStat(isCommit); + pgstat_report_xact_timestamp(-1, lastXactTime); + /* * And now we can clean up our mess. */ *************** *** 2068,2074 **** RecordTransactionCommitPrepared(TransactionId xid, RelFileNode *rels, int ninvalmsgs, SharedInvalidationMessage *invalmsgs, ! bool initfileinval) { XLogRecData rdata[4]; int lastrdata = 0; --- 2075,2082 ---- RelFileNode *rels, int ninvalmsgs, SharedInvalidationMessage *invalmsgs, ! bool initfileinval, ! TimestampTz *lastXactTime) { XLogRecData rdata[4]; int lastrdata = 0; *************** *** 2126,2131 **** RecordTransactionCommitPrepared(TransactionId xid, --- 2134,2140 ---- rdata[lastrdata].next = NULL; recptr = XLogInsert(RM_XACT_ID, XLOG_XACT_COMMIT_PREPARED, rdata); + *lastXactTime = xlrec.crec.xact_time; /* * We don't currently try to sleep before flush here ... nor is there any *************** *** 2166,2172 **** RecordTransactionAbortPrepared(TransactionId xid, int nchildren, TransactionId *children, int nrels, ! RelFileNode *rels) { XLogRecData rdata[3]; int lastrdata = 0; --- 2175,2182 ---- int nchildren, TransactionId *children, int nrels, ! RelFileNode *rels, ! TimestampTz *lastXactTime) { XLogRecData rdata[3]; int lastrdata = 0; *************** *** 2212,2217 **** RecordTransactionAbortPrepared(TransactionId xid, --- 2222,2228 ---- rdata[lastrdata].next = NULL; recptr = XLogInsert(RM_XACT_ID, XLOG_XACT_ABORT_PREPARED, rdata); + *lastXactTime = xlrec.arec.xact_time; /* Always flush, since we're about to remove the 2PC state file */ XLogFlush(recptr); *** a/src/backend/access/transam/xact.c --- b/src/backend/access/transam/xact.c *************** *** 272,278 **** static void CleanupTransaction(void); static void CheckTransactionChain(bool isTopLevel, bool throwError, const char *stmtType); static void CommitTransaction(void); ! static TransactionId RecordTransactionAbort(bool isSubXact); static void StartTransaction(void); static void StartSubTransaction(void); --- 272,279 ---- static void CheckTransactionChain(bool isTopLevel, bool throwError, const char *stmtType); static void CommitTransaction(void); ! static TransactionId RecordTransactionAbort(bool isSubXact, ! TimestampTz *lastXactTime); static void StartTransaction(void); static void StartSubTransaction(void); *************** *** 992,998 **** AtSubStart_ResourceOwner(void) * if the xact has no XID. (We compute that here just because it's easier.) */ static TransactionId ! RecordTransactionCommit(void) { TransactionId xid = GetTopTransactionIdIfAny(); bool markXidCommitted = TransactionIdIsValid(xid); --- 993,999 ---- * if the xact has no XID. (We compute that here just because it's easier.) */ static TransactionId ! RecordTransactionCommit(TimestampTz *lastXactTime) { TransactionId xid = GetTopTransactionIdIfAny(); bool markXidCommitted = TransactionIdIsValid(xid); *************** *** 1138,1143 **** RecordTransactionCommit(void) --- 1139,1145 ---- rdata[lastrdata].next = NULL; (void) XLogInsert(RM_XACT_ID, XLOG_XACT_COMMIT, rdata); + *lastXactTime = xlrec.xact_time; } else { *************** *** 1162,1167 **** RecordTransactionCommit(void) --- 1164,1170 ---- rdata[lastrdata].next = NULL; (void) XLogInsert(RM_XACT_ID, XLOG_XACT_COMMIT_COMPACT, rdata); + *lastXactTime = xlrec.xact_time; } } *************** *** 1426,1432 **** AtSubCommit_childXids(void) * if the xact has no XID. (We compute that here just because it's easier.) */ static TransactionId ! RecordTransactionAbort(bool isSubXact) { TransactionId xid = GetCurrentTransactionIdIfAny(); TransactionId latestXid; --- 1429,1435 ---- * if the xact has no XID. (We compute that here just because it's easier.) */ static TransactionId ! RecordTransactionAbort(bool isSubXact, TimestampTz *lastXactTime) { TransactionId xid = GetCurrentTransactionIdIfAny(); TransactionId latestXid; *************** *** 1508,1513 **** RecordTransactionAbort(bool isSubXact) --- 1511,1517 ---- rdata[lastrdata].next = NULL; (void) XLogInsert(RM_XACT_ID, XLOG_XACT_ABORT, rdata); + *lastXactTime = xlrec.xact_time; /* * Report the latest async abort LSN, so that the WAL writer knows to *************** *** 1818,1824 **** StartTransaction(void) */ xactStartTimestamp = stmtStartTimestamp; xactStopTimestamp = 0; ! pgstat_report_xact_timestamp(xactStartTimestamp); /* * initialize current transaction state fields --- 1822,1828 ---- */ xactStartTimestamp = stmtStartTimestamp; xactStopTimestamp = 0; ! pgstat_report_xact_timestamp(xactStartTimestamp, -1); /* * initialize current transaction state fields *************** *** 1862,1867 **** CommitTransaction(void) --- 1866,1872 ---- { TransactionState s = CurrentTransactionState; TransactionId latestXid; + TimestampTz lastXactTime = -1; ShowTransactionState("CommitTransaction"); *************** *** 1945,1951 **** CommitTransaction(void) /* * Here is where we really truly commit. */ ! latestXid = RecordTransactionCommit(); TRACE_POSTGRESQL_TRANSACTION_COMMIT(MyProc->lxid); --- 1950,1956 ---- /* * Here is where we really truly commit. */ ! latestXid = RecordTransactionCommit(&lastXactTime); TRACE_POSTGRESQL_TRANSACTION_COMMIT(MyProc->lxid); *************** *** 2027,2033 **** CommitTransaction(void) AtEOXact_HashTables(true); AtEOXact_PgStat(true); AtEOXact_Snapshot(true); ! pgstat_report_xact_timestamp(0); CurrentResourceOwner = NULL; ResourceOwnerDelete(TopTransactionResourceOwner); --- 2032,2038 ---- AtEOXact_HashTables(true); AtEOXact_PgStat(true); AtEOXact_Snapshot(true); ! pgstat_report_xact_timestamp(0, lastXactTime); CurrentResourceOwner = NULL; ResourceOwnerDelete(TopTransactionResourceOwner); *************** *** 2294,2300 **** PrepareTransaction(void) AtEOXact_HashTables(true); /* don't call AtEOXact_PgStat here; we fixed pgstat state above */ AtEOXact_Snapshot(true); ! pgstat_report_xact_timestamp(0); CurrentResourceOwner = NULL; ResourceOwnerDelete(TopTransactionResourceOwner); --- 2299,2305 ---- AtEOXact_HashTables(true); /* don't call AtEOXact_PgStat here; we fixed pgstat state above */ AtEOXact_Snapshot(true); ! pgstat_report_xact_timestamp(0, -1); CurrentResourceOwner = NULL; ResourceOwnerDelete(TopTransactionResourceOwner); *************** *** 2330,2335 **** AbortTransaction(void) --- 2335,2341 ---- { TransactionState s = CurrentTransactionState; TransactionId latestXid; + TimestampTz lastXactTime = -1; /* Prevent cancel/die interrupt while cleaning up */ HOLD_INTERRUPTS(); *************** *** 2412,2418 **** AbortTransaction(void) * Advertise the fact that we aborted in pg_clog (assuming that we got as * far as assigning an XID to advertise). */ ! latestXid = RecordTransactionAbort(false); TRACE_POSTGRESQL_TRANSACTION_ABORT(MyProc->lxid); --- 2418,2424 ---- * Advertise the fact that we aborted in pg_clog (assuming that we got as * far as assigning an XID to advertise). */ ! latestXid = RecordTransactionAbort(false, &lastXactTime); TRACE_POSTGRESQL_TRANSACTION_ABORT(MyProc->lxid); *************** *** 2457,2464 **** AbortTransaction(void) AtEOXact_ComboCid(); AtEOXact_HashTables(false); AtEOXact_PgStat(false); ! pgstat_report_xact_timestamp(0); } /* * State remains TRANS_ABORT until CleanupTransaction(). --- 2463,2472 ---- AtEOXact_ComboCid(); AtEOXact_HashTables(false); AtEOXact_PgStat(false); ! pgstat_report_xact_timestamp(0, lastXactTime); } + else + pgstat_report_xact_timestamp(-1, lastXactTime); /* * State remains TRANS_ABORT until CleanupTransaction(). *************** *** 4277,4282 **** static void --- 4285,4291 ---- AbortSubTransaction(void) { TransactionState s = CurrentTransactionState; + TimestampTz lastXactTime = -1; /* Prevent cancel/die interrupt while cleaning up */ HOLD_INTERRUPTS(); *************** *** 4353,4359 **** AbortSubTransaction(void) AtSubAbort_Notify(); /* Advertise the fact that we aborted in pg_clog. */ ! (void) RecordTransactionAbort(true); /* Post-abort cleanup */ if (TransactionIdIsValid(s->transactionId)) --- 4362,4368 ---- AtSubAbort_Notify(); /* Advertise the fact that we aborted in pg_clog. */ ! (void) RecordTransactionAbort(true, &lastXactTime); /* Post-abort cleanup */ if (TransactionIdIsValid(s->transactionId)) *************** *** 4387,4392 **** AbortSubTransaction(void) --- 4396,4402 ---- AtEOSubXact_HashTables(false, s->nestingLevel); AtEOSubXact_PgStat(false, s->nestingLevel); AtSubAbort_Snapshot(s->nestingLevel); + pgstat_report_xact_timestamp(-1, lastXactTime); } /* *** a/src/backend/access/transam/xlogfuncs.c --- b/src/backend/access/transam/xlogfuncs.c *************** *** 25,30 **** --- 25,31 ---- #include "catalog/pg_type.h" #include "funcapi.h" #include "miscadmin.h" + #include "pgstat.h" #include "replication/walreceiver.h" #include "storage/smgr.h" #include "utils/builtins.h" *************** *** 206,211 **** pg_current_xlog_insert_location(PG_FUNCTION_ARGS) --- 207,247 ---- } /* + * 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 result = 0; + TimestampTz xtime; + LocalPgBackendStatus *local_beentry; + int i; + + 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."))); + + local_beentry = pgstat_fetch_stat_all_local_beentry(); + + for (i = 0; i < MaxBackends; i++, local_beentry++) + { + xtime = local_beentry->backendStatus.st_xact_end_timestamp; + if (result < xtime) + result = xtime; + } + + if (result == 0) + PG_RETURN_NULL(); + + PG_RETURN_TIMESTAMPTZ(result); + } + + /* * Report the last WAL receive location (same format as pg_start_backup etc) * * This is useful for determining how much of WAL is guaranteed to be received *** a/src/backend/postmaster/pgstat.c --- b/src/backend/postmaster/pgstat.c *************** *** 262,268 **** static void pgstat_write_db_statsfile(PgStat_StatDBEntry *dbentry, bool permanen static HTAB *pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep); static void pgstat_read_db_statsfile(Oid databaseid, HTAB *tabhash, HTAB *funchash, bool permanent); static void backend_read_statsfile(void); ! static void pgstat_read_current_status(void); static bool pgstat_write_statsfile_needed(void); static bool pgstat_db_requested(Oid databaseid); --- 262,268 ---- static HTAB *pgstat_read_statsfiles(Oid onlydb, bool permanent, bool deep); static void pgstat_read_db_statsfile(Oid databaseid, HTAB *tabhash, HTAB *funchash, bool permanent); static void backend_read_statsfile(void); ! static void pgstat_read_current_status(bool all); static bool pgstat_write_statsfile_needed(void); static bool pgstat_db_requested(Oid databaseid); *************** *** 2298,2304 **** pgstat_fetch_stat_funcentry(Oid func_id) PgBackendStatus * pgstat_fetch_stat_beentry(int beid) { ! pgstat_read_current_status(); if (beid < 1 || beid > localNumBackends) return NULL; --- 2298,2304 ---- PgBackendStatus * pgstat_fetch_stat_beentry(int beid) { ! pgstat_read_current_status(false); if (beid < 1 || beid > localNumBackends) return NULL; *************** *** 2320,2326 **** pgstat_fetch_stat_beentry(int beid) LocalPgBackendStatus * pgstat_fetch_stat_local_beentry(int beid) { ! pgstat_read_current_status(); if (beid < 1 || beid > localNumBackends) return NULL; --- 2320,2326 ---- LocalPgBackendStatus * pgstat_fetch_stat_local_beentry(int beid) { ! pgstat_read_current_status(false); if (beid < 1 || beid > localNumBackends) return NULL; *************** *** 2330,2335 **** pgstat_fetch_stat_local_beentry(int beid) --- 2330,2354 ---- /* ---------- + * pgstat_fetch_stat_all_local_beentry() - + * + * Support function for the SQL-callable pgstat* functions. Returns + * our local copy of all backend entries. + * + * NB: caller is responsible for a check if the user is permitted to see + * this info (especially the querystring). + * ---------- + */ + LocalPgBackendStatus * + pgstat_fetch_stat_all_local_beentry(void) + { + pgstat_read_current_status(false); + + return localBackendStatusTable; + } + + + /* ---------- * pgstat_fetch_stat_numbackends() - * * Support function for the SQL-callable pgstat* functions. Returns *************** *** 2339,2349 **** pgstat_fetch_stat_local_beentry(int beid) int pgstat_fetch_stat_numbackends(void) { ! pgstat_read_current_status(); return localNumBackends; } /* * --------- * pgstat_fetch_stat_archiver() - --- 2358,2369 ---- int pgstat_fetch_stat_numbackends(void) { ! pgstat_read_current_status(false); return localNumBackends; } + /* * --------- * pgstat_fetch_stat_archiver() - *************** *** 2588,2593 **** pgstat_bestart(void) --- 2608,2618 ---- 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); *************** *** 2744,2754 **** pgstat_report_appname(const char *appname) } /* ! * Report current transaction start timestamp as the specified value. ! * Zero means there is no active transaction. */ void ! pgstat_report_xact_timestamp(TimestampTz tstamp) { volatile PgBackendStatus *beentry = MyBEEntry; --- 2769,2780 ---- } /* ! * Report current transaction start/end timestamp as the specified value. ! * Reporting transaction start/end timestamp as zero means there is no ! * active/finished transaction. */ void ! pgstat_report_xact_timestamp(TimestampTz startTime, TimestampTz endTime) { volatile PgBackendStatus *beentry = MyBEEntry; *************** *** 2761,2767 **** pgstat_report_xact_timestamp(TimestampTz tstamp) * ensure the compiler doesn't try to get cute. */ beentry->st_changecount++; ! beentry->st_xact_start_timestamp = tstamp; beentry->st_changecount++; Assert((beentry->st_changecount & 1) == 0); } --- 2787,2796 ---- * ensure the compiler doesn't try to get cute. */ beentry->st_changecount++; ! if (startTime >= 0) ! beentry->st_xact_start_timestamp = startTime; ! if (endTime >= 0) ! beentry->st_xact_end_timestamp = endTime; beentry->st_changecount++; Assert((beentry->st_changecount & 1) == 0); } *************** *** 2796,2806 **** pgstat_report_waiting(bool waiting) * pgstat_read_current_status() - * * Copy the current contents of the PgBackendStatus array to local memory, ! * if not already done in this transaction. * ---------- */ static void ! pgstat_read_current_status(void) { volatile PgBackendStatus *beentry; LocalPgBackendStatus *localtable; --- 2825,2836 ---- * pgstat_read_current_status() - * * Copy the current contents of the PgBackendStatus array to local memory, ! * if not already done in this transaction. If all is true, the local ! * array includes all entries. Otherwise, it includes only valid ones. * ---------- */ static void ! pgstat_read_current_status(bool all) { volatile PgBackendStatus *beentry; LocalPgBackendStatus *localtable; *************** *** 2842,2848 **** pgstat_read_current_status(void) int save_changecount = beentry->st_changecount; localentry->backendStatus.st_procpid = beentry->st_procpid; ! if (localentry->backendStatus.st_procpid > 0) { memcpy(&localentry->backendStatus, (char *) beentry, sizeof(PgBackendStatus)); --- 2872,2878 ---- int save_changecount = beentry->st_changecount; localentry->backendStatus.st_procpid = beentry->st_procpid; ! if (localentry->backendStatus.st_procpid > 0 || all) { memcpy(&localentry->backendStatus, (char *) beentry, sizeof(PgBackendStatus)); *************** *** 2865,2872 **** pgstat_read_current_status(void) } beentry++; ! /* Only valid entries get included into the local array */ ! if (localentry->backendStatus.st_procpid > 0) { BackendIdGetTransactionIds(i, &localentry->backend_xid, --- 2895,2902 ---- } beentry++; ! /* Only valid entries get included into the local array if all is false */ ! if (localentry->backendStatus.st_procpid > 0 || all) { BackendIdGetTransactionIds(i, &localentry->backend_xid, *** a/src/include/access/xlog_fn.h --- b/src/include/access/xlog_fn.h *************** *** 21,26 **** extern Datum pg_current_xlog_location(PG_FUNCTION_ARGS); --- 21,27 ---- 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 *************** *** 3038,3043 **** DATA(insert OID = 2850 ( pg_xlogfile_name_offset PGNSP PGUID 12 1 0 0 0 f f f f --- 3038,3045 ---- 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 f t f i 1 0 25 "3220" _null_ _null_ _null_ _null_ pg_xlogfile_name _null_ _null_ _null_ )); DESCR("xlog filename, given an xlog location"); + DATA(insert OID = 3218 ( pg_last_xact_insert_timestamp PGNSP PGUID 12 1 0 0 0 f 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 = 3165 ( pg_xlog_location_diff PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1700 "3220 3220" _null_ _null_ _null_ _null_ pg_xlog_location_diff _null_ _null_ _null_ )); DESCR("difference in bytes, given two xlog locations"); *** a/src/include/pgstat.h --- b/src/include/pgstat.h *************** *** 726,731 **** typedef struct PgBackendStatus --- 726,734 ---- TimestampTz st_activity_start_timestamp; TimestampTz st_state_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; *************** *** 860,866 **** extern void pgstat_bestart(void); extern void pgstat_report_activity(BackendState state, const char *cmd_str); extern void pgstat_report_tempfile(size_t filesize); extern void pgstat_report_appname(const char *appname); ! extern void pgstat_report_xact_timestamp(TimestampTz tstamp); extern void pgstat_report_waiting(bool waiting); extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser); extern const char *pgstat_get_crashed_backend_activity(int pid, char *buffer, --- 863,871 ---- extern void pgstat_report_activity(BackendState state, const char *cmd_str); extern void pgstat_report_tempfile(size_t filesize); extern void pgstat_report_appname(const char *appname); ! extern void pgstat_report_xact_timestamp(TimestampTz startTime, ! TimestampTz endTime); ! 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 const char *pgstat_get_crashed_backend_activity(int pid, char *buffer, *************** *** 946,951 **** extern PgStat_StatDBEntry *pgstat_fetch_stat_dbentry(Oid dbid); --- 951,957 ---- extern PgStat_StatTabEntry *pgstat_fetch_stat_tabentry(Oid relid); extern PgBackendStatus *pgstat_fetch_stat_beentry(int beid); extern LocalPgBackendStatus *pgstat_fetch_stat_local_beentry(int beid); + extern LocalPgBackendStatus *pgstat_fetch_stat_all_local_beentry(void); extern PgStat_StatFuncEntry *pgstat_fetch_stat_funcentry(Oid funcid); extern int pgstat_fetch_stat_numbackends(void); extern PgStat_ArchiverStats *pgstat_fetch_stat_archiver(void);
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers