Avoid resetting subscription two_phase state to pending when already enabled

From: Zhijie Hou <houzhijie22(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Cc: amit(dot)kapila16(at)gmail(dot)com
Subject: Avoid resetting subscription two_phase state to pending when already enabled
Date: 2026-10-29 05:56:56
Message-ID: 8e85abdf-738b-4578-ac42-4dc8eaa061dd@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


Hi,

While reviewing another logical replication thread, I noticed an issue
with altering a subscription's two_phase option.

In AlterSubscription(), executing ALTER SUBSCRIPTION ... SET (two_phase
= true) unconditionally set subtwophasestate to
LOGICALREP_TWOPHASE_STATE_PENDING, even when two_phase was already in
LOGICALREP_TWOPHASE_STATE_ENABLED state.

Because two_phase remains enabled on the publisher's replication slot
and prepared transactions may already exist on the subscriber,
overwriting the state to PENDING broke checks that rely on
subtwophasestate being ENABLED:

1. A subsequent ALTER SUBSCRIPTION ... SET (two_phase = false) skipped
the LookupGXactBySubid() check and disabled two_phase while prepared
transactions still existed on the subscriber. When those transactions
were later committed on the publisher, the publisher re-sent all of
their changes as regular transactions instead of COMMIT PREPARED,
leading to self-deadlocks, duplicate data, or orphaned prepared
transactions.

2. ALTER SUBSCRIPTION ... REFRESH PUBLICATION (and SET/ADD PUBLICATION)
with copy_data = true was allowed even though the publisher's slot still
had two_phase enabled, allowing prepared transactions on newly added
tables to be streamed during initial table synchronization and applied
as empty prepares.

I think we can fix this by not resetting subtwophasestate to PENDING
when two_phase is already in the ENABLED state, as shown in the attached
patch.

This issue has existed since PG 18, when altering the two_phase option
was introduced, so it should be back-patched down to that branch.

--
Best Regards,
Zhijie Hou

Attachment Content-Type Size
v1-0001-Avoid-resetting-subscription-two_phase-state-to-p.patch text/plain 4.0 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message vignesh C 2026-10-29 06:21:37 Re: Parallel Apply
Previous Message Kirill Reshke 2026-10-29 05:53:25 Re: [PG19] pg_upgrade --check passes, then the upgrade fails