From 1462aad2e4474ab61174f8ab00992cd3d6d57c7b Mon Sep 17 00:00:00 2001 From: Amit Kapila Date: Wed, 24 Jul 2024 10:13:36 +0530 Subject: Allow altering of two_phase option of a SUBSCRIPTION. The two_phase option is controlled by both the publisher (as a slot option) and the subscriber (as a subscription option), so the slot option must also be modified. Changing the 'two_phase' option for a subscription from 'true' to 'false' is permitted only when there are no pending prepared transactions corresponding to that subscription. Otherwise, the changes of already prepared transactions can be replicated again along with their corresponding commit leading to duplicate data or errors. To avoid data loss, the 'two_phase' option for a subscription can only be changed from 'false' to 'true' once the initial data synchronization is completed. Therefore this is performed later by the logical replication worker. Author: Hayato Kuroda, Ajin Cherian, Amit Kapila Reviewed-by: Peter Smith, Hou Zhijie, Amit Kapila, Vitaly Davydov, Vignesh C Discussion: https://postgr.es/m/8fab8-65d74c80-1-2f28e880@39088166 --- doc/src/sgml/protocol.sgml | 18 +++++++++++++++- doc/src/sgml/ref/alter_subscription.sgml | 36 ++++++++++++++++++++++++++++---- 2 files changed, 49 insertions(+), 5 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml index 1b27d0a5479..79cd5996926 100644 --- a/doc/src/sgml/protocol.sgml +++ b/doc/src/sgml/protocol.sgml @@ -2192,7 +2192,23 @@ psql "dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;" - The following option is supported: + The following options are supported: + + + + TWO_PHASE [ boolean ] + + + If true, this logical replication slot supports decoding of two-phase + commit. With this option, commands related to two-phase commit such as + PREPARE TRANSACTION, COMMIT PREPARED + and ROLLBACK PREPARED are decoded and transmitted. + The transaction will be decoded and transmitted at + PREPARE TRANSACTION time. + + + + diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml index 476f1956223..6af6d0d2c8d 100644 --- a/doc/src/sgml/ref/alter_subscription.sgml +++ b/doc/src/sgml/ref/alter_subscription.sgml @@ -68,8 +68,9 @@ ALTER SUBSCRIPTION name RENAME TO < Commands ALTER SUBSCRIPTION ... REFRESH PUBLICATION, ALTER SUBSCRIPTION ... {SET|ADD|DROP} PUBLICATION ... - with refresh option as true and - ALTER SUBSCRIPTION ... SET (failover = true|false) + with refresh option as true, + ALTER SUBSCRIPTION ... SET (failover = true|false) and + ALTER SUBSCRIPTION ... SET (two_phase = false) cannot be executed inside a transaction block. These commands also cannot be executed when the subscription has @@ -228,8 +229,9 @@ ALTER SUBSCRIPTION name RENAME TO < disable_on_error, password_required, run_as_owner, - origin, and - failover. + origin, + failover, and + two_phase. Only a superuser can set password_required = false. @@ -252,6 +254,32 @@ ALTER SUBSCRIPTION name RENAME TO < failover option is enabled. + + + The failover + and two_phase + parameters can only be altered when the subscription is disabled. + + + + When altering two_phase + from true to false, the backend + process reports an error if any prepared transactions done by the + logical replication worker (from when two_phase + parameter was still true) are found. You can resolve + prepared transactions on the publisher node, or manually roll back them + on the subscriber, and then try again. The transactions prepared by + logical replication worker corresponding to a particular subscription have + the following pattern: pg_gid_%u_%u + (parameters: subscription oid, remote transaction id xid). + To resolve such transactions manually, you need to roll back all + the prepared transactions with corresponding subscription IDs in their + names. Applications can check + pg_prepared_xacts + to find the required prepared transactions. After the two_phase + option is changed from true to false, + the publisher will replicate the transactions again when they are committed. + -- cgit v1.2.3