MIME-Version: 1.0
References: 
 <CA+-JvFsMHckBMzsu5Ov9HCG3AFbMh056hHy1FiXazBRtZ9pFBg@mail.gmail.com>
 <OSCPR01MB149667506BCFD684FEFDCB919F511A@OSCPR01MB14966.jpnprd01.prod.outlook.com>
 <CALj2ACUmPbkcj4y4oeXvzUkBejG68QDtrFF7QHDC_qz2vQcTCg@mail.gmail.com>
 <CAHg+QDfnK7tQxsEZox=kOkYfqANmL70mwn0N=eRrJxE1Z+1ygg@mail.gmail.com>
 <CALj2ACX_o+dKeAaK76mpAtG646UnDHpGUWziUkCvicVz8mz6=A@mail.gmail.com>
 <CAD21AoATM=Un8ejnbcDQ7q+CaCoxpkA7Ln9bacvQEoymqvjPug@mail.gmail.com>
 <CALj2ACUmUb=CLEyfsQrW0WAkF6Y9fiBfG6pnPjepfOM7A1XReA@mail.gmail.com>
 <CAD21AoAg6x57a8LoP2s+0vgizp9QGHcLGJL9bwh7kzEJq3arBg@mail.gmail.com>
 <CALj2ACWcaSkfMAQu3s6ZkTZuoFvVRD=DkxXbBwC33PL9+kzsqw@mail.gmail.com>
 <CAD21AoBEBqQONiZxvnUYOu814yB2tRPrmX=7KqvL+f3ae7250w@mail.gmail.com>
 <CALj2ACUenekLgjMr8x4DyuU=zKZ4eNqW9XF-1PovSctkY2VA0Q@mail.gmail.com>
 <CAFC+b6pO44=zGqwijzrcyGGTYCM51Y7zS5uQX0_nWjsxW9i3QQ@mail.gmail.com>
 <CALj2ACU=A31kqHELyaF-=2vnyed=cO2JNQt+vY92KtHLF-m8sg@mail.gmail.com>
 <CAD21AoB4MsTpG5JEkifght_tQ91VHJO_8kpsDCrG-z9qkkmN5g@mail.gmail.com>
 <CALj2ACVGpVHuqchPPFWdiLDN-PDPCEe=sU43YB7nqafE+VMXaQ@mail.gmail.com>
 <CAD21AoB61CgA921J2FwXzxfxUsxQzi7GYAZaH05PHmb6HFe_hg@mail.gmail.com>
 <CALj2ACV724Yr2=5Jun4XGQyGhvCiXo+7GXJUnybqf9SYqQA6gw@mail.gmail.com>
 <CALj2ACXR=5SoApm6duEOc6pi=-KaBqSxuSP6XNQot2LywrqtAA@mail.gmail.com>
 <CAD21AoABTzYoe99gmdvs4D8kwfD=AtaaWFb8C2biMkUncmcXDA@mail.gmail.com>
 <CALj2ACV6jYzaMZD3jF3nTj_bLoUZw8+UwucVKTK1kVCwXYVQ8w@mail.gmail.com>
 <CAD21AoArh6-RfOGGY6GfWqqHv0qUJEMwXVmGQk=29uYiKFLSgA@mail.gmail.com>
 <CALj2ACV_PV5LiL55O02UYJ_zfZw0CUKnR_nW83mpm0ZuYkbM8g@mail.gmail.com>
In-Reply-To: 
 <CALj2ACV_PV5LiL55O02UYJ_zfZw0CUKnR_nW83mpm0ZuYkbM8g@mail.gmail.com>
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Wed, 15 Apr 2026 22:03:33 -0700
Message-ID: 
 <CALj2ACU7nVkqWx+jxmaZ-V4txqKdhKmeXHcZa+kkyPeOFYSQPA@mail.gmail.com>
Subject: Re: Introduce XID age based replication slot invalidation
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Srinath Reddy Sadipiralla <srinath2133@gmail.com>,
	SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>,
	"Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>,
 John H <johnhyvr@gmail.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CALj2ACU7nVkqWx%2BjxmaZ-V4txqKdhKmeXHcZa%2BkkyPeOFYSQPA%40mail.gmail.com>
Precedence: bulk

Hi,

On Mon, Apr 6, 2026 at 10:42=E2=80=AFAM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
>
> On Mon, Apr 6, 2026 at 1:45=E2=80=AFAM Masahiko Sawada <sawada.mshk@gmail=
.com> wrote:
> >
> > > I took a look at the v10 patch and it LGTM. I tested it - make
> > > check-world passes, pgindent doesn't complain.
> >
> > While reviewing the patch, I found that with this patch, backend
> > processes and autovacuum workers can simultaneously attempt to
> > invalidate the same slot for the same reason. When invalidating a
> > slot, we send a signal to the process owning the slot and wait for it
> > to exit and release the slot. If the process takes a long time to exit
> > for some reason, subsequent autovacuum workers attempting to
> > invalidate the same slot will also send a SIGTERM and get stuck at
> > InvalidatePossiblyObsoleteSlot(). In the worst case, this could result
> > in all autovacuum activity being blocked. I think we need to address
> > this problem.
>
> Thank you!
>
> You're right that multiple autovacuum workers can wait on the same
> slot for SIGTERM to take effect on the process (mainly walsenders)
> holding the slot. Once the process holding the slot exits, one worker
> finishes the invalidation and the others see it's done and move on.
>
> However, IMHO, this is unlikely to be a problem in practice.
>
> First, SIGTERM must take a long time to terminate the process holding
> the slot. This seems unlikely unless I'm missing some cases.
>
> Second, the slot's xmin must be very old (past XID age) while the
> process is still running but slow to exit. If we set max_slot_xid_age
> close to vacuum_failsafe_age (e.g., 1.6 billion. I've added this note
> in the docs), it seems unlikely that the replication connection would
> still be active at that point.
>
> Also, concurrent invalidation can already happen today between the
> startup process and checkpointer on standby.
>
> If needed, we could add a flag to skip extra invalidation attempts
> based on field experience. Since this feature is off by default, I'd
> prefer to keep things simple, but I'm open to other approaches.
>
> Thoughts?

Thank you Sawada-san. I've been thinking more about it and I agree we
need to address this. While I still think the scenario is unlikely in
practice (SIGTERM would have to take a long time, the slot's xmin
would have to be very old while the walsender is still running, etc.),
I think it's worth handling.

I can think of a couple of approaches:

1. Use ConditionVariableTimedSleep instead of ConditionVariableSleep
when called from an autovacuum worker. Workers don't block forever,
but they still wait for the timeout duration, still send redundant
SIGTERMs, and a correct timeout value needs to be chosen. When it
expires, the worker either retries (still stuck) or gives up (same as
approach 2).

2. Make the vacuum path non-blocking when another process is already
invalidating the same slot. The first process to attempt invalidation
proceeds normally: it sends SIGTERM and waits on
ConditionVariableSleep for the process holding the slot to exit. But
if a subsequent autovacuum worker finds that another process has
already initiated invalidation of this slot, it skips the slot and
proceeds with vacuum instead of waiting on the same
ConditionVariableSleep.

I think approach 2 is simple. If another process is already
invalidating the slot, there's no reason for the autovacuum worker to
also block. The tradeoff is that this vacuum cycle's OldestXmin won't
move forward and it will need another cycle for this relation. But
that's fine given that the scenario as explained above is unlikely to
happen in practice.

Please let me know if my thinking sounds reasonable. I'm open to other
ideas too.

Thoughts?

--=20
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com