[Todo item] Add entry creation timestamp column to pg_stat_replication

Hello hackers,

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.

test example>
postgres=# select pid, reply_time from pg_stat_replication;
-[ RECORD 1 ]-----------------------------
pid | 4817
reply_time | 2018-07-31 12:00:53.911198+09
-[ RECORD 2 ]-----------------------------
pid | 4819
reply_time | 2018-07-31 12:00:53.911154+09


Several candidates exist for the field name.
- reply_timestamp
- info_gen_timestamp
- stats_reset
- last_msg_send_time

Feedback and suggestion will be very welcome.
Thanks!

Best regards,
Myungkyu, Lim

I changed field name from 'reply_time' to 'last_msg_send_time'.
Because 'last_msg_send_time' is used in pg_stat_wal_receiver/pg_stat_subsctiption view.
I think that field has the same meaning.

test example>
postgres=# select pid, last_msg_send_time from pg_stat_replication;
-[ RECORD 1 ]------+------------------------------
pid | 12015
last_msg_send_time | 2018-08-02 18:02:49.233049+09
-[ RECORD 2 ]------+------------------------------
pid | 12084
last_msg_send_time | 2018-08-02 18:02:48.583256+09

I Attached new patch file : 0001-Implement-following-TODO-list-item-v2.patch

Feedback and suggestion will be very welcome.
Thanks!

Best regards,
Myungkyu, Lim
 
--------- Original Message ---------
Date : 2018-07-31 17:56 (GMT+9)
Title : [Todo item] Add entry creation timestamp column to pg_stat_replication
 
Hello hackers,
 
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.
 
test example>
postgres=# select pid, reply_time from pg_stat_replication;
-[ RECORD 1 ]-----------------------------
pid        | 4817
reply_time | 2018-07-31 12:00:53.911198+09
-[ RECORD 2 ]-----------------------------
pid        | 4819
reply_time | 2018-07-31 12:00:53.911154+09
 
 
Several candidates exist for the field name.
    - reply_timestamp
    - info_gen_timestamp
    - stats_reset
    - last_msg_send_time
 
Feedback and suggestion will be very welcome.
Thanks!
 
Best regards,
Myungkyu, Lim

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

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(dot)albe(at)cybertec(dot)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
 
 
 

Thanks for your feedback and info!

I registered patch in commit fest.
https://commitfest.postgresql.org/20/1841/

For the record, replied on original thread.

Best regards,
Myungkyu, Lim

-----Original Message-----
From: Michael Paquier [mailto:michael(at)paquier(dot)xyz]
Sent: Thursday, October 25, 2018 8:50 PM
To: Laurenz Albe
Cc: myungkyu(dot)lim(at)samsung(dot)com; pgsql-hackers(at)postgresql(dot)org
Subject: Re: pg_stat_replication vs StandbyReplyMessage

On Thu, Oct 25, 2018 at 08:13:53AM +0200, Laurenz Albe wrote:
> MyungKyu LIM wrote:
>> I saw this topic in todo list,
>>
>> so I implemented simple patch.
>>
>> https://www.postgresql.org/message-id/flat/1657809367.407321.15330274
>> 17725.JavaMail.jboss%40ep2ml404
>
> For the archives' sake, please always reply on the original thread.

And if you could add some documentation into the patch, and register it to the commit fest if you would like to get it reviewed, that would be nice. Here are some general guidelines on the matter:
https://wiki.postgresql.org/wiki/Submitting_a_Patch
--
Michael

Hi .
On Tue, Jul 31, 2018 at 11:57 AM MyungKyu LIM <myungkyu(dot)lim(at)samsung(dot)com>
wrote:

>
> Feedback and suggestion will be very welcome.
>

Can you explain the purpose of this feature more because now we have
columns to report replication delay times like write_lag ,flush_lag and
replay_lag that can use for similar purpose .

regards

Surafel

Hi.
Thanks for your feedback.

> Can you explain the purpose of this feature more because now we have columns to report replication delay times like write_lag ,flush_lag and replay_lag that can use for similar purpose .

I think, time elapsed stats are very useful on DML query active system,
but not present that stats on idle system - not query, or only select.

sent_lsn | 0/5476C88
write_lsn | 0/5476C88
flush_lsn | 0/5476C88
replay_lsn | 0/5476C88
write_lag | 00:00:00.000055
flush_lag | 00:00:00.000855
replay_lag | 00:00:00.000914
sync_priority | 0
sync_state | async
last_msg_send_time | 2018-11-15 14:04:39.65889+09

state | streaming
sent_lsn | 0/5476CC0
write_lsn | 0/5476CC0
flush_lsn | 0/5476CC0
replay_lsn | 0/5476CC0
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
last_msg_send_time | 2018-11-15 14:05:02.935457+09

state | streaming
sent_lsn | 0/5476CC0
write_lsn | 0/5476CC0
flush_lsn | 0/5476CC0
replay_lsn | 0/5476CC0
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
last_msg_send_time | 2018-11-15 14:06:23.128947+09

This timestamp column is useful when react interval check and debugging on idle system.

Best regards,
Myungkyu, Lim

On Thu, Aug 2, 2018 at 6:34 PM MyungKyu LIM <myungkyu(dot)lim(at)samsung(dot)com> wrote:
>
> I changed field name from 'reply_time' to 'last_msg_send_time'.
> Because 'last_msg_send_time' is used in pg_stat_wal_receiver/pg_stat_subsctiption view.
> I think that field has the same meaning.

I got confused by the field name. If we have 'last_msg_send_time'
field in pg_stat_replciation which has information of wal senders
users would think it as a time when the wal sender sent a message last
time. However values of the fields actually shows a time when the wal
receiver sent a reply message last time. So perhaps
'last_reply_send_time' would be more clear.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center