Re: Proposal: recent access based routing for primary-replica setups

Hi,

I'm back at work - wdyt of this version?

(side note - Japan was incredible :))

On Tue, Sep 30, 2025 at 12:35 PM Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:

> > I would actually suggest including down state instances in case pgpool
> > isn’t aware yet. It can exclude them once it does.
> > For these cases maybe -1 ?
>
> I don't think pgpool will triger failover even if
> replication_delay_source_cmd returns -1 for such instance because
> pgpool already has its own method to detect instance down (i.e. health
> check) and method to avoid false positive
> (i.e. failover_require_consensus).
>
> Still for such instaces replication_delay_source_cmd returns -1 maybe
> useful if it's logged for admins.
>
> So I am Okay with the idea.
>
> Best regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
>
> > Nadav Shatz
> > Tailor Brands | CTO
> >
> >
> > On Mon, Sep 22, 2025 at 7:34 AM Tatsuo Ishii <ishii(at)postgresql(dot)org>
> wrote:
> >
> >> > Thank you for the kind words. We are having a great time!
> >>
> >> Glad to hear that!
> >>
> >> > Regarding the command knowing about the primary I think it is safe to
> >> assume.
> >>
> >> Okay.
> >>
> >> > We can start this way and evolve in the future if needed.
> >>
> >> Agreed.
> >>
> >> > I can include a note about it in the notes that the command will only
> >> receive the secondary instances as arguments.
> >> >
> >> > Anything else that comes to mind?
> >>
> >> Sounds like a reasonable requirement. Also the command excludes any
> >> instance which is in down state?
> >>
> >> Best regards,
> >> --
> >> Tatsuo Ishii
> >> SRA OSS K.K.
> >> English: http://www.sraoss.co.jp/index_en/
> >> Japanese:http://www.sraoss.co.jp
> >>
> >> > Nadav Shatz
> >> > CTO
> >> >
> >> >> On Sep 16, 2025, at 7:30 PM, Tatsuo Ishii <ishii(at)postgresql(dot)org>
> wrote:
> >> >>
> >> >> 
> >> >>>
> >> >>> Hi Tatsuo,
> >> >>>
> >> >>> Sorry for the late reply - I'm traveling with my family at the
> moment
> >> (in
> >> >>> Japan actually)
> >> >>
> >> >> Excellent! Hope you and your family are spending great time in Japan.
> >> >>
> >> >>> and might be delayed in responding.
> >> >>
> >> >> No problem at all. I think you should focus on the travel at this
> >> >> moment.
> >> >>
> >> >>> Re your points:
> >> >>> 1 - we can, but I have to say that a user I tend to prefer
> >> configuration
> >> >>> values not have a "magic" value that does something different than
> the
> >> >>> usual case like this would create. I'd stick with what we already
> have
> >> >>> planned. happy to hear from others on the mailing list as well of
> >> course.
> >> >>
> >> >> Makes sense. I withdraw my proposal.
> >> >>
> >> >>> 2 - I think we can have the primary always be the first or we can
> >> >>> completely remove it since it might be redundant as it's always
> going
> >> to be
> >> >>> 0. what do you think?
> >> >>
> >> >> What I am not sure is, whether we can assume the command always knows
> >> >> which host (or IP) is primary? If the answer is yes, then we could
> >> >> omit the primary. What do you think?
> >> >>
> >> >>> 3 - I agree with you, next version (after we clear everything else)
> >> will
> >> >>> have only ip/hostname+port.
> >> >>
> >> >> Thank you for understanding.
> >> >>
> >> >>> Let me know your thoughts
> >> >>>
> >> >>> Thanks!
> >> >>>
> >> >>>> On Tue, Sep 9, 2025 at 9:42 AM Tatsuo Ishii <ishii(at)postgresql(dot)org>
> >> wrote:
> >> >>>>
> >> >>>> Hi Nadav,
> >> >>>>
> >> >>>>> Hi Tatsuo,
> >> >>>>>
> >> >>>>> Please find attached the 3 patch files (implementation, tests,
> docs)
> >> with
> >> >>>>> the updates we discussed.
> >> >>>>>
> >> >>>>> What do you think?
> >> >>>>
> >> >>>> I haven't read the code details yet but I have a few questions.
> >> >>>>
> >> >>>> 1) Can we use only replication_delay_source_cmd and if it's value
> is
> >> >>>> 'builtin', then we treat it as replication_delay_source =
> builtin?
> >> >>>> Maybe this is matter of taste but I would like to know your
> >> >>>> opinion.
> >> >>>>
> >> >>>> 2) replication_delay_source_cmd will be given an ordered list of
> >> >>>> instance identifiers. But it seems there's no way for the command
> >> >>>> which one is the primary instance. Is it okay for the command?
> >> >>>>
> >> >>>> 3) Why do you have 3 kind of instance identifiers (application
> name,
> >> >>>> hostname (IP) + port and node id? I thought "hostname (IP) +
> port"
> >> >>>> is sufficient.
> >> >>>>
> >> >>>> Comments?
> >> >>>> --
> >> >>>> Tatsuo Ishii
> >> >>>> SRA OSS K.K.
> >> >>>> English: http://www.sraoss.co.jp/index_en/
> >> >>>> Japanese:http://www.sraoss.co.jp
> >> >>>>
> >> >>>
> >> >>>
> >> >>> --
> >> >>> Nadav Shatz
> >> >>> Tailor Brands | CTO
> >> >
> >> >
> >>
>

--
Nadav Shatz
Tailor Brands | CTO