MIME-Version: 1.0
References: 
 <CAHg+QDeHkMcLBKaBu6sxigL2gUsHXye3QQs14zKyD25BnPNAvA@mail.gmail.com>
In-Reply-To: 
 <CAHg+QDeHkMcLBKaBu6sxigL2gUsHXye3QQs14zKyD25BnPNAvA@mail.gmail.com>
From: Alexander Korotkov <aekorotkov@gmail.com>
Date: Thu, 16 Apr 2026 10:31:01 +0300
Message-ID: 
 <CAPpHfdtKvFME6+-NPnbmQj0dYXWKD2kKm_OTrV22Oq68iYBMQw@mail.gmail.com>
Subject: Re: [PATCH] Fix WAIT FOR LSN standby_write/standby_flush for archive
 recovery cases
To: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAPpHfdtKvFME6%2B-NPnbmQj0dYXWKD2kKm_OTrV22Oq68iYBMQw%40mail.gmail.com>
Precedence: bulk

Hi, Satyanarayana!

On Wed, Apr 15, 2026 at 9:44=E2=80=AFAM SATYANARAYANA NARLAPURAM
<satyanarlapuram@gmail.com> wrote:
> GetCurrentLSNForWaitType() for WAIT_LSN_TYPE_STANDBY_WRITE and
> WAIT_LSN_TYPE_STANDBY_FLUSH previously relied on the WAL receiver's
> tracked write/flush positions (GetWalRcvWriteRecPtr/GetWalRcvFlushRecPtr)=
.
> There are two scenarios where WAIT FOR LSN queries can be stalled though
> replay is making progress. Breaking it down to two to give clarity on set=
ups but
> the underlying problem is the same.
>
> There are two scenarios here:
>
> (1). When the standby is disconnected from the primary and switched to WA=
L
> archive mode, it continues to be in that mode until no more WAL is availa=
ble to replay
> and then switch to streaming mode. Until then WAIT FOR LSN calls get stuc=
k on the
> standby though replay catches up beyond the stale WAL receiver position. =
Switching
> XLog source from archive to streaming is separately tracked in [1].
>
> (2). In the case of Archive recovery, no WAL receiver process exists, so =
these
> functions return InvalidXLogRecPtr (0/0). WAIT FOR LSN with standby_flush=
 or
> standby_write modes would always time out, even for WAL that has been
> fully replayed.
>
> Fix by falling back to the replay LSN (GetXLogReplayRecPtr) when the WAL
> receiver position is invalid or behind replay. This is correct because an=
y
> WAL that has been replayed has necessarily already been written and flush=
ed
> to disk. Attached the repro test case.

Please, check, similar patch is already posted here.

https://www.postgresql.org/message-id/CABPTF7Ukk8iJF7TpnK2mFOaboNJgWL1csfXu=
4e3J4GT0o7x0GQ%40mail.gmail.com

------
Regards,
Alexander Korotkov
Supabase