Avoid resetting subscription two_phase state to pending when already enabled.
ALTER SUBSCRIPTION ... SET (two_phase = true) always moved the subscription's two_phase state to pending, even if two_phase was already enabled. The publisher's slot still has two_phase enabled in that case, and prepared transactions may already exist on the subscriber, so the reset broke checks that rely on the enabled state: 1. A later SET (two_phase = false) no longer checked for existing prepared transactions and disabled two_phase anyway. When those transactions were committed on the publisher, their changes were re-sent as regular transactions instead of COMMIT PREPARED, leading to self-deadlocks, duplicate data, or orphaned prepared transactions. 2. REFRESH PUBLICATION (and SET/ADD PUBLICATION) with copy_data = true was allowed even though the publisher's slot still had two_phase enabled. This could stream prepared transactions during the initial sync of newly added tables and apply them as empty prepares. Fix this by leaving the state unchanged when two_phase is already enabled. This problem was introduced by commit 1462aad2e44, which allowed altering the two_phase option of a subscription. This changes behavior when slot_name is altered together with SET (two_phase = true) on an ENABLED subscription. Previously the state was reset to PENDING and the apply worker enabled two_phase on the replacement slot. Now the state stays ENABLED and the apply worker does not touch the replacement slot's two_phase property. This is intentional: resetting the state would reintroduce the problems described above, since prepared transactions from the old slot may still exist on the subscriber. The documentation already requires the replacement slot's two_phase property to match the subscription option. Author: Zhijie Hou <[email protected]> Reviewed-by: shveta malik <[email protected]> Reviewed-by: Hayato Kuroda <[email protected]> Reviewed-by: Bertrand Drouvot <[email protected]> Reviewed-by: vignesh C <[email protected]> Reviewed-by: Peter Smith <[email protected]> Reviewed-by: Amit Kapila <[email protected]> Discussion: https://postgr.es/m/[email protected] Backpatch-through: 18 Branch ------ REL_19_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/1e1a4821a9efefe4a813932d1510c6704938f713 Modified Files -------------- src/backend/commands/subscriptioncmds.c | 19 +++++++++++++------ src/test/subscription/t/021_twophase.pl | 16 +++++++++++++--- 2 files changed, 26 insertions(+), 9 deletions(-)
