MIME-Version: 1.0
References: 
 <CAHg+QDdKVnCLHot=AcoPpEiSyDzGz7wGYjAFHVOw57oDtmUDWQ@mail.gmail.com>
 <CAJpy0uDGM8eVqMORHKw61mm24AjH7jPZAhUaN3YeJrZ1bdpQFQ@mail.gmail.com>
 <CAHg+QDfQEX5b=4MB=67WgPrF4t8gyUMWyFp7-hFe=EAFW-qALA@mail.gmail.com>
In-Reply-To: 
 <CAHg+QDfQEX5b=4MB=67WgPrF4t8gyUMWyFp7-hFe=EAFW-qALA@mail.gmail.com>
From: Nisha Moond <nisha.moond412@gmail.com>
Date: Tue, 28 Apr 2026 14:03:48 +0530
Message-ID: 
 <CABdArM5RtN5XUqE53LC+6FSKA1fFd1BHL5MmeX13pgmkN0Z=fA@mail.gmail.com>
Subject: Re: [Patch]: Fix excessive ProcArrayLock acquisitions with
 subscription max_retention_duration=0
To: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Cc: shveta malik <shveta.malik@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Amit Kapila <amit.kapila16@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CABdArM5RtN5XUqE53LC%2B6FSKA1fFd1BHL5MmeX13pgmkN0Z%3DfA%40mail.gmail.com>
Precedence: bulk

On Mon, Apr 27, 2026 at 10:32=E2=80=AFPM SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> wrote:
>
> Hi,
>
> On Mon, Apr 27, 2026 at 2:48=E2=80=AFAM shveta malik <shveta.malik@gmail.=
com> wrote:
>>
>> On Mon, Apr 27, 2026 at 2:11=E2=80=AFPM SATYANARAYANA NARLAPURAM
>> <satyanarlapuram@gmail.com> wrote:
>> >
>> > Hi Hackers,
>> >
>> > When a subscription has retain_dead_tuples enabled with maxretention s=
et
>> > to zero (unlimited retention), adjust_xid_advance_interval() caps
>> > xid_advance_interval to Min(interval, maxretention).  Since maxretenti=
on
>> > is zero, this always collapses the interval to zero milliseconds.
>> >
>> > A zero makes  TimestampDifferenceExceeds(last_time, now, 0)  always
>> > true in get_candidate_xid(). This causes the apply worker to call
>> > GetOldestActiveTransactionId() on every single WAL message. This resul=
ts in
>> > a huge number of ProcArrayLock acquisitions under moderate write load.
>> >
>> > Fix by adding a maxretention > 0 guard to the cap. When maxretention i=
s zero ,
>> > the exponential back-off in adjust_xid_advance_interval()
>> > now works correctly, growing the interval from 100 ms toward the 180 s
>> > ceiling.
>> >
>> > Measured with perf uprobe counting GetOldestActiveTransactionId calls
>> > at ~39K TPS (pgbench, 5 clients):
>> >
>> >   Before fix: 25,104 calls / 5 s  (~5,021/s)
>> >   After fix:     31 calls / 5 s  (~6/s)
>> >
>>
>> Thanks for reporting it. I am reveiwing the problem sattement.
>> Meanwhile can you please look at it, I am getting the following error
>> while applying the patch on my Ubuntu setup (git am):
>>
>> error: corrupt patch at line 22
>
>
> Thanks! Please find the updated v2 patch.

Thanks for the patch. I am able to reproduce the reported issue in
debugging. The  xid_advance_interval stays 0 when retain_dead_tuples
is enabled but max_retention_duration is off which is unexpected
behavior.

Confirmed that the patch fixed it.

--
Thanks,
Nisha