pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: myungkyu.lim <myungkyu.lim@samsung.com>
To: 'Michael Paquier' <michael@paquier.xyz>
Cc: 'Masahiko Sawada' <sawada.mshk@gmail.com>
Cc: 'PostgreSQL-development' <pgsql-hackers@postgresql.org>
Cc: woosung.sohn@samsung.com
Cc: don.hong@samsung.com
Subject: RE: [Todo item] Add entry creation timestamp column to pg_stat_replication
Date: Thu, 29 Nov 2018 17:43:26 +0900
Message-ID: <00d401d487bf$98727c10$c9577430$@samsung.com> (raw )
In-Reply-To: <20181129070020.GD2356@paquier.xyz >
References: <CGME20181115080226epcas4p1f81c51691d9f12f7fa94c198e84edb3a@epcas4p1.samsung.com >
<007701d47cb9$8f414380$adc3ca80$@samsung.com >
<20181115082748.GG1374@paquier.xyz >
<CAD21AoCgJ4asOa2qP5DQiyzHBJ+Dhhi8Z5kVVokS-R7HxsmtfA@mail.gmail.com >
<20181122075321.GJ3369@paquier.xyz >
<010201d4854b$ae87e510$0b97af30$@samsung.com >
<20181129070020.GD2356@paquier.xyz >
>On Mon, Nov 26, 2018 at 02:48:39PM +0900, myungkyu.lim wrote:
>> So I selected 'reply_time' because 'timestamp' was not used in stat
>> field.
>
>Fine by me. Could you send a new patch please? I am switching the patch
as
>waiting on author for now.
Ok.
Changed field name 'last_msg_send_time' to 'reply_time'.
Attached new patch file : 0001-Implement-following-TODO-list-item-v4.patch
example>
postgres=# select pid, reply_time from pg_stat_replication;
-[ RECORD 1 ]-----------------------------
pid | 10493
reply_time | 2018-11-29 17:37:14.638428+09
check plz.
Best regards,
Myungkyu, Lim
Attachments:
0001-Implement-following-TODO-list-item-v4.patch (application/octet-stream, 7.8K) download
From 93a81fece48acd8e678393b852dc819a4277cc16 Mon Sep 17 00:00:00 2001
From: "MyungKyu, Lim" <myungkyu.lim@samsung.com>
Date: Thu, 29 Nov 2018 17:29:10 +0900
Subject: [PATCH] [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 7aada14..0d8d35b 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -1920,6 +1920,11 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
</itemizedlist>
</entry>
</row>
+ <row>
+ <entry><structfield>reply_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 715995d..8630542 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.reply_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 46edb52..3bf0c58 100644
--- a/src/backend/replication/walsender.c
+++ b/src/backend/replication/walsender.c
@@ -1763,6 +1763,8 @@ ProcessStandbyReplyMessage(void)
applyLag;
bool clearLagTimes;
TimestampTz now;
+ TimestampTz sendTimestamp;
+ char *sendTime;
static bool fullyAppliedLastTime = false;
@@ -1770,14 +1772,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 by standby */
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();
@@ -1824,6 +1832,7 @@ ProcessStandbyReplyMessage(void)
walsnd->flushLag = flushLag;
if (applyLag != -1 || clearLagTimes)
walsnd->applyLag = applyLag;
+ walsnd->replyTime = sendTimestamp;
SpinLockRelease(&walsnd->mutex);
}
@@ -2265,6 +2274,7 @@ InitWalSenderSlot(void)
walsnd->applyLag = -1;
walsnd->state = WALSNDSTATE_STARTUP;
walsnd->latch = &MyProc->procLatch;
+ walsnd->replyTime = 0;
SpinLockRelease(&walsnd->mutex);
/* don't need the lock anymore */
MyWalSnd = (WalSnd *) walsnd;
@@ -3179,7 +3189,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;
@@ -3233,6 +3243,7 @@ pg_stat_get_wal_senders(PG_FUNCTION_ARGS)
int priority;
int pid;
WalSndState state;
+ TimestampTz replyTime;
Datum values[PG_STAT_GET_WAL_SENDERS_COLS];
bool nulls[PG_STAT_GET_WAL_SENDERS_COLS];
@@ -3252,6 +3263,7 @@ pg_stat_get_wal_senders(PG_FUNCTION_ARGS)
flushLag = walsnd->flushLag;
applyLag = walsnd->applyLag;
priority = walsnd->sync_standby_priority;
+ replyTime = walsnd->replyTime;
SpinLockRelease(&walsnd->mutex);
memset(nulls, 0, sizeof(nulls));
@@ -3328,6 +3340,11 @@ pg_stat_get_wal_senders(PG_FUNCTION_ARGS)
CStringGetTextDatum("sync") : CStringGetTextDatum("quorum");
else
values[10] = CStringGetTextDatum("potential");
+
+ if (replyTime == 0)
+ nulls[11] = true;
+ else
+ values[11] = TimestampTzGetDatum(replyTime);
}
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 034a41e..f79fcfe 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5023,9 +5023,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,reply_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..73a09a6 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 replyTime;
} WalSnd;
extern WalSnd *MyWalSnd;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 735dd37..b68b8d2 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.reply_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, reply_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: <00d401d487bf$98727c10$c9577430$@samsung.com>
Permalink: ../00d401d487bf$98727c10$c9577430$@samsung.com/
Also on: postgresql.org/message-id/00d401d487bf$98727c10$c9577430$@samsung.com
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, michael@paquier.xyz, sawada.mshk@gmail.com, woosung.sohn@samsung.com, don.hong@samsung.com
Subject: RE: [Todo item] Add entry creation timestamp column to pg_stat_replication
In-Reply-To: <00d401d487bf$98727c10$c9577430$@samsung.com>
* 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