[pgpool-general: 8809] Re: queries not redirecting to primary when "delay_threshold_by_time" is exceeded

[Tatsuo Ishii]

>> Hi
>> 
>> We have pgpool 4.4.2 with streaming replication setup using PG 14.7
>> (primary+1 replica)
>> 
>> The configuration is set up to check for replication lag every 5 seconds
>> and we have also set up "delay_treshold_by_time=1" (meaning 1 second)
>> 
>> We can see replication lag messages in the log as well as check the
>> replication lag using SHOW POOL_NODES and pcp_node_info.
>> All that works fine...
>> 
>> However, we are noticing that even after the lag is detected and
>> exceeds 1 second, the SELECT queries are NOT being routed back to the
>> primary server!
>> 
>> Attached file has config and sample taken from the log (with
>> log_per_node_statement=on)
>> This is a rather impactful issue for us because the application is
>> getting stale data.
>> I know we can redirect all queries to go to primary on per application
>> basis but would rather leverage load balancing when possible.
> 
> I will look into this.

It turned out that delay_treshold_by_time is broken since it was introduced.
I have fixed it and pushed to the git repository.
https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=6aebf24aa9a627f4c852e44f6407f17a34d50528

Unfortunately there's no workaround of this for now. Please use
delay_treshold instead if you like.

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