[pgpool-hackers: 4107] 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

I have committed/pushed the patch along with pcp_node_info changes and
document changes to master branch.

https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=832212b23df061bec3ce18df83f0a853bfaf6593

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