Re: Allow reading LSN written by walreciever, but not flushed yet

> On 13 May 2025, at 14:13, Fujii Masao <masao.fujii@oss.nttdata.com> wrote:
>
>
>
> On 2025/05/13 0:47, Andrey Borodin wrote:
>> Moved off from "Small fixes needed by high-availability tools"
>>> On 12 May 2025, at 01:33, Amit Kapila <amit.kapila16@gmail.com> wrote:
>>>
>>> On Fri, May 2, 2025 at 6:30 PM Andrey Borodin <x4mmm@yandex-team.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
beforeby written LSN. If we do a failover we choose node2 instead of node1 and loose data recently committed with
synchronous_commit=remote_write.
>
> In this case, doesn't the flush LSN typically catch up to the write LSN on node2
> after a few seconds? Even if the walreceiver exits while there's still written
> but unflushed WAL, it looks like WalRcvDie() ensures everything is flushed by
> calling XLogWalRcvFlush(). So, isn't it safe to rely on the flush LSN when selecting
> the most advanced node? No?

Well, we implemented this and made tests that do a lot of failovers. These tests observed data loss in some infrequent
casesdue to wrong new primary selection. Because "few seconds" is actually unknown random time. 

>>>> Caveat: we already have a function pg_last_wal_receive_lsn(), which in fact returns flushed LSN, not written. I
proposeto add a new function which returns LSN actually written. Internals of this function are already implemented
(GetWalRcvWriteRecPtr()),but unused. 
>
> GetWalRcvWriteRecPtr() returns walrcv->writtenUpto, which can move backward
> when the walreceiver restarts. This behavior is OK for your purpose?

It is OK, because:
1. It's strictly no worse than flushed LSN
2. synchronous_commit = remove_write assumes that you can loose data when primary failed and standby is restarted
simultaneously.The user is warned. 


Best regards, Andrey Borodin.