[pgpool-hackers: 4106] Re: Proposal: allow to specify replication delay by time

[Tatsuo Ishii]

>> Currently delay_threshold specifies replication delay upper limit in
>> bytes. It will be more intuitive to specify the replication delay by
>> time like '10 seconds'. For this purpose, we can use
>> (pg_last_committed_xact()).timestamp instead of pg_current_wal_lsn(),
>> and pg_last_xact_replay_timestamp() instead of
>> pg_last_wal_replay_lsn(). One thing we need to care about is, to use
>> pg_last_committed_xact(), track_commit_timestamp (available in
>> PostgreSQL 9.5 or after) must be enabled. If this is not enabled,
>> pg_last_committed_xact() raises an error. Also the function returns
>> NULL if no transaction is committed since the system started.
>> 
>> Comments and/or suggestions are welcome.
> 
> I have created a patch for this.
> 
> - new config parameter delay_threshold_by_time is added. Number in
>   seconds can be specified. If it's greater than 0, replication_delay
>   in "show pool_nodes" is presented in seconds with 6 digits after the
>   decimal point.
> 
> - In order to make the parameter effective, following conditions must be all met:
> 
>   - PostgreSQL 9.5 or after
>   - track_commit_timestamp is enabled in postgresql.conf
> 
>   If not, falls back as if delay_threshold_by_time is 0.

Attached is the v2 patch.  When no wal replay is perfomed,
pg_last_xact_replay_timestamp() returns no result. In this case we can
calculate the delay by:
current-timestamp - last-committed-transaction's-timestamp-on-primary

Best reagards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

-------------- next part --------------
A non-text attachment was scrubbed...
Name: replication_delay_by_time_v2.patch
Type: text/x-patch
Size: 12558 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-hackers/attachments/20220125/acb0966e/attachment-0001.bin>