Horton

Re: Fix slot synchronization with two_phase decoding enabled

Amit Kapila <amit.kapila16@gmail.com>

View thread View on postgr.es
From: Amit Kapila <amit.kapila16@gmail.com>
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, shveta malik <shveta.malik@gmail.com>, Nisha Moond <nisha.moond412@gmail.com>, "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2025-05-05T10:59:18Z
Lists: pgsql-hackers 
On Sun, May 4, 2025 at 2:33 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> While I cannot be entirely certain of my analysis, I believe the root
> cause might be related to the backward movement of the confirmed_flush
> LSN. The following scenario seems possible:
>
> 1. The walsender enables the two_phase and sets two_phase_at (which
> should be the same as confirmed_flush).
> 2. The slot's confirmed_flush regresses for some reason.
> 3. The slotsync worker retrieves the remote slot information and
> enables two_phase for the local slot.
>

Yes, this is possible. Here is my theory as to how it can happen in
the current case. In the failed test, after the primary has prepared a
transaction, the transaction won't be replicated to the subscriber as
two_phase was not enabled for the slot. However, subsequent keepalive
messages can send the latest WAL location to the subscriber and get
the confirmation of the same from the subscriber without its origin
being moved. Now, after we restart the apply worker (due to
disable/enable for a subscription), it will use the previous
origin_lsn to temporarily move back the confirmed flush LSN as
explained in one of the previous emails in another thread [1]. During
this temporary movement of confirm flush LSN, the slotsync worker
fetches the two_phase_at and confirm_flush_lsn values, leading to the
assertion failure. We see this issue intermittently because it depends
on the timing of slotsync worker's request to fetch the slot's value.

If this theory is correct, then we need something on the lines of what
Vignesh proposed in email [2] (Confirm_flush_dont_allow_backward) to
fix it.

[1]: https://www.postgresql.org/message-id/CAA4eK1%2BzWQwOe5G8zCYGvErnaXh5%2BDbyg_A1Z3uywSf_4%3DT0UA%40mail.gmail.com
[2]: https://www.postgresql.org/message-id/CALDaNm3hgow2%2BoEov5jBk4iYP5eQrUCF1yZtW7%2BdV3J__p4KLQ%40mail.gmail.com

-- 
With Regards,
Amit Kapila.

Commits

  1. Don't retreat slot's confirmed_flush LSN.

  2. Fix assertion failure during decoding from synced slots.

  3. Fix xmin advancement during fast_forward decoding.

  4. Fix slot synchronization for two_phase enabled slots.

  5. Again match pg_user_mappings to information_schema.user_mapping_options.