pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: MyungKyu LIM <myungkyu.lim@samsung.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
To: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: RE: [Todo item] Add entry creation timestamp column to pg_stat_replication
Date: Thu, 25 Oct 2018 16:28:13 +0900
Message-ID: <2037111623.586731.1540452493623.JavaMail.jboss@ep2ml404> (raw )
In-Reply-To: <5e6d20066860e079803db48bd6b969c741374f35.camel@cybertec.at >
References: <5e6d20066860e079803db48bd6b969c741374f35.camel@cybertec.at >
<1657809367.407321.1533027417725.JavaMail.jboss@ep2ml404 >
<CGME20180731085657epcms4p86e41bb64a978775023ef8cb77535ef44@epcms4p7 >
Thanks for your feedback!
Include documentation for new column.
Attached new patch file
: 0001-Implement-following-TODO-list-item-v3.patch
Best regards,
Myungkyu, Lim
--------- Original Message ---------
Sender : Laurenz Albe <laurenz.albe@cybertec.at>
Date : 2018-10-25 15:14 (GMT+9)
Title : Re: [Todo item] Add entry creation timestamp column to pg_stat_replication
MyungKyu LIM wrote:
> I have worked on following todo list item.
>
> - Add entry creation timestamp column to pg_stat_replication
> http://archives.postgresql.org/pgsql-hackers/2011-08/msg00694.php
>
> This item looks like simple because necessary data was already exist.
> So, I wrote a prototype patch.
Thank you!
You should add this to the next commitfest:
https://commitfest.postgresql.org/20/
Please make sure to read the Developer FAQ if you haven't already done it:
https://wiki.postgresql.org/wiki/Developer_FAQ
Yours,
Laurenz Albe
Attachments:
[application/octet-stream] 0001-Implement-following-TODO-list-item-v3.patch (7.9K, ../2037111623.586731.1540452493623.JavaMail.jboss@ep2ml404/2-0001-Implement-following-TODO-list-item-v3.patch)
download | inline diff:
From addffcd2dd6e69e534a3f2bd903986498ae58ff4 Mon Sep 17 00:00:00 2001
From: "MyungKyu, Lim" <myungkyu.lim@samsung.com>
Date: Thu, 25 Oct 2018 16:09:29 +0900
Subject: [PATCH] Implement following TODO list item
: Add entry creation timestamp column to pg_stat_replication
mailing list : https://www.postgresql.org/message-id/flat/1657809367.407321.1533027417725.JavaMail.jboss%40ep2ml404
todo page : https://wiki.postgresql.org/wiki/Todo#Monitoring
---
doc/src/sgml/monitoring.sgml | 5 +++++
src/backend/catalog/system_views.sql | 3 ++-
src/backend/replication/walsender.c | 25 +++++++++++++++++++++----
src/include/catalog/pg_proc.dat | 6 +++---
src/include/replication/walsender_private.h | 5 +++++
src/test/regress/expected/rules.out | 5 +++--
6 files changed, 39 insertions(+), 10 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index add7145..4fadd1c 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -1916,6 +1916,11 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
</itemizedlist>
</entry>
</row>
+ <row>
+ <entry><structfield>last_msg_send_time</structfield></entry>
+ <entry><type>timestamp with time zone</type></entry>
+ <entry>Send time of last message received from WAL receiver</entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 53ddc59..5179234 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -734,7 +734,8 @@ CREATE VIEW pg_stat_replication AS
W.flush_lag,
W.replay_lag,
W.sync_priority,
- W.sync_state
+ W.sync_state,
+ W.last_msg_send_time
FROM pg_stat_get_activity(NULL) AS S
JOIN pg_stat_get_wal_senders() AS W ON (S.pid = W.pid)
LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);
diff --git a/src/backend/replication/walsender.c b/src/backend/replication/walsender.c
index 2683385..0f2af3f 100644
--- a/src/backend/replication/walsender.c
+++ b/src/backend/replication/walsender.c
@@ -1777,6 +1777,8 @@ ProcessStandbyReplyMessage(void)
applyLag;
bool clearLagTimes;
TimestampTz now;
+ TimestampTz sendTimestamp;
+ char *sendTime;
static bool fullyAppliedLastTime = false;
@@ -1784,14 +1786,20 @@ ProcessStandbyReplyMessage(void)
writePtr = pq_getmsgint64(&reply_message);
flushPtr = pq_getmsgint64(&reply_message);
applyPtr = pq_getmsgint64(&reply_message);
- (void) pq_getmsgint64(&reply_message); /* sendTime; not used ATM */
+ sendTimestamp = pq_getmsgint64(&reply_message); /* sendTime */
replyRequested = pq_getmsgbyte(&reply_message);
- elog(DEBUG2, "write %X/%X flush %X/%X apply %X/%X%s",
+ /* Copy because timestamptz_to_str returns a static buffer */
+ sendTime = pstrdup(timestamptz_to_str(sendTimestamp));
+
+ elog(DEBUG2, "write %X/%X flush %X/%X apply %X/%X%s replytime %s",
(uint32) (writePtr >> 32), (uint32) writePtr,
(uint32) (flushPtr >> 32), (uint32) flushPtr,
(uint32) (applyPtr >> 32), (uint32) applyPtr,
- replyRequested ? " (reply requested)" : "");
+ replyRequested ? " (reply requested)" : "",
+ sendTime);
+
+ pfree(sendTime);
/* See if we can compute the round-trip lag for these positions. */
now = GetCurrentTimestamp();
@@ -1838,6 +1846,7 @@ ProcessStandbyReplyMessage(void)
walsnd->flushLag = flushLag;
if (applyLag != -1 || clearLagTimes)
walsnd->applyLag = applyLag;
+ walsnd->lastMsgSendTime = sendTimestamp;
SpinLockRelease(&walsnd->mutex);
}
@@ -2286,6 +2295,7 @@ InitWalSenderSlot(void)
walsnd->applyLag = -1;
walsnd->state = WALSNDSTATE_STARTUP;
walsnd->latch = &MyProc->procLatch;
+ walsnd->lastMsgSendTime = 0;
SpinLockRelease(&walsnd->mutex);
/* don't need the lock anymore */
MyWalSnd = (WalSnd *) walsnd;
@@ -3204,7 +3214,7 @@ offset_to_interval(TimeOffset offset)
Datum
pg_stat_get_wal_senders(PG_FUNCTION_ARGS)
{
-#define PG_STAT_GET_WAL_SENDERS_COLS 11
+#define PG_STAT_GET_WAL_SENDERS_COLS 12
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
TupleDesc tupdesc;
Tuplestorestate *tupstore;
@@ -3258,6 +3268,7 @@ pg_stat_get_wal_senders(PG_FUNCTION_ARGS)
int priority;
int pid;
WalSndState state;
+ TimestampTz last_send_time;
Datum values[PG_STAT_GET_WAL_SENDERS_COLS];
bool nulls[PG_STAT_GET_WAL_SENDERS_COLS];
@@ -3277,6 +3288,7 @@ pg_stat_get_wal_senders(PG_FUNCTION_ARGS)
flushLag = walsnd->flushLag;
applyLag = walsnd->applyLag;
priority = walsnd->sync_standby_priority;
+ last_send_time = walsnd->lastMsgSendTime;
SpinLockRelease(&walsnd->mutex);
memset(nulls, 0, sizeof(nulls));
@@ -3353,6 +3365,11 @@ pg_stat_get_wal_senders(PG_FUNCTION_ARGS)
CStringGetTextDatum("sync") : CStringGetTextDatum("quorum");
else
values[10] = CStringGetTextDatum("potential");
+
+ if (last_send_time == 0)
+ nulls[11] = true;
+ else
+ values[11] = TimestampTzGetDatum(last_send_time);
}
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 4d7fe1b..c717409 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5014,9 +5014,9 @@
proname => 'pg_stat_get_wal_senders', prorows => '10', proisstrict => 'f',
proretset => 't', provolatile => 's', proparallel => 'r',
prorettype => 'record', proargtypes => '',
- proallargtypes => '{int4,text,pg_lsn,pg_lsn,pg_lsn,pg_lsn,interval,interval,interval,int4,text}',
- proargmodes => '{o,o,o,o,o,o,o,o,o,o,o}',
- proargnames => '{pid,state,sent_lsn,write_lsn,flush_lsn,replay_lsn,write_lag,flush_lag,replay_lag,sync_priority,sync_state}',
+ proallargtypes => '{int4,text,pg_lsn,pg_lsn,pg_lsn,pg_lsn,interval,interval,interval,int4,text,timestamptz}',
+ proargmodes => '{o,o,o,o,o,o,o,o,o,o,o,o}',
+ proargnames => '{pid,state,sent_lsn,write_lsn,flush_lsn,replay_lsn,write_lag,flush_lag,replay_lag,sync_priority,sync_state,last_msg_send_time}',
prosrc => 'pg_stat_get_wal_senders' },
{ oid => '3317', descr => 'statistics: information about WAL receiver',
proname => 'pg_stat_get_wal_receiver', proisstrict => 'f', provolatile => 's',
diff --git a/src/include/replication/walsender_private.h b/src/include/replication/walsender_private.h
index 4b90477..057bcb4 100644
--- a/src/include/replication/walsender_private.h
+++ b/src/include/replication/walsender_private.h
@@ -75,6 +75,11 @@ typedef struct WalSnd
* SyncRepLock.
*/
int sync_standby_priority;
+
+ /*
+ * timestamp of the latest message, reported by standby server
+ */
+ TimestampTz lastMsgSendTime;
} WalSnd;
extern WalSnd *MyWalSnd;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 735dd37..9037681 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1861,9 +1861,10 @@ pg_stat_replication| SELECT s.pid,
w.flush_lag,
w.replay_lag,
w.sync_priority,
- w.sync_state
+ w.sync_state,
+ w.last_msg_send_time
FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn)
- JOIN pg_stat_get_wal_senders() w(pid, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag, sync_priority, sync_state) ON ((s.pid = w.pid)))
+ JOIN pg_stat_get_wal_senders() w(pid, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag, sync_priority, sync_state, last_msg_send_time) ON ((s.pid = w.pid)))
LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
pg_stat_ssl| SELECT s.pid,
s.ssl,
--
1.8.3.1
view thread (22+ messages) latest in thread
Message-ID: <2037111623.586731.1540452493623.JavaMail.jboss@ep2ml404>
Permalink: ../2037111623.586731.1540452493623.JavaMail.jboss@ep2ml404/
Also on: postgresql.org/message-id/2037111623.586731.1540452493623.JavaMail.jboss@ep2ml404
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: myungkyu.lim@samsung.com, laurenz.albe@cybertec.at
Subject: RE: [Todo item] Add entry creation timestamp column to pg_stat_replication
In-Reply-To: <2037111623.586731.1540452493623.JavaMail.jboss@ep2ml404>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox