Re: Implement waiting for wal lsn replay: reloaded - Mailing list pgsql-hackers

Hi Alexander, Hackers,

On Sun, Nov 16, 2025 at 10:01 PM Xuneng Zhou <xunengzhou@gmail.com> wrote:
>
> Hi!
>
> On Sun, Nov 16, 2025 at 8:37 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> >
> > On Wed, Nov 12, 2025 at 9:20 AM Xuneng Zhou <xunengzhou@gmail.com> wrote:
> > > On Wed, Nov 5, 2025 at 5:51 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
> > > > On Mon, Nov 3, 2025 at 5:13 PM Andres Freund <andres@anarazel.de> wrote:
> > > > > On 2025-11-03 16:06:58 +0100, Álvaro Herrera wrote:
> > > > > > On 2025-Nov-03, Alexander Korotkov wrote:
> > > > > >
> > > > > > > I'd like to give this subject another chance for pg19.  I'm going to
> > > > > > > push this if no objections.
> > > > > >
> > > > > > Sure.  I don't understand why patches 0002 and 0003 are separate though.
> > > > >
> > > > > FWIW, I appreciate such splits. Even if the functionality isn't usable
> > > > > independently, it's still different type of code that's affected. And the
> > > > > patches are each big enough to make that worthwhile for easier review.
> > > >
> > > > Thank you for the feedback, pushed.
> > > >
> > > > > One thing that'd be nice to do once we have WAIT FOR is to make the common
> > > > > case of wait_for_catchup() use this facility, instead of polling...
> > > >
> > > > The draft patch for that is attached.  WAIT FOR doesn't handle all the
> > > > possible use cases of wait_for_catchup(), but I've added usage when
> > > > it's appropriate.
> > >
> > > I tested the patch using make check-world, and it worked well. I also
> > > made a few adjustments:
> > >
> > > - Added an unconditional chomp($isrecovery) after querying
> > > pg_is_in_recovery() to prevent newline mismatches when $target_lsn is
> > > accidently defined.
> > > - Added chomp($output) to normalize the result from WAIT FOR LSN
> > > before comparison.
> > >
> > > At the moment, the WAIT FOR LSN command supports only the replay mode.
> > > If we intend to extend its functionality more broadly, one option is
> > > to add a mode option or something similar. Are users expected to wait
> > > for flush(or others) completion in such cases? If not, and the TAP
> > > test is the only intended use, this approach might be a bit of an
> > > overkill.
> >
> > I would say that adding mode parameter seems to be a pretty natural
> > extension of what we have at the moment.  I can imagine some
> > clustering solution can use it to wait for certain transaction to be
> > flushed at the replica (without delaying the commit at the primary).
> >
> > ------
> > Regards,
> > Alexander Korotkov
> > Supabase
>
> Makes sense. I'll play with it and try to prepare a follow-up patch.
>
> --
> Best,
> Xuneng

In terms of extending the functionality of the command, I see two
possible approaches here. One is to keep mode as a mandatory keyword,
and the other is to introduce it as an option in the WITH clause.

Syntax Option A: Mode in the WITH Clause

WAIT FOR LSN '0/12345' WITH (mode = 'replay');
WAIT FOR LSN '0/12345' WITH (mode = 'flush');
WAIT FOR LSN '0/12345' WITH (mode = 'write');

With this option, we can keep "replay" as the default mode. That means
existing TAP tests won’t need to be refactored unless they explicitly
want a different mode.

Syntax Option B: Mode as Part of the Main Command

WAIT FOR LSN '0/12345' MODE 'replay';
WAIT FOR LSN '0/12345' MODE 'flush';
WAIT FOR LSN '0/12345' MODE 'write';

Or a more concise variant using keywords:

WAIT FOR LSN '0/12345' REPLAY;
WAIT FOR LSN '0/12345' FLUSH;
WAIT FOR LSN '0/12345' WRITE;

This option produces a cleaner syntax if the intent is simply to wait
for a particular LSN type, without specifying additional options like
timeout or no_throw.

I don’t have a clear preference among them. I’d be interested to hear
what you or others think is the better direction.


--
Best,
Xuneng