Allow reading LSN written by walreciever, but not flushed yet

Moved off from "Small fixes needed by high-availability tools"

> On 12 May 2025, at 01:33, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Fri, May 2, 2025 at 6:30 PM Andrey Borodin <x4mmm(at)yandex-team(dot)ru> wrote:
>>
>> 3. Allow reading LSN written by walreciever, but not flushed yet
>>
>> Problem: if we have synchronous_standby_names = ANY(node1,node2), node2 might be ahead of node1 by flush LSN, but before by written LSN. If we do a failover we choose node2 instead of node1 and loose data recently committed with synchronous_commit=remote_write.
>>
>
> In which case, can we rely on written WAL that is not yet flushed?
> Because say you decide based on written WAL and choose node-1 in above
> case for failover, what if it restarts without flushing the written
> WAL?

When user operate on "synchronous_commit=remote_write" they understand that simultaneous reboot of primary and standbys will incur data loss.
And if node is not rebooted - we need LSN of write, not flush. Or might want LSN "flush everything you have written, and return that LSN". That will also do the trick, but is not necessary.

>
>> Caveat: we already have a function pg_last_wal_receive_lsn(), which in fact returns flushed LSN, not written. I propose to add a new function which returns LSN actually written. Internals of this function are already implemented (GetWalRcvWriteRecPtr()), but unused.
>>
>
> It seems to me that this is less controversial than your other two
> proposals. So, we can discuss this in a separate thread as well.

Done so. Thanks!

Best regards, Andrey Borodin.