Re: confirmed_flush_lsn shows LSN of the data that has not yet been received by the logical subscriber.

On Thu, Sep 8, 2022 at 4:14 PM Ashutosh Sharma <ashu.coek88@gmail.com> wrote:
>
> Hi All,
>
> The logically decoded data are sent to the logical subscriber at the time of transaction commit, assuming that the
datais small. However, before the transaction commit is performed, the LSN representing the data that is yet to be
receivedby the logical subscriber appears in the confirmed_flush_lsn column of pg_replication_slots catalog. Isn't the
informationseen in the confirmed_flush_lsn column while the transaction is in progress incorrect ? esp considering the
descriptiongiven in the pg doc for this column. 
>
> Actually, while the transaction is running, the publisher keeps on sending keepalive messages containing LSN of the
lastdecoded data saved in reorder buffer and the subscriber responds with the same LSN as the last received LSN which
isthen updated as confirmed_flush_lsn by the publisher. I think the LSN that we are sending with the keepalive message
shouldbe the one representing the transaction begin message, not the LSN of the last decoded data which is yet to be
sent.Please let me know if I am missing something here. 

The transactions with commit lsn < confirmed_flush_lsn are confirmed
to be received (and applied by the subscriber. Setting LSN
corresponding to a WAL record within a transaction in progress as
confirmed_flush should be ok. Since the transactions are interleaved
in WAL stream, it's quite possible that LSNs of some WAL records of an
inflight transaction are lesser than commit LSN of some another
transaction. So setting commit LSN of another effectively same as
setting it to any of the LSNs of any previous WAL record irrespective
of the transaction that it belongs to.

In case WAL sender restarts with confirmed_flush_lsn set to LSN of a
WAL record of an inflight transaction, the whole inflight transaction
will be sent again since its commit LSN is higher than
confirmed_flush_lsn.

I think logical replication has inherited this from physical
replication. A useful effect of this is to reduce WAL retention by
moving restart_lsn based on the latest confirmed_flush_lsn.

--
Best Wishes,
Ashutosh Bapat