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

Hi Tatsuo,

I don't want to rush at all - did you get a chance to look at what I sent?
Can I share more relevant information with you?

What do you think?

On Tue, Aug 26, 2025 at 9:54 AM Nadav Shatz <nadav(at)tailorbrands(dot)com> wrote:

> Hi Tatsuo,
>
> I haven’t tried it yet but the whole premise of having it run a command is
> that it’s not dependent on the specific DB. As you mentioned earlier.
>
> The issue blocking the regular lag extraction from aurora is that it
> doesn’t update the tables in the DB. It does have a CloudWatch API to get
> the numbers tho.
>
> You can see the metric AuroraReplicaLag under
>
> https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/metrics-reference.html
>
> So if we have a simple command to either get it or have something else
> update a file with the numbers based on it we’ll be fine.
>
> Ordering here could get tricky since we couple the command with the
> instance order.
>
> Maybe we can expand the command to receive some arguments as to instance
> order.
>
> What do you think?
>
>
> Nadav Shatz
> Tailor Brands | CTO
>
>
> On Tue, Aug 26, 2025 at 4:42 AM Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
>
>> Hi Nadav,
>>
>> Thank you for updating the patch. I will look into that.
>>
>> I have a question. Have you actually tried the patch with AWS Aurora?
>> I am wondering how patched pgpool works with Aurora. I am asking
>> because in the doc "8.5. Aurora Configuration Example":
>>
>> Set sr_check_period to 0 to disable streaming replication delay
>> checking. This is because Aurora does not provide necessary functions
>> to check the replication delay.
>>
>> sr_check_period = 0
>>
>> So streaming replication checking is disabled, and it means that your
>> patch is also effectively disabled too.
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS K.K.
>> English: http://www.sraoss.co.jp/index_en/
>> Japanese:http://www.sraoss.co.jp
>>
>> > Hi Tatsuo,
>> >
>> > Thank you for the notes - please find attached an updated version.
>> >
>> > What do you think?
>> >
>> > Thanks,
>> >
>> > On Mon, Aug 25, 2025 at 5:18 AM Tatsuo Ishii <ishii(at)postgresql(dot)org>
>> wrote:
>> >
>> >> Hi Nadav,
>> >>
>> >> Thank you for the patch!
>> >>
>> >> I have one question. How do you provide a password (sr_check_password)
>> >> while executing replication_delay_source_cmd as sr_check_user? In my
>> >> understanding replication_delay_source_cmd is executed through su
>> >> command in your patch. In this case su command tries to read the
>> >> password from terminal. I don't see such a code in the patch.
>> >>
>> >> BTW, I start to think that executing replication_delay_source_cmd as
>> >> sr_check_user might not be a good idea. sr_check_user is a database
>> >> user, not OS user. In PostgreSQL they are not necessarily the
>> >> same. Also doing su in pgpool process needs to be very carefully to
>> >> avoid vulnerability. Probably we just execute it as pgpool OS user?
>> >>
>> >> Lastly when I apply the patches using git apply, there are some
>> >> trailing space errors.
>> >>
>> >> $ git apply ~/external-lag-feature-implementation.patch
>> >> /home/t-ishii/external-lag-feature-implementation.patch:314: trailing
>> >> whitespace.
>> >>
>> >> /home/t-ishii/external-lag-feature-implementation.patch:317: trailing
>> >> whitespace.
>> >>
>> >> /home/t-ishii/external-lag-feature-implementation.patch:318: trailing
>> >> whitespace.
>> >> cmd_len = strlen(escaped_cmd) +
>> >> /home/t-ishii/external-lag-feature-implementation.patch:320: trailing
>> >> whitespace.
>> >>
>> >> /home/t-ishii/external-lag-feature-implementation.patch:322: trailing
>> >> whitespace.
>> >> snprintf(full_command, cmd_len, "su - %s -c
>> '%s'",
>> >> warning: squelched 4 whitespace errors
>> >> warning: 9 lines add whitespace errors.
>> >>
>> >> $ git apply ~/external-lag-feature-tests.patch
>> >> /home/t-ishii/external-lag-feature-tests.patch:87: trailing whitespace.
>> >> - test_parsing.sh: Unit test for parsing logic
>> >> /home/t-ishii/external-lag-feature-tests.patch:440: trailing
>> whitespace.
>> >> # Test 2: Float values
>> >> warning: 2 lines add whitespace errors.
>> >>
>> >> Also I have some compilation errors after patching the source
>> >> code. See attached compilation log.
>> >>
>> >> 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
>>
>

--
Nadav Shatz
Tailor Brands | CTO