Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ut4gb-000EsA-Oi for pgpool-hackers@arkaria.postgresql.org; Mon, 01 Sep 2025 13:35:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1ut4gZ-0003dT-SP for pgpool-hackers@arkaria.postgresql.org; Mon, 01 Sep 2025 13:35:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1ut4gZ-0003dL-Dg for pgpool-hackers@lists.postgresql.org; Mon, 01 Sep 2025 13:35:04 +0000 Received: from mail-yb1-xb35.google.com ([2607:f8b0:4864:20::b35]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1ut4gW-0000OB-2U for pgpool-hackers@lists.postgresql.org; Mon, 01 Sep 2025 13:35:02 +0000 Received: by mail-yb1-xb35.google.com with SMTP id 3f1490d57ef6-e96e5a3b3b3so3664511276.3 for ; Mon, 01 Sep 2025 06:35:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=tailorbrands.com; s=google; t=1756733700; x=1757338500; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=B0jPwJ1D1ZkLMvXgzcAjO/isxxtxPkvV3nCIOBA2ATI=; b=XdF013vymuWi6TGrcbR99gr4KKE7cBfhSyAlI21S699xba0MRZzKMRqYlZ5evRDmuj 9FRel+wk0lXdFI8zWoBPN40BL2rb3ew8oiPIenLDM/KiuQELGnkwAqg66wX4M405k6Y2 JM61WcElGT98sKaYpXT4KtydiFV0CvE5QTeklKj9uYD/f/egawOjbgVbwNZxgNeaM5nG PiawF8v43dxAOGW9ALJqaSiH4Sb3DR0f8HsRIpdiHk0wicIHfBjOpwGv8RX8rM6cNL2g mJOVKnxwup5GBPoEousGUDXvoi2KwXmOVl+MXz3XUcr+fHOb6U+KbfJcrvhfEkCF6Ysd bu3Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1756733700; x=1757338500; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=B0jPwJ1D1ZkLMvXgzcAjO/isxxtxPkvV3nCIOBA2ATI=; b=pLoonEvURFD1cVHnSbQPCymalkLunNyNvDuG136d80rnPwqH1KhR4UcPPNqC+dyGDJ Ye4nbQpvnV1WNuiye9rPehj0wdV1GRxVgsoLQccMW20ZPtBqmgcdXugQjl+tuBUnKlkh AynBqK3OCmXwKAEWYO1YIzsZXFpMqyjaRjU52A3O4xUo6c7eqKYV7O9ex+Dg1aA3oNrQ wUCwPPBi/c/mlcX/MES3v98DGHi1LXJDIQbltFMYKV//jTam28J6UCN6PviCcUsPVQLN vljtkVxdxfjbbJU+q+Gz3P8Y/mWsijF6dwjnonAhEyRsCfTWgUTcfLrGq3P4WzHhnRsY McYA== X-Gm-Message-State: AOJu0YyiVyMm3DsKR5FpQpwAGmJNm7ws5etBGWoOWyRuGGC8xVDLvQ7B DGzKsvB8Z3hCfuNWqBJjaB5Bmx8NXxWNJ7DXRBH2Oh7IjF//enF37qJj8cT2r5723XYChohMTNC u1qtcvtpJVX3M9+zhPDd+lWVtGHgrN4vASDRrKoZxnXAf0XEW0vKyPDsNfg== X-Gm-Gg: ASbGncvAFySpHW+Wo/l60dJMGgwPQ1BebQ2QXzn2Kuo3rLI/WOSdAYNvlP97eqMF6R5 cfLphy8C7ZuuJfHLXZJtgbquzUM8DbO2hL0hPwRnfjXpLloBDdU5+1TX4O37HKqMAiXZr0rREEs iDXxv/h4bZAJY0sngvtCFsiTCZLltCHzU5aMWofVc5wvYJfZfTzOG+heHIVhh3vobq3WuB+pe+y 1HlK3BMD/g5QeOD X-Google-Smtp-Source: AGHT+IHXBO4InclZDQTrdvDPgJMrsaeSa4TClRXHfKyiuQ45gwwvYN9YywJg9HkNReR6Kn/O0PFArLzaKU8BTHuoUqI= X-Received: by 2002:a05:690c:9508:b0:721:391d:8ed6 with SMTP id 00721157ae682-722765117e1mr57220037b3.40.1756733699466; Mon, 01 Sep 2025 06:34:59 -0700 (PDT) MIME-Version: 1.0 References: <20250825.111825.1765418484481637087.ishii@postgresql.org> <20250826.104155.1065705000208094250.ishii@postgresql.org> In-Reply-To: From: Nadav Shatz Date: Mon, 1 Sep 2025 16:34:48 +0300 X-Gm-Features: Ac12FXyqQnCcZ6G3_T6lnJKvWxdtGx0T1KEZpWv1tx428Psdf6jj_JGWaIkSm2U Message-ID: Subject: Re: Proposal: recent access based routing for primary-replica setups To: Tatsuo Ishii Cc: pgpool-hackers@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000001f4b36063dbd7054" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001f4b36063dbd7054 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=E2=80=AFAM Nadav Shatz wrote: > Hi Tatsuo, > > I haven=E2=80=99t tried it yet but the whole premise of having it run a c= ommand is > that it=E2=80=99s not dependent on the specific DB. As you mentioned earl= ier. > > The issue blocking the regular lag extraction from aurora is that it > doesn=E2=80=99t update the tables in the DB. It does have a CloudWatch AP= I to get > the numbers tho. > > You can see the metric AuroraReplicaLag under > > https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/metrics-refe= rence.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=E2=80=99ll 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=E2=80=AFAM Tatsuo Ishii 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 =3D 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=E2=80=AFAM Tatsuo Ishii >> 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 =3D 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 whitespac= e. >> >> - 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 >> > --=20 Nadav Shatz Tailor Brands | CTO --0000000000001f4b36063dbd7054 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Tatsuo,

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

What do you thin= k?

On Tue, Aug 26, 2025 at 9:54=E2=80=AFAM Nadav= Shatz <nadav@tailorbrands.com= > wrote:
=
Hi=C2=A0Tatsuo,

I haven=E2=80=99t tried it yet but the whole premise of having it= run a command is that it=E2=80=99s not dependent on the specific DB. As yo= u mentioned earlier.=C2=A0

The issue blocking the regular lag extraction from aurora is that it doe= sn=E2=80=99t update the tables in the DB. It does have a CloudWatch API to = get the numbers tho.=C2=A0

You can see the metric AuroraReplicaLag under=C2=A0
=
So if we have a simple command to either get it or have s= omething else update a file with the numbers based on it we=E2=80=99ll be f= ine.=C2=A0

Ordering here= could get tricky since we couple the command with the instance order.=C2= =A0

Maybe we can expand = the command to receive some arguments as to instance order.=C2=A0

What do you think?


<= div dir=3D"rtl" class=3D"gmail_signature">
Na= dav Shatz
Tai= lor Brands=C2=A0| CTO


On Tue, Aug 26, 2025 at 4:42=E2=80=AFAM Tatsuo Ishii <ishii@postgresql.or= g> 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":

=C2=A0Set sr_check_period to 0 to disable streaming replication delay
=C2=A0checking. This is because Aurora does not provide necessary functions=
=C2=A0to check the replication delay.

=C2=A0sr_check_period =3D 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=E2=80=AFAM Tatsuo Ishii <ishii@postgresql.org> w= rote:
>
>> Hi Nadav,
>>
>> Thank you for the patch!
>>
>> I have one question. How do you provide a password (sr_check_passw= ord)
>> while executing replication_delay_source_cmd as sr_check_user? In = my
>> understanding replication_delay_source_cmd is executed through su<= br> >> 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.<= br> >>
>> 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 databas= e
>> user, not OS user. In PostgreSQL they are not necessarily the
>> same. Also doing su in pgpool process needs to be very carefully t= o
>> 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: trail= ing
>> whitespace.
>>
>> /home/t-ishii/external-lag-feature-implementation.patch:317: trail= ing
>> whitespace.
>>
>> /home/t-ishii/external-lag-feature-implementation.patch:318: trail= ing
>> whitespace.
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0cmd_len =3D strlen(escaped_cmd) +
>> /home/t-ishii/external-lag-feature-implementation.patch:320: trail= ing
>> whitespace.
>>
>> /home/t-ishii/external-lag-feature-implementation.patch:322: trail= ing
>> whitespace.
>>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0snprintf(full_command, cmd_len, "su - %s -c &#= 39;%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 whites= pace.
>> - test_parsing.sh: Unit test for parsing logic
>> /home/t-ishii/external-lag-feature-tests.patch:440: trailing white= space.
>> # 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
<= font color=3D"#000000">Tailor Brands=C2=A0| CTO
--0000000000001f4b36063dbd7054--