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 1uqnZo-00DK7P-T0 for pgpool-hackers@arkaria.postgresql.org; Tue, 26 Aug 2025 06:54:42 +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 1uqnZo-0023QS-4A for pgpool-hackers@arkaria.postgresql.org; Tue, 26 Aug 2025 06:54:40 +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 1uqnZn-0023QL-Q0 for pgpool-hackers@lists.postgresql.org; Tue, 26 Aug 2025 06:54:40 +0000 Received: from mail-yw1-x1130.google.com ([2607:f8b0:4864:20::1130]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uqnZk-001nQu-2O for pgpool-hackers@lists.postgresql.org; Tue, 26 Aug 2025 06:54:39 +0000 Received: by mail-yw1-x1130.google.com with SMTP id 00721157ae682-71d601859f5so40223807b3.0 for ; Mon, 25 Aug 2025 23:54:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=tailorbrands.com; s=google; t=1756191276; x=1756796076; 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=uYn7Q1Ux9Td9J0766hA6J+spiYLFktD3sgfxql04tw4=; b=IrUvSdFFlL6L8deE9i6TZhouUjsQ18uEk6NyaFjviByJ5hHzjqO4sI390dwLBNBVAP bqu/PHzIMBXI8vaeT2Ug7o43e+7nsXSKcxQu3pZ1l0RFMioP3JshIVpYCRwkEhwYwGv0 lC4g1Hg6rKxRLU1WRErHAdhm1n5m7MO5tZ4rOnniDRqLKs7q1EBtt/9m5zBNT2sdJaMO T4UBK5fu94hckJApGETcM+WR50U2AVkZWO4TSDGIUD56XDGS2ZIO5FZoa448Pm6FfUUr UOQKETAq3W18gX0Iog+6McxdRmWD7VeeV0t+nn9A+E9BkJ2JvQTnTmMRLJPGST2CDRz8 NxNw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1756191276; x=1756796076; 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=uYn7Q1Ux9Td9J0766hA6J+spiYLFktD3sgfxql04tw4=; b=AMMr+lDQOVfRmu5jalj/LIaSUNfAohyAoj26qo1J12nZ7rPVS2TNPnD+8VLE/Gesc3 w3yl863GzmItue2DBx+kzKRFVtvrRLKIMzMJ1kteJupJv3igBAEtCzGD5KTBe7KbNRZQ l+/0FhTm9xf7367tWvfKh8HTOuIYvUhibbGL1QVZkfE1EfHavNuZpRWUivKGJAjsrbFo y0ObtspW3EV19kXVuKU253qXI6W+XU0Q9HPG01pw7btSDniPWQGyrkxXtUBCNp5Q7Or+ VZ9T5bUwhD+f39JH/qGgh85wqx2o4WxLWFYuHqAmgux0lwZAUl4BCBvhpxZt5skRvzis gX8Q== X-Gm-Message-State: AOJu0YyeJZyc4UTlPKaCW998eN2LpQckfSORj/BbLSDDhhx+eMkiIzuo swxVUzxMkk7mLXJ2N3IDhtFSwwsueem5gdbJumCTtcmAho/lDUbk29ZDLoRKMfhSpPECnkHk7JM UOMGlL04Z6aDh0ciWVU/gsF4yeY8DnCy6ADWr7fKiPQ== X-Gm-Gg: ASbGncux0pFGicOblvwyhmb75vyi4VfQIEI+oS6UVIP0fJh8FLQg16rQtBAUlcjn3+M h8euO5W1CVgXRmTQ9QdVT9VX0kh8HFk4+l5+r/EhFXzB5y2lOlUky1LJA5sRx9h9n9gVCbPI7A0 hqwvOyI2VxxxSA4z+tWb4JtHRsyWgpsGJl4kgQd0rICRRTYdIwWSABTJj6IUUsT1925gG3syByp DZ7NBioE6Bms6mjhhA8vJCz5MVXdOW3WBdAfTvn6MEyFa4P2+MR3xkfi7Wu+A== X-Google-Smtp-Source: AGHT+IHHS4r2HwiSyciqc9p6Y4CnhB8SR6nCpx8GmDDolWYgthLQmv2kMCNn0xCImwDVEDs3c7Uu/xKo7UQiDypuoAU= X-Received: by 2002:a05:690c:9a11:b0:71e:71b2:7dd2 with SMTP id 00721157ae682-71fdc2f10d6mr190334797b3.10.1756191276448; Mon, 25 Aug 2025 23:54:36 -0700 (PDT) MIME-Version: 1.0 References: <20250825.111825.1765418484481637087.ishii@postgresql.org> <20250826.104155.1065705000208094250.ishii@postgresql.org> In-Reply-To: <20250826.104155.1065705000208094250.ishii@postgresql.org> From: Nadav Shatz Date: Tue, 26 Aug 2025 09:54:25 +0300 X-Gm-Features: Ac12FXx_q3xvIHXu8QLhv4zzQieG2TKLvGdjoyB3ydBaFfO4-0_wM6gT0Q0jmHQ 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="00000000000030d7ed063d3f256a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000030d7ed063d3f256a Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Tatsuo, I haven=E2=80=99t tried it yet but the whole premise of having it run a com= mand is that it=E2=80=99s not dependent on the specific DB. As you mentioned earlie= r. 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 API = to get the numbers tho. You can see the metric AuroraReplicaLag under https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/metrics-refere= nce.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 whitespace= . > >> - test_parsing.sh: Unit test for parsing logic > >> /home/t-ishii/external-lag-feature-tests.patch:440: trailing whitespac= e. > >> # 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 > --00000000000030d7ed063d3f256a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
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 something else upda= te a file with the numbers based on it we=E2=80=99ll be fine.=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 recei= ve some arguments as to instance order.=C2=A0

What do you think?

<= div dir=3D"auto">
Nadav Shatz
Tailor Brands=C2=A0| CTO


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

=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
--00000000000030d7ed063d3f256a--