Fix slot synchronization for two_phase enabled slots. The issue is that the transactions prepared before two-phase decoding is enabled can fail to replicate to the subscriber after being committed on a promoted standby following a failover. This is because the two_phase_at field of a slot, which tracks the LSN from which two-phase decoding starts, is not synchronized to standby servers. Without two_phase_at, the logical decoding might incorrectly identify prepared transaction as already replicated to the subscriber after promotion of standby server, causing them to be skipped.
To address the issue on HEAD, the two_phase_at field of the slot is exposed by the pg_replication_slots view and allows the slot synchronization to copy this value to the corresponding synced slot on the standby server. This bug is likely to occur if the user toggles the two_phase option to true after initial slot creation. Given that altering the two_phase option of a replication slot is not allowed in PostgreSQL 17, this bug is less likely to occur. We can't change the view/function definition in backbranch so we can't push the same fix but we are brainstorming an appropriate solution for PG17. Author: Zhijie Hou <houzj.f...@fujitsu.com> Reviewed-by: Amit Kapila <amit.kapil...@gmail.com> Reviewed-by: Masahiko Sawada <sawada.m...@gmail.com> Discussion: https://postgr.es/m/tyapr01mb5724cc7c288535bbceee65da94...@tyapr01mb5724.jpnprd01.prod.outlook.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/4868c96bc8c60145958f1a83bbe9409718a696cb Modified Files -------------- doc/src/sgml/system-views.sgml | 11 +++ src/backend/catalog/system_views.sql | 1 + src/backend/replication/logical/slotsync.c | 14 ++-- src/backend/replication/slotfuncs.c | 8 ++- src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.dat | 6 +- .../recovery/t/040_standby_failover_slots_sync.pl | 81 ++++++++++++++++++++-- src/test/regress/expected/rules.out | 3 +- 8 files changed, 112 insertions(+), 14 deletions(-)
