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

[zam bak]

On Mon, Jun 5, 2023 at 8:43 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>
> >> 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.

Thanks for looking into this.
This is really impacting us a lot - Is it possible to have a 4.4.4
release with this fix?

According to the site, next release is slated for August 17th, 2023
(more than two months away)
and using delay_treshold is not not really an option for us due to
business requirements (one cannot "translate" amount of WAL bytes to
replication lag "time")

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